Generate unique ID to share with multiple tables SQL 2008

Question

I have a couple of tables in a SQL 2008 server that I need to generate unique ID's for. I have looked at the "identity" column but the ID's really need to be unique and shared between all the tables.

So if I have say (5) five tables of the flavour "asset infrastructure" and I want to run with a unique ID between them as a combined group, I need some sort of generator that looks at all (5) five tables and issues the next ID which is not duplicated in any of those (5) five tales.

I know this could be done with some sort of stored procedure but I'm not sure how to go about it. Any ideas?

Answer 1

This is a common problem for example when using a table of people (called PERSON singular please) and each person is categorized, for example Doctors, Patients, Employees, Nurse etc.

It makes a lot of sense to create a table for each of these people that contains thier specific category information like an employees start date and salary and a Nurses qualifications and number.

A Patient for example, may have many nurses and doctors that work on him so a many to many table that links Patient to other people in the PERSON table facilitates this nicely. In this table there should be some description of the realtionship between these people which leads us back to the categories for people.

Since a Doctor and a Patient could create the same Primary Key ID in their own tables, it becomes very useful to have a Globally unique ID or Object ID.

A good way to do this as suggested, is to have a table designated to Auto Increment the primary key. Perform an Insert on that Table first to obtain the OID, then use it for the new PERSON.

I like to go a step further. When things get ugly (some new developer gets got his hands on the database, or even worse, a really old developer, then its very useful to add more meaning to the OID.

Usually this is done programatically, not with the database engine, but if you use a BIG INT for all the Primary Key ID's then you have lots of room to prefix a number with visually identifiable sequence. For example all Doctors ID's could begin with 100, all patients with 110, all Nurses with 120.

To that I would append say a Julian date or a Unix date+time, and finally append the Auto Increment ID.

This would result in numbers like:

110,2455892,00000001
120,2455892,00000002
100,2455892,00000003

since the Julian date 100yrs from now is only 2492087, you can see that 7 digits will adequately store this value.

A BIGINT is 64-bit (8 byte) signed integer with a range of -9.22x10^18 to 9.22x10^18 ( -2^63 to 2^63 -1). Notice the exponant is 18. That's 18 digits you have to work with.

Using this design, you are limited to 100 million OID's, 999 categories of people and dates up to... well past the shelf life of your databse, but I suspect thats good enough for most solutions.

The operations required to created an OID like this are all Multiplication and Division which avoids all the gear grinding of text manipulation.

The disadvantage is that INSERTs require more than a simple TSQL statement, but the advantage is that when you are tracking down errant data or even being clever in your queries, your OID is visually telling you alot more than a random number or worse, an eyesore like GUID.

Answer 2

I have a very simple solution. It should be good for cases when the number of tables is small:

create table T1(ID int primary key identity(1,2), rownum varchar(64))

create table T2(ID int primary key identity(2,2), rownum varchar(64))

insert into T1(rownum) values('row 1')
insert into T1(rownum) values('row 2')
insert into T1(rownum) values('row 3')

insert into T2(rownum) values('row 1')
insert into T2(rownum) values('row 2')
insert into T2(rownum) values('row 3')

select * from T1

select * from T2

drop table T1
drop table T2

Answer 3

The simplest solution is to set your identity seeds and increment on each table so they never overlap. Table 1: Seed 1, Increment 5 Table 2: Seed 2, Increment 5 Table 3: Seed 3, Increment 5 Table 4: Seed 4, Increment 5 Table 5: Seed 5, Increment 5

The identity column mod 5 will tell you which table the record is in. You will use up your identity space five times faster so make sure the datatype is big enough.

Answer 4

I found this when searching on google. I am facing a simillar problem for the first time. I had the idea to have a dedicated ID table specifically to generate the IDs but I was unsure if it was something that was considered OK design. So I just wanted to say THANKS for confirmation.. it looks like it is an adequate sollution although not ideal.

Answer 5

Reading into the design a bit, it sounds like what you really need is a single table called "Asset" with an identity column, and then either:

a) 5 additional tables for the subtypes of assets, each with a foreign key to the primary key on Asset; or

b) 5 views on Asset that each select a subset of the rows and then appear (to users) like the 5 original tables you have now.

If the columns on the tables are all the same, (b) is the better choice; if they're all different, (a) is the better choice. This is a classic DB spin on the supertype / subtype relationship.

Alternately, you could do what you're talking about and recreate the IDENTITY functionality yourself with a stored proc that wraps INSERT access on all 5 tables. Note that you'll have to put a TRANSACTION around it if you want guarantees of uniqueness, and if this is a popular table, that might make it a performance bottleneck. If that's not a concern, a proc like that might take the form:

CREATE PROCEDURE InsertAsset_Table1 (
  BEGIN TRANSACTION
  -- SELECT MIN INTEGER NOT ALREADY USED IN ANY OF THE FIVE TABLES
  -- INSERT INTO Table1 WITH THAT ID
  COMMIT TRANSACTION -- or roll back on error, etc.
)

Again, SQL is highly optimized for helping you out if you choose the patterns I mention above, and NOT optimized for this kind of thing (there's overhead with creating the transaction AND you'll be issuing shared locks on all 5 tables while this process is going on). Compare that with using the PK / FK method above, where SQL Server knows exactly how to do it without locks, or the view method, where you're only inserting into 1 table.

Answer 6

You could let them each have an identity that seeds from numbers far enough apart never to collide.

GUIDs would work but they're butt-ugly, and non-sequential if that's significant.

Another common technique is to have a single-column table with an identity that dispenses the next value each time you insert a record. If you need them pulling from a common sequence, it's not unlikely to be useful to have a second column indicating which table it was dispensed to.

You realize there are logical design issues with this, right?

Answer 7

Why not use a GUID?