The Importance of First Column in Nonclustered Index

Does it matter which column is first in an Index? Yes it does, simply because using different columns can result in different execution plans. 

Introduction

Let’s consider the below select statement – first we will create an index named [IX_OrderDate_ProductKey] on OrderDate and ProductKey and then an index [IX_ProductKey_OrderDate] on ProductKey and OrderDate.

Comparing the Results

Now, comparing the two indexes, notice that both have different order.

The execution plans after running the previously mentioned select statement:

Query Cost: 22%
Total time to Execute: 36ms
Logical Reads: 80
Query Cost: 78%
Total time to Execute: 826ms
Logical Reads: 287

Notice that the index [IX_OrderDate_ProductKey], resulted in an Index Seek, whereas the index [IX_ProductKey_OrderDate] resulted in an Index Scan. This is because SQL Server is using statistics to decide which is the best plan to execute your query. Since the statistics didn’t contain any useful information about the second query, SQL Server had to decide that an Index Scan was the only way to solve the query. 

The Statistics

Since you are wondering how statistics can impact execution plan decisions, let’s compare the difference between the statistics for the above indexes. You can use the below command to view these statistics:

Notice that for the [IX_OrderDate_ProductKey] index SQL Server created a RANGE_HI_KEY bucket for OrderDate. This allows SQL Server to know exactly where the OrderDate > ‘2013-04-30’ is located, resulting in an Index Seek. On the other hand, the [IX_ProductKey_OrderDate] index SQL Server created a RANGE_HI_KEY on ProductKey. This column has no meaningful information on where the location of OrderDate > ‘2013-04-30’ could be, resulting in an Index Scan

Conclusion

It is very essential to plan index creation properly and choosing the right column can improve query performance significantly.

You may also like...

1 Response

  1. Nice! True about rowstore indexes, in nonclustered columnstore order of attributes doesn’t matter because of completely different internal structure.

Leave a Reply

Your email address will not be published. Required fields are marked *