locked
Primary key on a non clustered index: what is the point? RRS feed

  • Question

  • Hello,

    I always supposed that the way an index works, is that it stores a copy of the specified tables, and for each such row, it stores the PK to the row.

    I've seen a few indexes on my database where the PK column is explicitely mentioned, often (but not always) last.

    Is there a scenario where that is a good idea?

    EDIT :

    Ok, let me rephrase the question.

    I have a table

    CREATE TABLE SOME_DATA (
        record int PRIMARY KEY,
        creation datetime,
        columnA varchar(100),
        columnB varchar(100),
        columnC varchar(100),
        columnD varchar(100),
        columnE varchar(100),
        columnF varchar(100),
        columnG varchar(100),
        columnH varchar(100),
        columnI varchar(100),
        columnJ varchar(100),
    )
    
    CREATE INDEX IDX_TEST1 ON SOME_DATA (columnA, columnC, record)
    CREATE INDEX IDX_TEST2 ON SOME_DATA (columnA, columnC)
    
    Is there a scenario where IDX_TEST1 is more useful than IDX_TEST2 ?

    if a problem looks too big, break it into smaller objects



    • Edited by M0nkeyMaster Wednesday, April 4, 2012 1:01 PM format
    Wednesday, April 4, 2012 12:31 PM

Answers

  • SQL Server always creates a unique index on the primary key column(s).  Whether or not that index should be clustered and the order of composite key columns depend on how the table is queried.  Similarly, other index key columns (including whether the PK is part of a composite key) and the column order depend on queries. 

    Take a look at the execution plans of your critical queries to make sure indexes are used efficiently (touching the minimal amount of data needed for the task at hand).


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Kalman Toth Tuesday, October 2, 2012 8:31 PM
    Wednesday, April 4, 2012 12:45 PM
  • Since there can only be one clustered index for a table, it is a precious resource. The best use of a clustered index is to support business critical queries. One example: OnlineOrderDate (datetime) to support range queries.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    Contrary to common belief, PRIMARY KEY does not require clustered index.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Edited by Kalman Toth Tuesday, October 2, 2012 8:31 PM
    • Marked as answer by Kalman Toth Tuesday, October 2, 2012 8:31 PM
    Wednesday, April 4, 2012 12:55 PM
  • Data in table is logically sorted according clustered index key. It could be difficult to choose the right column for CI, for example assume you have a table with many columns along ID (identity) AND created_date column which is also is incremented.. Both are good candidates at glance right? But most of the queries you are running against the table with WHERE condition based on range of created_date column (WHERE created_date >@dt AND created_date <@dt..) CI is especially useful for range filtering so you probably need to create on this column and to enforce uniqueness  create PK on ID column and create unique NCI on this column.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Kalman Toth Tuesday, October 2, 2012 8:33 PM
    Wednesday, April 4, 2012 12:55 PM
    Answerer

All replies

  • As your Title goes..For each row in a non clustered index there is a primary key value which helps in pointing data(that should be unique obviously) other than that what you have specified in the non clustered index.

    It doen't really matters whether you explicitly define the PK within non clustered index or not, it will be there any how..

    For Details : http://msdn.microsoft.com/en-us/library/ms180978%28v=sql.100%29.aspx


    Thanks and regards, Rishabh , Microsoft Community Contributor

    Wednesday, April 4, 2012 12:40 PM
  • SQL Server always creates a unique index on the primary key column(s).  Whether or not that index should be clustered and the order of composite key columns depend on how the table is queried.  Similarly, other index key columns (including whether the PK is part of a composite key) and the column order depend on queries. 

    Take a look at the execution plans of your critical queries to make sure indexes are used efficiently (touching the minimal amount of data needed for the task at hand).


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Kalman Toth Tuesday, October 2, 2012 8:31 PM
    Wednesday, April 4, 2012 12:45 PM
  • Since there can only be one clustered index for a table, it is a precious resource. The best use of a clustered index is to support business critical queries. One example: OnlineOrderDate (datetime) to support range queries.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    Contrary to common belief, PRIMARY KEY does not require clustered index.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Edited by Kalman Toth Tuesday, October 2, 2012 8:31 PM
    • Marked as answer by Kalman Toth Tuesday, October 2, 2012 8:31 PM
    Wednesday, April 4, 2012 12:55 PM
  • Data in table is logically sorted according clustered index key. It could be difficult to choose the right column for CI, for example assume you have a table with many columns along ID (identity) AND created_date column which is also is incremented.. Both are good candidates at glance right? But most of the queries you are running against the table with WHERE condition based on range of created_date column (WHERE created_date >@dt AND created_date <@dt..) CI is especially useful for range filtering so you probably need to create on this column and to enforce uniqueness  create PK on ID column and create unique NCI on this column.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Kalman Toth Tuesday, October 2, 2012 8:33 PM
    Wednesday, April 4, 2012 12:55 PM
    Answerer
  • Putting the sorting of the data in the physical storage aside since we are talking about a non clustered pk. The importance of the   primary key is to explicitely define what makes a row unique. It can also provide performance benefits on queries that use this key for where clauses, join on clauses or simple reads that do not include other columns to name a few scenarios. It also allows you to create foreign keys from other tables that reference the primary key on the table in question since foreign keys can only reference Primary Keys or unique constraints. As you can see there are a ton of benefits of having a primary key even if its non clustered.

    Cheers,

    Luis @luisefigueroa

    Wednesday, April 4, 2012 1:10 PM