SQL Server Performance Forum – Threads Archive

Using Datetime in a Primary Key

I am working on an application and considering the following primary key structure. RegID int
MachNum int
RecordDate Datetime This would be the Join structure for all the child tables. I am concerned about the performance using a datetime field in the primary index. Any thoughts would be greatly appreciated Jim

I dont know of any times when I would recommend a datetime to be used in a PK. But also there is potentially a uniqueness problem..
if (RegID, MachNum) is unique, then you dont need RecordDate in your PK
if (RegID, MachNum) is NOT unique, then isnt it possible (albeit unlikely) that two records could try to share the same RecordDate ?

you should avoid the datetime as the Primary key which will effect the perfomrance of the database.
If you can explain your businees logic we might able to suggest a new key without using a datetime —————————————-
http://spaces.msn.com/members/dineshasanka

Hard to tell given the information you provided. Generally you would want to keep the PRIMARY KEY as narrow as possible. So, if you have the choice between a three column composite PRIMARY KEY and adding a single column surrogate key (probably with the IDENTITY property), I think many folks would go with the IDENTITY approach. Probably the DATETIME column is a good candidate for the clustered index. But then again, that’s difficult to tell without further information.
How many rows are we talking about? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>