aclawhon at hiwaay.net
aclawhon at hiwaay.net
Wed Sep 3 21:48:12 CDT 2014
Susan: Look at the bright side. This guy has just handed you a great article to write if you're still submitting occasional articles to TechRepublic or whoever it is that you write for. You can write an article on the general topic of factors to consider when selecting a primary key and cite - without embarrassing this guy by calling him out by name - the specific scenario/application/query that he presented to you. Your article title might be something like: "Primary Keys: Artificial Key Or AutoNumber Key?" or something like that. (There's plenty of discussion of this topic in various database theory books.) If you think it will help, feel free to use my brilliant "mathematical analysis" of his application. (One of your editors at TechRepublic can help with figuring out the math with respect to the probability that he'll get a duplicate record and "break" his design.) I'm going to "guess" (off the top of my head) that the odds are somewhere in the 1-in-100 to 1-in-10,000 range - which is too great a probability to risk - especially if this guy is creating a database that will be heavily utilized and/or one where quite a bit of money is involved. Positive Alan of Huntsville Quoting Susan Harkins <ssharkins at gmail.com>: > This was my reaction too. Should be just never pans out. > > Susan H. > > > On Wed, Sep 3, 2014 at 10:26 AM, Rocky Smolin <rockysmolin at bchacc.com> > wrote: > >> "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 > >