SQL Server Performance

PK's

Discussion in 'General Developer Questions' started by Reddy, Sep 21, 2005.

  1. Reddy New Member

    Hi
    In a single table how many columns at maximum I can have as a PK, I hope we call it as composite key. How does it effect the performance?


    Thanks!
    "He laughs best who laughs last"

  2. ghemant Moderator

    Hi Reddy,
    BOL says in "Maximum Capacity Specification" , "Columns per primary key : 16 " .


    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  3. dineshasanka Moderator

  4. FrankKalis Moderator

    You would want to keep your PRIMARY KEY as compact as possible.
    Remember that all column in the PRIMARY KEY *must* be present as FOREIGN KEY columns in all child tables. The more (and wider) those columns, the more you blow up space needed.
    Also, the more columns participate in a PRIMARY KEY, the more complex will be your code.
    You would have to specify all columns when JOINing tables. JOINs will also be slower when there are more columns to process.
    Another point arises when your PRIMARY KEY is also your clustered index. In that case, all clustered index keys (e.g. all your PK columns) are stored as part of the bookmark in each and every nonclustered index on that table.
    It depends, but when you have a natural composite PK on multiple columns, seriously evaluate the use of a surrogate PK. In terms of performance and maintenance you might be better this way.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page