[dba-SQLServer] Fwd: SQL Server Primary Key

Rocky Smolin rockysmolin at bchacc.com
Wed Sep 3 20:48:08 CDT 2014


"Your reader states that he believes he can use a DateTime field as a
"unique" record identifier because each record is created in a "different"
100 nanosecond window. "

Let's just call that one of the worst design ideas we've ever read and leave
it at that. :)

r 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
aclawhon at hiwaay.net
Sent: Wednesday, September 03, 2014 6:30 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Fwd: SQL Server Primary Key

Susan:

I was having doubts/concerns about this very question, but it looks like
Rocky beat me to the punch.  Your reader states that he believes he can use
a DateTime field as a "unique" record identifier because each record is
created in a "different" 100 nanosecond window.  I checked this to be sure:
A nanosecond is one-billionth of a second or
1 x ten-to-the-negative-ninth of a second.  (Stated numerically that would
be 0.000000001 seconds.)

He says each record will be unique because they are all created in a
"different" 100 nanosecond window.  Numerically, 100 nanoseconds equates to
0.000000100 or 1 x ten-to-the-negative-seventh power.  He says he has 100
million records or 1 x ten-to-the-eighth power.  With that many records,
there's no guarantee that two (or possibly more than two) of the records
might have been created with the exact [identical] DateTime value.  (I'm not
a good enough mathematician or statistician to calculate the probability
that two records might be created with the same value in the DateTime field,
but the probability is not zero, therefore the DateTime data type should not
be used as the PK.

I recall reading a discussion in a book on database theory where this very
point was debated with respect to whether or not Social Security numbers
should be used as a PK.  While the probability of two SSNs being duplicates
was very low, it was not zero so the argument was that "artificial" primary
keys - such as Social Security numbers - should never be used as a PK.

Positive Alan of Huntsville



Quoting Rocky Smolin <rockysmolin at bchacc.com>:

> "Each phone record can be
>> uniquely identified by the DateTime2(7) start time of the call 
>> because each record is guaranteed to be created in a different 100 
>> nano second window."
>
> That's probably true.  Probably isn't good enough for a primary key.  
> Use Autonumber.
>
> IMHO.
>
> Rocky
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan 
> Harkins
> Sent: Wednesday, September 03, 2014 7:02 AM
> To: Discussion concerning MS SQL Server
> Subject: [dba-SQLServer] Fwd: SQL Server Primary Key
>
> This is from a reader -- seriously over my head. Anyone want to offer 
> some advice?
>
> Susan H.
>
>
> On Wed, Sep 3, 2014 at 4:02 AM, Phillip Smith <phillip at creamcow.com>
wrote:
>
>> Hi Susan,
>> Just reading your post regarding using the right Primary Key. I'm 
>> building a rehouse to store telephone data. Each phone record can be 
>> uniquely identified by the DateTime2(7) start time of the call 
>> because each record is guaranteed to be created in a different 100 
>> nano second window. There are 100 million records. The main way to 
>> view data is
> chronological order.
>> I'm trying to decide whether to use the CallStart datetime2(7) field 
>> for the primary key. I can cluster on this key and join to my 
>> bridging tables using this key. Or should I crate a CallId (Bigint) 
>> that encodes the datetim, Maybe in yymmddhhmmssnnnnnnn format. You 
>> have stated on your post that there is an overhead to using Datetime 
>> type as the primary key. Is this true for my scenario?
>> Best regards
>>
>> Phillip
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list