I am sending this additional excerpt only to call attention to the section
below that I have preceded with asterisks. This lends argument to the
negative aspects of a Unique Identifier as opposed to the AutoNumber. Also,
some info on how a GUID begins it life.
Ensuring Uniqueness of Data Objects
When you are dealing with large databases used by multiple sites, as in this
multinational scenario, you might encounter the tricky issue of ensuring the
uniqueness of table objects. In such situations, UNIQUEIDENTIFIER may just
be the answer. UNIQUEIDENTIFIER is a GUID data type that stores globally
unique identifiers. A GUID is a binary number guaranteed to be unique, so no
other computer will generate the same value.
The GUID's unique values are generated from the identification number of the
computer's network card plus a unique number from the CPU clock. Network
card manufacturers guarantee the uniqueness of each network card-at least,
for the next 100 years.
UNIQUEIDENTIFIER values are not generated automatically the way the IDENTITY
property does. To generate UNIQUEIDENTIFIER values for your table objects,
you must specify the NEWID function as the default value for the column.
For example, if you want to create a table that lists net revenues of all
your multinational company's subsidiaries' top products, and you want to
specify a GUID data type, you can type:
CREATE TABLE NetRevenueTable
(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10))
In Database Tools, you do this in Database Diagram or when you're designing
a table. Select Is RowGUID for the column you want uniquely identified. The
default would be (newid()), which automatically generates the RowGUID.
* Although there are many instances where you need to guarantee that a
table object is unique, keep in mind the following characteristics of the
UNIQUEIDENTIFIER data type if you choose to use it:
* Values are long and obscure.
* Values are random and follow no pattern that makes sense to the user.
* Values are difficult to use in applications that depend on serially
incrementing values.
* Values, at 16 bytes, are large, so indexes built with these keys may be
slower.
For more information on the GUID data type, consult the SQL Server 7.0
documentation.
----- Original Message -----
From: "Stephen R. Hartmann" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 23, 2001 11:21 PM
Subject: RE: Random numbers continued
> I think I can give you a reason for random numbers versus autonumbers. I
> was once asked to look into interfacing with a very sophisticated contact
> management system. This system used no autonumbers. It used something it
> called a GUID. I forget what the GUID stood for, but they were very large
> random numbers. The reason for the GUIDs in this application was
> replication. The system was built to support offline processing. Whent
the
> user came back on line and merged their changes, the randomness of the
IDs,
> made the merging process much easier. The developer I talked to said the
> odds of duplicating one of these very large numbers were extremely low.
>
> There is a minore performance reason for using random numbers. The way
> indexes are built, if your ID is indexed, the performance of your indexes
> will degrade as new records are added with the values scewed towards the
> same range of numbers.
>
> Steve
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of Bob Powell
> Sent: Monday, April 23, 2001 4:26 PM
> To: [EMAIL PROTECTED]
> Subject: Random numbers continued
>
>
>
> Ladies and Gentlemen:
>
> I see I have instigated a large discussion on
> random numbers. Let me explain where this is coming from.
>
> I have a DBA that feels id numbers which link the
> various database files together should be random in nature.
> I explained that R:base has its own way of
> automatically generating consecutive numbers which
> guarantees uniqueness. I also feel that allowing R:BASE to
> generate the number automatically in the table is better
> than having to write application code to do it. As we all
> know this is a must and R:Base's solution is just fine with
> me.
>
> She feels however that random generating a
> unique id number is a better way to go although I am hard
> pressed to have her explain to me why. So I just wanted to
> know if someone had done something I have never been able
> to do.
>
> Having taken PASCAL 100 years ago I agree with the
> idea that a random generator will eventaually repeat a
> number. I was taught that a random generator should in
> fact repeat the same sequence if the same seed is used.
> Somethimes for testing being able to generate the same
> random sequence is good. At other times it is not, but it
> seems to me that as long as you change the seed the random
> sequence should be different which I think is what you all
> have discovered. This seems perfectly acceptable to me if
> I remeber my training years ago.
>
> Based on the way I understand random generators in all
> languages R:BASE's is responding correctly. I think what
> we all are discovering is that there is not way to randomly
> generate unique numbers. This is OK with me since I feel
> the unique consective numbering allowed in tables works
> just dandy.
>
> Maybe I need to explain to her that sometimes database
> theory needs to be over-riden by practicality. She has it
> in her notes from one of her database instructors that
> random generated id numbers is a good thing.
>
> ----------------------
> Bob Powell
> The Hotchkiss School
> Lakeville, Connecticut
> Systems Administrator
> [EMAIL PROTECTED]
>
>
