When to put a non-clustered index on your clustering key

First off, let’s remember the difference between clustered & nonclustered 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 (but these included columns don’t affect order).

Should I ever have both on the same columns?

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.

I’ll also note that SQL Server will tend to make the same decision on a seek: If two indexes are both covering, it will choose to use the one that takes up the least space.

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.

OK, I don’t have that corner case, which one should I drop?

As a general rule, most tables should have a clustered index. (That’s a whole other topic). Without 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.

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.

The post When to put a non-clustered index on your clustering key appeared first on Andy M Mallon - AM².