Unique System Wide Identifiers but not GUID

[CaseyR]

Most tables have a single component primary key with a unique value.  If Auto-Increment is on,  this value is unique to the table only.  I recently had a situation where I needed a value that was not just unique to to one table, but unique to several tables.  GUID's would work, but they come with a lot of overhead and performance impacts.

So, I am considering doing this:

• Establishing a Global unthreaded variable to hold the last index number created;
• On web server startup, query each of the physical tables to get the highest index number used and store in the Global variable
• Each time a new record is created, increment the Global variable and use that number as the index number for the record (with appropriate threading control)

I though a ULONG would work as index number.

What are the downsides to this approach?

Many thanks.

[Bruce]

Hi Casey,

>> GUID's would work, but they come with a lot of overhead and performance impacts.

Interesting. I use GUIDS a fair bit here, and I'm not seeing the performance or overhead impacts. Not that I've set up too many formal tests mind. Perhaps, if you're concerned about this, you might want to do some tests to quantify these impacts?

>> What are the downsides to this approach?

Ulongs are a lot slower than Longs (computationally speaking) and of course Max out at 4 billion numbers (or 2 billion if you use a Long).

Also your system doesn't "scale" up well. For example, if the system became so busy (or uptime so important)  that you decided to add another webserver (talking to the same database) then you'd be in a right pickle.

Also users can "infer" information about your database, by guessing id numbers. NT5 makes this much less of an issue than NT4, but it never hurts to keep security as tight as possible.

For Guids, I use a really simple function to populate a 16 character string. It can be as simple as
loop x = 1 to 16
  guid

• = chr(random(65,90))

end

If you're bad at math and feel that there's too much chance of a collision then it's not hard to extend the code to include digits as well.

Cheers
Bruce

[CaseyR]

Thanks, Bruce

>>Interesting. I use GUIDS a fair bit here, and I'm not seeing the performance or overhead impacts. Not that I've set up too many formal tests mind. Perhaps, if you're concerned about this, you might want to do some tests to quantify these impacts?

I was just reflecting a lot of discussion at some of the Microsoft development forums.  Some testing is a good idea.  I will post the results.

>>Ulongs are a lot slower than Longs (computationally speaking) and of course Max out at 4 billion numbers (or 2 billion if you use a Long).

Never knew about ULONG computational speed, but I did think 4 billion would be more than enough.

Hadn't thought of adding multiple servers, but no sense closing off future choices.

Thanks again.

[Bruce]

Hi Casey,

A guid can be "anything" which you consider to be suitably unique. There is a Guid type in SQL, but that's quite a bit longer and a bit of overkill in our situation here.

Frankly, even a 16 char string is overkill. (possible combinations = alphabetSize ^ StringLength)

On the up side, no database access is required to "prime a new record ready for adding", and colission errors can pretty much be ignored. (The odds of getting a collision are _way_ smaller than say winning the lottery 10 times in a row.)

But please, if you do some performance testing, let us know the results, and if at all possible release the test code so others can duplicate the test to verify it.

cheers
Bruce

[CaseyR]

Just to let you know, I did a few tests comparing GUID to LONG in simple Parent/Child/GrandChild structures as Browses with 90,000 record tables.  As you mentioned, I didn't notice much differences in speed on a single machine.  Big difference in table size, though.

[Bruce]

The table size doesn't matter much methinks...
How big a guid did you use?

cheers
Bruce

[CaseyR]

16 character string