What is the difference between Clustered Index (CI) seek and Non Clustered Index (NCI) seek? Does one perform better than the other?

The reason I am asking this is because I have a table with 50 million rows and 150 columns. It has a column named ID defined as the clustered index. There is one more NCI on it with the same index key ID and seven include-d columns. It seems to me that the NC index is a duplicate here and can be safely dropped.

So I want some expert views/advice if it can be dropped safely or should keep it intact?

  • Are you saying that ALL the columns are included on the none clustered index? – Ian Ringrose May 6 '16 at 8:07
  • No. The table has 150 columns in it with ID column defined as CI and it has NCI index with again ID column as index key and 7 more columns in include. – SQLPRODDBA May 6 '16 at 8:18
  • Then Paul White seems to have given the correct answer, however I question if you should change the "base" table from a clustered table to a heap. Or even split the table up depending on how often different columns are updated, so as to reduce the cost of transaction logging. – Ian Ringrose May 6 '16 at 8:24
up vote 26 down vote accepted

A nonclustered index that has the same key(s)* as the clustered index, may still be useful, because the nonclustered index will usually be smaller and denser. Remember, a clustered index includes all in-row data, so it is normally the widest (least dense) index possible.

* The same key columns, in the same sequence, sorted the same way (asc/desc).

For a singleton seek (a seek using an equality predicate into a unique index), the main difference is the presumed greater chance of the index page(s) needed to find that single record being already in memory. All things being equal, a nonclustered index with 100 pages has a better chance than a clustered index stored on 10,000 pages.

For any other kind of seek except a singleton, there will be a scanning component as well. The scanning portion will also benefit from the greater density of the nonclustered index (more rows per page). Even if the pages must come in from persistent storage, reading fewer pages is faster.

You should check the system documentation, ask the person that added the index, and/or look for code comments to understand why this index exists. You also need to check the complete index definitions carefully, including compression, and look at index maintenance scripts. There could be other special reasons for having the nonclustered index (e.g. ability to rebuild online).

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.