Rocky Smolin
rockysmolin at bchacc.com
Wed Sep 3 20:50:13 CDT 2014
Some people love self-flagellation and gorgonzola cheese, too. There's no accounting for taste. r -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia Sent: Wednesday, September 03, 2014 6:36 PM To: Discussion concerning MS SQL Server Cc: Discussion concerning MS SQL Server Subject: Re: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com