I have a table that has an ID column set as an int type with identity set to
'Yes', so that each number that is auto-generated in this column will be
unique.  However, now I need to add a second table to the database which
will also need an ID column.  My problem is that I have a requirement that
the ID values in both tables be unique across both tables.  What is the best
way to accomplish this?

Thanks!

Actually that no built in functionality of SQL Server, you will have to
write a function or procedure on your own to determine the next valid
key. Make sure that you cover problems that come along with
concurrency.

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Maybe you could explain why these IDs need to be unique?  Why do you care if
you have a CustomerID = 1 and an OrderID = 1?  If they are different
entities, that's why they are in different tables, right?

Anyway, assuming you have a good reason (or a pointy-haired boss who doesn't
care about, much less understand, reason):

Without changing the first table, and assuming you don't care what the
values look like, you could set the identity seed on the second table to be
some astronomical value like -2 billion or 1 billion.  Assuming neither
table will attept to generate more than 1 billion inserts, you should be
safe for some time.

If you need something that is automatic and unique across tables and doesn't
have to worry about boundaries then stop using INTs and use GUIDs, I guess.
Or go back to the top of this message and review the requirements.






Show quoteHide quote

To take that idea a step further, you could seed the "other" table with -1
and increment by -1.  One table will have positive numbers for ID, the other
will have negative numbers.

Set the datatype to uniqueidentifier.
It will be unique across all tables, even separate databases.

Show quoteHide quote

Using a Unique Identifier Data Type (GUID) will solve your problem.

Possible Solutions...
If you cant update the Current Table with a new datatype then what I would
do is put a INSTEAD OF trigger on the second table to look for another
Identity.
This option isnt great for high transaction rates.

You could also do the same with Stored Procs. When data is inserted, look at
the first table grab the max identity increase by one an insert.
This option isnt great for high transaction rates either.

thanks,

Show quoteHide quote

UNDO
XP_SENDMAIL Overload in SQL 2005
how to do this
Query help.... with CASE
Time recording query
Small problem with NOT EXISTS...
Win Server 2003 SP2 - SQL2k5 Performance Improvements
Linked Server Error: Login failed for user NT AUTHORITY\ANONYMOUS LOGON
Statistic Problem
Union with the results of two SP