Darryl Collins
darryl at whittleconsulting.com.au
Wed Sep 3 21:33:11 CDT 2014
Agreed. I don't understand this obsession with 'natural' PK's when there is such a painless and automated way to avoid all of that risk and bother. Just put in a damn autokey for heaven's sake!! Oh well. Each to their own I guess. Cheers Darryl. -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of aclawhon at hiwaay.net Sent: Thursday, 4 September 2014 12:22 PM To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Fwd: SQL Server Primary Key Susan: If he winds up with a PK conflict, I have a feeling you'll hear about it ... he might send you [another] email full of exasperation as to why a noted database "authority" (that would be you) didn't warn him that this could happen. (Ha! Ha!) It won't be that big of a deal though. He'll just have to redo his application - once he figures out why it's not working. If he encounters a duplicate, the SQL Server engine will give him some kind of error message and not allow the record to be entered. Then he starts panicking ... Positive Alan of Huntsville Quoting Susan Harkins <ssharkins at gmail.com>: > 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