[dba-SQLServer] Fwd: SQL Server Primary Key

Susan Harkins ssharkins at gmail.com
Wed Sep 3 20:34:06 CDT 2014


Well, I sent him all of the recommendations and he did exactly what he
proposed to do in the first place. I have no idea why he asked for my
opinion. :)

I wish him well!

Susan H.


On Wed, Sep 3, 2014 at 9:29 PM, <aclawhon at hiwaay.net> wrote:

> 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