Difference between clustered index seek and non clustered index seek

Question

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?

Answer 1

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).