This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Does it ever make sense to have a clustered and non clustered index for the same column
Question
Does it ever make sense to have a clustered and non clustered index for the same column
I inherited a database and a web service that goes with it.
Looking at the indexes for duplicates I see an two indexes that point at the same column (1 column and sorted the same way). One is clustered and one is not, that is the only difference I see.
- Does it ever make sense to keep both of these?
- If I delete one which one should I delete
asked 2016-08-16 by Maestro1024
Answer
Before addressing the two questions, let me briefly define the difference between clustered & non-clustered indexes.
The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, it has the entire row).
The nonclustered index is also organized by the key columns. It implicitly includes the clustering key columns (if the table is clustered), or a pointer to the row (if the table’s a heap). If any INCLUDE columns are explicitly specified, they will also be included in the index structure.
Does it ever make sense to keep both of these?
There is a corner case where it makes sense to have a non-clustered index “duplicating” the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index. The non-clustered index does not contain the full row data, and thus it will take up less physical space. Because it takes up less physical space, SQL Server can scan the table with fewer IOs, and will make use of it for performance reasons.
You can query sys.dm_db_index_usage_stats, you can see the number of user_seeks and user_scans on the two indexes. This will help you see how the two indexes are being used, and determine the usefulness of the two indexes.
If I delete one which one should I delete?
As a general rule, most tables should have a clustered index. (That’s a whole other topic). Not knowing any details about your index usage or data access, I’d guess that if you were to drop one, you would drop the nonclustered index (but, that really is a guess based on what I know).
Depending on the clustering key, the data, the data access patterns, etc., the most correct answer might be to drop the clustered index and create a different clustered index. You may want to read more on Effective Clustered Indexes.
answered 2016-08-17 by Andy Mallon