[dba-SQLServer] Fwd: SQL Server Primary Key

When he ends up with duplicate records errors he may ask again (: 


Some people just love natural keys. 
—
Sent from Mailbox

On Wed, Sep 3, 2014 at 6:34 PM, Susan Harkins <ssharkins at gmail.com> wrote:

> 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
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com