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

Why not use a GUID?

Answer 2

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 3

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 4

Hi, 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

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.