Share auto-incremented primary key between two tables

Question

Hi I want to have two tables each have an INT "id" column which will auto-increment but I don't want either "id" columns to ever share the same number. What is this called and what's the best way to do it? Sequence? Iterator? Index? Incrementor?

Motivation: we're migrating from one schema to a another and have a web-page that reads both tables and shows the (int) ID, but I can't have the same ID used for both tables.

I'm using SQL Server 9.0.3068.

Thanks!

Answer 1

I think using a GUID would be the most straightforward way, if I understand you correctly.

SELECT NEWID()

Answer 2

Use a column with GUID (Globally Unique Identifier) type. It's 16 byte and will be always unique for each row.

Just be aware that you'll get a significant performance hit comparing to normal integer keys.

Answer 3

Use another table with an ID key of type int default it to 1, called KeyID or whatever.

Have a stored procedure retrieve the value, add 1, then update the KeyID, then return this to the stored procedure which is updating your two tables which needs the new unique key.

This will ensure the ID is an int, and that it's unique between the set of tables which are using the stored procedure to generate new ID's.

Answer 4

I don't know what you would call it.

If you don't want to use a GUID or a separate table, you could also create a function that looked at the max values of the ids from both tables and added one to the that value (or something like that).

You could then call that function in an insert trigger on both tables.

Answer 5

Just configure the identity increment to be >1 e.g. table one uses IDENTITY (1, 10) [1,11,21...] and table two uses IDENTITY (2, 10) [2,12,22...]. This will also give you some room for expansion if needed later.

Answer 6

I am personally a fan of the GUID solution, but here is a viable option.

Many solutions to this problem have avoided GUID and used good old integer. This is common also with merge replication situations where many satellite sites merge with a master and key conflicts need to be avoided.

If GUID will not work for you, and you absolutely must have int, bigint, or the like, you can always just use an IDENTITY column and have each table with a different value for SEED. Those datatypes have a very wide range, and it is not too hard to split the range into usable segments, especially if all you want is two splits. As an example, basic int has a range from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). This is more than enough for a customer table, for example.

Transact-SQL Reference (SQL Server 2000) int, bigint, smallint, and tinyint

Example:

--Create table with a seed of 1 billion and an increment of 1
CREATE TABLE myTable
(
primaryKey int IDENTITY (1000000000, 1),
columnOne varchar(10) NOT NULL
)

Answer 7

You can define an IDENTITY column in a third table, use that to generate ID values, but you always roll back any inserts you make into the table (to avoid making it grow). Rolling back the transaction doesn't roll back the fact that the ID was generated.

I'm not a regular user of Microsoft SQL Server, so please forgive any syntax gaffes. But something like the following is what I have in mind:

CREATE TABLE AlwaysRollback (
  id IDENTITY(1,1)
);

BEGIN TRANSACTION;
INSERT INTO AllwaysRollBack () VALUES ();
ROLLBACK TRANSACTION;

INSERT INTO RealTable1 (id, ...) VALUES (SCOPE_IDENTITY(), ...);

BEGIN TRANSACTION;
INSERT INTO AllwaysRollBack () VALUES ();
ROLLBACK TRANSACTION;

INSERT INTO RealTable2 (id, ...) VALUES (SCOPE_IDENTITY(), ...);