Using a common seed generation for multiple tables.

Question

Using a common seed generation for multiple tables.

Randy Emerson

SSC Veteran

Points: 246
More actions
September 16, 2010 at 11:54 am

#240497

Hi, I'm new to SQL Server 2008, and I like to seek your help in achieving the following.
We are trying to implement a mechanism that will guarantee unique ID of type bigint across x number of tables.
The idea is to have a table named 'ID_generating_table' with nothing but an ID column set as IDENTITY(1,1). Any table that need an ID should insert a record into the ID_generating_table and use the Scope_Identity() or @@IDENTITY to get the ID inserted.
Failed tries:
* Create a scalar valued UDF and use it as default binding on the ID seeking table's ID column. scalar UDF won't allow INSERT, so not possible.
* Set the IDENTITY (1,2) on first ID seeking table and IDENTITY(2,2) on second ID seeking table. The two tables is a variable figure and it may become three, four or X in future.
Help is greatly appreciated.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

It would be a huge help if you posted the code you're using to insert data, and the table definition.

You need to have one table as your "master" table where you insert your first piece of data, and then pull the ID from there. It should be a pretty easy thing to add.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

A BigInt datatype has max value of 9,223,372,036,854,775,807, so it's large enough to allow several tables to reserve an identity seed of several billion. If you're planning to impelment a partitioned view, then you will want to do it this way. In the example below, the value of ID will be unique across all 3 tables, and you can easily add more tables as needed.

create table Table1 ( ID bigint not null identity(10000000000,1) primary key );

create table Table2 ( ID bigint not null identity(20000000000,1) primary key );

create table Table3 ( ID bigint not null identity(30000000000,1) primary key );

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125562 More actions · Answer 3

Eric Russell 13013 (9/16/2010)
A BigInt datatype has max value of 9,223,372,036,854,775,807, so it's large enough to allow several tables to reserve an identity seed of several billion. If you're planning to impelment a partitioned view, then you will want to do it this way. In the example below, the value of ID will be unique across all 3 tables, and you can easily add more tables as needed.

Also, adding a check constraint on ID will guarantee it's value stays within the intended range for each table.

create table Table1

(

ID bigint not null identity(10000000000,1)

check (ID between 10000000000 and 19999999999)

primary key

);

create table Table2

(

ID bigint not null identity(20000000000,1)

check (ID between 20000000000 and 29999999999)

primary key

);

create table Table3

(

ID bigint not null identity(30000000000,1)

check (ID between 30000000000 and 39999999999)

primary key

);

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

I'm curious... Why do the folks that imposed this requirement think that it's a good idea to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Randy Emerson SSC Veteran Points: 246 More actions · Answer 5

Thank you for the answer Eric that seems like a valid solution, let me propose it to my boss man and see what he got to say.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

Jeff Moden (9/16/2010)
I'm curious... Why do the folks that imposed this requirement think that it's a good idea to do?

There are a narrow range of data modeling scenarios where a column contained in multiple tables needs to be populated with incremental and non-overlapping values. For example, when implementing partitioned views, it's a requirement. Of course, in that case it would make more sense to partition on something like transaction_date or customer_id rather than using an identity column. I'm not sure why he needs the columns, but the important thing is using check constraints to enforce the non-overlapping requirement.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Eric Russell 13013 (9/17/2010)
Jeff Moden (9/16/2010)
I'm curious... Why do the folks that imposed this requirement think that it's a good idea to do?
There are a narrow range of data modeling scenarios where a column contained in multiple tables needs to be populated with incremental and non-overlapping values. For example, when implementing partitioned views, it's a requirement. Of course, in that case it would make more sense to partition on something like transaction_date or customer_id rather than using an identity column. I'm not sure why he needs the columns, but the important thing is using check constraints to enforce the non-overlapping requirement.

Thanks Eric. Partitioned views pretty much take care of themselves and no such special handling is required.

This is different than a partitioned view. The OP wants to do the equivalent of having more than one table share an IDENTITY column without regard to ranges of numbers. I've seen a database like this once and what they used it for actually was a good idea... I just can't remember what it was. Doing such a thing is normally a horrible idea and normally breaks all sorts of normalization rules.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 8

@Randy,

