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