Nonclustered indexes are secondary indexes used to help the performance of queries not served by the clustered index. 

You’re typically going to want to add a nonclustered index to the column that’s in your WHERE clause and your most commonly run queries.

You want to use these involved on columns that are in joins and grouping operations, so where we’re doing a GROUP BY. Columns that are in the WHERE clause and columns that have lots of distinct values. So, a high cardinality level will benefit from a nonclustered index because you’re going to be able to do seeks on those pages that contain those data values. 


Cost that’s associated with nonclustered indexes is space, and then insert and update performance. As you’re updating or inserting data into your base table and your clustered index; which happens at the same time, so there’s no penalty for that; each nonclustered index, if the data from that column is inserted, will have to receive an insert. And this can cause what’s known as write amplification.


Last modified: March 16, 2019



Write a Reply or Comment