If you get a solution for this, please share it. And whether you get a solution or not, could you explain a bit more about why you need to do this so we can all learn from it? Thanks, Randy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Randy Emerson SSC Veteran Points: 246 More actions · Answer 9

I haven't found a solution yet. But here is why we are trying this solution. We have an address table which needs to be referenced from four tables User, Lead, contact, and activity. The proposed solution is to have a unique key across the four tables and use one foreign key in the address table like shown below.

LocalSeed

---------

ID - PK, Unique, Auto Incrementing IDENTITY

Contact

--------

ContactID - PK, FK(LocalSeed.ID)

Name

Blah...

Activity

---------

ActivityID - PK, FK(LocalSeed.ID)

Blah1

Blah2...

Lead

------

LeadID - PK, FK(LocalSeed.ID)

Blah3

Blah4...

Address

--------

AddressId - PK Auto Incrementing IDENTITY

AssociatedItemId - FK(LocalSeed.ID)

AddressOne

AddressTwo

City...

The other way was to implement Contact, Activity and Lead tables each with auto increment Identity and three cross reference tables ContactAddress, LeadAddress and ActivityAddress. Its not a problem from the database standpoint, but coding is a problem as we have to maintain three sets of code (at least in the form of if-else or switch-case statements) to add/insert an Address.

What we're trying can be achieved with a stored procedure that inserts into LocalSeed and get new ID every time I want to insert a record into one of three tables. But, I have to remember on every stored procedure and insert query to get the identity before inserting to one of these tables.

Now my attempt is to make it look seamless by applying a function as default constraint for LeadID, ContactID etc. which will get identity by inserting into LocalSeed table.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 10

Randy Emerson (9/21/2010)
I haven't found a solution yet. But here is why we are trying this solution. We have an address table which needs to be referenced from four tables User, Lead, contact, and activity. The proposed solution is to have a unique key across the four tables and use one foreign key in the address table like shown below.
LocalSeed
---------
ID - PK, Unique, Auto Incrementing IDENTITY
Contact
--------
ContactID - PK, FK(LocalSeed.ID)
Name
Blah...
Activity
---------
ActivityID - PK, FK(LocalSeed.ID)
Blah1
Blah2...
Lead
------
LeadID - PK, FK(LocalSeed.ID)
Blah3
Blah4...
Address
--------
AddressId - PK Auto Incrementing IDENTITY
AssociatedItemId - FK(LocalSeed.ID)
AddressOne
AddressTwo
City...
The other way was to implement Contact, Activity and Lead tables each with auto increment Identity and three cross reference tables ContactAddress, LeadAddress and ActivityAddress. Its not a problem from the database standpoint, but coding is a problem as we have to maintain three sets of code (at least in the form of if-else or switch-case statements) to add/insert an Address.
What we're trying can be achieved with a stored procedure that inserts into LocalSeed and get new ID every time I want to insert a record into one of three tables. But, I have to remember on every stored procedure and insert query to get the identity before inserting to one of these tables.
Now my attempt is to make it look seamless by applying a function as default constraint for LeadID, ContactID etc. which will get identity by inserting into LocalSeed table.

Sorry I lost track of this thread.

A function cannot insert into the Localseed table. Functions cannot modify data in tables.

I can certainly show you how to build and use a sequence table to solve this problem but it'll be nothing but headaches for you in the future. I just don't understand why you can't use an IDENTITY column in each table and not worry about a sequence table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125562 More actions · Answer 11

Randy Emerson (9/21/2010)
I haven't found a solution yet. But here is why we are trying this solution. We have an address table which needs to be referenced from four tables User, Lead, contact, and activity. The proposed solution is to have a unique key across the four tables and use one foreign key in the address table like shown below.

You mentioned earlier that you were considering my suggestion of implementing an identity column in each table with a non-overlapping seeded value and a constraint to guarantee it's uniqueness. What were your final thoughts on that?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Randy Emerson SSC Veteran Points: 246 More actions · Answer 12

Eric, I just wanted to let this solution run for a few weeks before making a final call, yes its working out well for us. We're using bigint identity with seeds about a billion apart from each other. The IDs look a bit too long on second table and there after, but we're not displaying them anywhere so no problems.