Re: NextNumbers Tables

stu wrote:
> Ive heard ppl suggest a next numbers table to generate a unique ID for a
> surrogate primary key. Something like:
> CREATE TABLE STUART.NEXTNUMBERS (TABLENAME VARCHAR (20 ) NOT NULL,
> NEXTNUMBER BIGINT NOT NULL)
>
> They then increment the next number when one is taken. My question is:
>
> If the number is just a unique meaningless number why not have:
> CREATE TABLE STUART.NEXTNUMBER (NEXTNUMBER BIGINT NOT NULL) and all tables
> use the same next number. Just though of one reason as im writing this.
> This would mean all inserts (even across different tables) would need t be
> serialised. This is a pretty good reason not to use this!
>
> Any more thoughts on a next numbers table for surrogate keys?

If you want to do it that way, I'd go for the first approach. Just on caveat: The way to go is first increment the number then select it. Otherwise, you'll run into trouble in a multiuser environment where two users want to insert at the same time.

By the way: One advantadge of this method is that it is SQL standard and therefore DMBS independent. On the other hand, it's probably best to encapsulate this function anyway. Therefore you'd be able to switch implementations pretty easily.

Heinz Received on Mon Feb 10 2003 - 08:52:22 CET