locked
why should primary key be the first column in a table RRS feed

  • Question

  • Hi,

    I'm trying to build a case to alter a table in production with a composite key . I have  PK columns at the start of table which is how it should be , but the issue is down the line , another column was added at the end and the same was added to composite key .

    issue is as PK arranges records physically where it creates a clustered index, as far as my knwoledge goes,  data is ordered initially by the three fields (composite keys) and then ordered again by another composite key field as they are not defined in a set , will this cause any performance bottle necks as it's a stage table to populate a fact if so how ??

     below is the table structure..

    Any help  in this regard is appreciated :)

    Thursday, April 17, 2014 2:55 PM

Answers

  • PRIMARY KEY as first column is just a convention.

    The PRIMARY KEY columns can be anywhere in a table.

    I don't see any advantage to the 5-column PRIMARY KEY.

    I would design it with INT IDENTITY (or SEQUENCE OBJECT) SURROGATE PRIMARY KEY.

    You can make the 5-columns a UNIQUE key or unique index.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Proposed as answer by pascual79 Thursday, April 17, 2014 3:06 PM
    • Edited by Kalman Toth Thursday, April 17, 2014 3:07 PM
    • Marked as answer by vishal.... _ Thursday, April 17, 2014 3:20 PM
    Thursday, April 17, 2014 3:03 PM

All replies

  • PRIMARY KEY as first column is just a convention.

    The PRIMARY KEY columns can be anywhere in a table.

    I don't see any advantage to the 5-column PRIMARY KEY.

    I would design it with INT IDENTITY (or SEQUENCE OBJECT) SURROGATE PRIMARY KEY.

    You can make the 5-columns a UNIQUE key or unique index.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Proposed as answer by pascual79 Thursday, April 17, 2014 3:06 PM
    • Edited by Kalman Toth Thursday, April 17, 2014 3:07 PM
    • Marked as answer by vishal.... _ Thursday, April 17, 2014 3:20 PM
    Thursday, April 17, 2014 3:03 PM
  • It does not necessarily be the first column. For practical significance you may have to place it as a first column.

    Otherwise, you can make it the last column. The crucial point you should simply note is every row in a table is an element of a set. And each element in the set must have something to specifically identify that element. And for identifying elements we use primary key.

    Just like every soccer player has its number, every element in the set has a primary key. Note that the set is composed of all the rows in a table and the name of the set is the name of the table.


    ebro

    Thursday, April 17, 2014 3:04 PM
  • A primary key does NOT arrange the rows in order. A clustered index does.  A PK is not required to be a clustered index, this is simply the default in the UI.

    The order you see the fields on the screen has no impact on the way the clustered index works.

    Thursday, April 17, 2014 3:14 PM
  • Thank you ...
    Thursday, April 17, 2014 3:20 PM