locked
identity RRS feed

  • Question

  • Hi all,

    Why should you not put a clustered index on your Identity column?

    Friday, March 29, 2013 5:08 AM

Answers

  • Please read the below article:

    http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Bharat.Bhushan01 Friday, March 29, 2013 7:02 AM
    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:37 PM
    Friday, March 29, 2013 5:32 AM
  • Why should you not put a clustered index on your Identity column?

    The real question is what key(s) should you use for the clustered index.  A table can have only one clustered index so the choice is an important one because it determines the logical order of the table and the clustered index key columns are included in all non-clustered indexes too. 

    The answer is that it depends on your queries and workload.  For example, if you have an Orders table and most queries are by CustomerID (e.g. customers often login to your web site to query their order status and performance of these queries is paramount), it may be appropriate to create the clustered index on CustomerID (and perhaps OrderDate) instead of an OrderID identity column.  This would be a case where the clustered index should not be the identity column.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:37 PM
    Sunday, March 31, 2013 6:13 AM

All replies

  • Please read the below article:

    http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Bharat.Bhushan01 Friday, March 29, 2013 7:02 AM
    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:37 PM
    Friday, March 29, 2013 5:32 AM
  • Why should you not put a clustered index on your Identity column?

    The real question is what key(s) should you use for the clustered index.  A table can have only one clustered index so the choice is an important one because it determines the logical order of the table and the clustered index key columns are included in all non-clustered indexes too. 

    The answer is that it depends on your queries and workload.  For example, if you have an Orders table and most queries are by CustomerID (e.g. customers often login to your web site to query their order status and performance of these queries is paramount), it may be appropriate to create the clustered index on CustomerID (and perhaps OrderDate) instead of an OrderID identity column.  This would be a case where the clustered index should not be the identity column.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Maggie Luo Sunday, April 7, 2013 3:37 PM
    Sunday, March 31, 2013 6:13 AM