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 > >