SQL Server Performance

Using Datetime in a Primary Key

Discussion in 'T-SQL Performance Tuning for Developers' started by jhubbard@healthwaresoluti, Sep 7, 2005.

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

  2. Chappy New Member

    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 ?

  3. dineshasanka Moderator

    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

  4. FrankKalis Moderator

    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
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page