aclawhon at hiwaay.net
aclawhon at hiwaay.net
Wed Sep 3 21:21:36 CDT 2014
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 > >