none
non clustered index with pk vs non clustered index without pk RRS feed

  • Question

  • Hi we run 2017 standard. I'm looking at some tables whose non clustered (nc) indexes are definitely being utilized in the execution plan but there is no pk and no clustered index.

    Does an nc index perform as well on a table that doesn't have a pk?  Say in comparison with everything being equal except that the second table has a pk that is the clustered index?

    Tuesday, August 13, 2019 6:05 PM

Answers

  • I'm afraid the only possible answer is "it depends". You can find a pair of tables where the table with PK wins. You can find those where it does not matter at all. And those where the tabls without the PK have better performance.

    There is not really anything magic about a primary key. It is a unique index where all key columns are NOT NULL. That's all.

    It may be a little more important whether the table has a clustered index or not, since the CI keys serve as row locators. Since they are implicitly included in the NC indexes, this can make some indexes covering and therefore cause some queries to run faster. On the other hand, if you have a query that scans the columns explicitly defined in the indexes, adding a wide clustered index will decrease the performance of those queries, since the index now stretches over more pages.

    Generally, tables should have a primary key, preferrably defined from natural keys to avoid duplicate data entry. And the way that SQL Server is designed and works, having a clustered index means that you go with the flow and you will have less nasty surprises. This does not have to be true with other platforms where heaps may be the norm.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 13, 2019 9:19 PM
  • As Erland says "it depends", but in general it's going to lose for one or more reasons.

    And in general, SQL Server is "unhappy" if you don't have a clustered index, whether or not it's a PK.

    Sometimes a clustered index is very valuable because the order of the index is the order of the physical rows, then you get more efficient access by that key.  You lose that if you have *no* clustered index.

    In SQL Server, if no other ideas present themselves (which is going to be rare in a well-normalized database), people add an identity int field and make that the clustered PK, and then if you have a bunch of nonclustered indexes this is about as efficient as SQL Server gets, because all nonclustered indexes point *through* the clustered index.

    Josh

    • Marked as answer by db042190 Thursday, August 15, 2019 12:18 PM
    Tuesday, August 13, 2019 11:49 PM
  • thx, when there is no clustered index, how is a row located via nci? some internal page + row identifier?

    Yup. It's called RID.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Thursday, August 15, 2019 12:18 PM
    Wednesday, August 14, 2019 8:58 PM
  • In SQL Server, if no other ideas present themselves (which is going to be rare in a well-normalized database), people add an identity int field and make that the clustered PK,

    In a well-designed database there are occasional tables with surrogate keys, IDENTITY, sequences or roll-your-owns. If you find them all over the place, there is something wrong in the design.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Thursday, August 15, 2019 12:19 PM
    Wednesday, August 14, 2019 9:02 PM

All replies

  • I'm afraid the only possible answer is "it depends". You can find a pair of tables where the table with PK wins. You can find those where it does not matter at all. And those where the tabls without the PK have better performance.

    There is not really anything magic about a primary key. It is a unique index where all key columns are NOT NULL. That's all.

    It may be a little more important whether the table has a clustered index or not, since the CI keys serve as row locators. Since they are implicitly included in the NC indexes, this can make some indexes covering and therefore cause some queries to run faster. On the other hand, if you have a query that scans the columns explicitly defined in the indexes, adding a wide clustered index will decrease the performance of those queries, since the index now stretches over more pages.

    Generally, tables should have a primary key, preferrably defined from natural keys to avoid duplicate data entry. And the way that SQL Server is designed and works, having a clustered index means that you go with the flow and you will have less nasty surprises. This does not have to be true with other platforms where heaps may be the norm.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 13, 2019 9:19 PM
  • As Erland says "it depends", but in general it's going to lose for one or more reasons.

    And in general, SQL Server is "unhappy" if you don't have a clustered index, whether or not it's a PK.

    Sometimes a clustered index is very valuable because the order of the index is the order of the physical rows, then you get more efficient access by that key.  You lose that if you have *no* clustered index.

    In SQL Server, if no other ideas present themselves (which is going to be rare in a well-normalized database), people add an identity int field and make that the clustered PK, and then if you have a bunch of nonclustered indexes this is about as efficient as SQL Server gets, because all nonclustered indexes point *through* the clustered index.

    Josh

    • Marked as answer by db042190 Thursday, August 15, 2019 12:18 PM
    Tuesday, August 13, 2019 11:49 PM
  • thx, when there is no clustered index, how is a row located via nci? some internal page + row identifier?
    Wednesday, August 14, 2019 4:12 PM
  • thx, when there is no clustered index, how is a row located via nci? some internal page + row identifier?

    Yup. It's called RID.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Thursday, August 15, 2019 12:18 PM
    Wednesday, August 14, 2019 8:58 PM
  • In SQL Server, if no other ideas present themselves (which is going to be rare in a well-normalized database), people add an identity int field and make that the clustered PK,

    In a well-designed database there are occasional tables with surrogate keys, IDENTITY, sequences or roll-your-owns. If you find them all over the place, there is something wrong in the design.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Thursday, August 15, 2019 12:19 PM
    Wednesday, August 14, 2019 9:02 PM