[thelist] preventing duplicate randomly generated ID's was: SQL 2000 auto create ID's

[Scott Dexter]

> -----Original Message-----
> From: Chris W. Parker [mailto:cparker at swatgear.com]
>
> i know i'm coming late to this thread but how do you handle
> duplicates? although duplicate passwords aren't too much of
> problem, there is a problem with duplicate IDs.
>
> i was thinking you could create the ID, check a table to see
> if that ID already exists. if it does, create a new ID and
> check again until a match isn't found. once a unique ID is

stepping in a little (okay, a lot) late here, but if your method of
choosing an ID is robust enough, you don't have to worry about checking
for existence. (Head them off at the pass, as it were)

Setting an ID column to be the Identity column and letting SQL server
increment this value is one way --even if you delete records you're not
going to get duplicate ids as you add records.

In SQL2000 there is also the uniqueidentifier datatype, and it takes a
32 byte GUID. You can create a table with a column of this datatype, and
give it the default value of NewID() --NewID() is a SQL Server function
that returns a 32 byte GUID. These damn things are guaranteed unique
from the operating system level all the way up and through any
application. They can be a pain to work with (any 32-character
alphanumeric value would be), but are appropriate when all you want is
to mark the record with a guaranteed unique id.

If you need the ID outside of inserting the record, use a stored
procedure; use NewID() before your INSERT statement, add its returned
value as part of the INSERT, and then return it from your stored proc.

An idea, anyway...

sgd
--
http://ti3.com/
Ti3, Inc. A TALX company