{"id":98,"date":"2013-01-01T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/sql-server-index-column-order\/?shared=email&msg=fail"},"modified":"2013-01-01T00:00:00","modified_gmt":"2013-01-01T00:00:00","slug":"sharedemailmsgfail","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/01\/01\/sharedemailmsgfail\/","title":{"rendered":"SQL Server Index Column Order"},"content":{"rendered":"

At times I am\u00a0in a position to advise development teams on their practices. What I have learned from this experience is that there is always a demand for me to reduce what I tell them to\u00a0developer terms<\/em>. Before the developers reading this go grab their torches and pitch forks, I am not speaking negatively of developers in any way. I am acknowledging that the DBA\u00a0it depends<\/em> does not work well for how developers do their jobs.<\/p>\n

When I begin to describe the half a dozen different scenarios which impact how SQL Server behaves, I can see our developers\u2019 eyes glaze over. Sometimes\u00a0I am asked to simplify my explanation into a single rule which they can follow.\u00a0Having rules like these implemented as development standards is wonderful. They do not replace performance monitoring and tuning, however.<\/p>\n

The ordering of index columns is one case where it is difficult to nail down a bullet-proof standard. There is common advise that you should always order the columns from most selective to least selective. This advise is pretty good\u00a0but it is really inadequate in my opinion because there are queries which would not perform\u00a0optimally when this advise is followed.<\/p>\n

How column ordering affects your query<\/h2>\n

When the query optimizer is determining what index to use, it looks at the statistics that were available at the time. In the statistic, the histogram is only stored for the first column in the index key. This gives the best determination of column density or selectivity. Density is\u00a01\/(number of distinct values). For the other key columns, only the density of the left-based subsets are kept. In other words, the density is unknown for any column in the index unless all of the columns to the left of it are included in the predicates. Not filtering on the first column prevents the optimizer from knowing the density and may not chose to use that index, possibly resulting in a\u00a0table or index scan. The behavior is also different depending upon whether equality or inequality predicates are being used.<\/p>\n

Demo setup<\/h3>\n

To demonstrate these behaviors I will be using the AdventureWorks<\/a> database from Microsoft. I\u2019ve chosen to use the [Sales].[SalesOrderDetail] table based on its pre-loaded data but I have dropped all indexes from it and created a single, new, index.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

This index has the columns ordered with the most selective on the left and the least selective on the right, as seen by the reduced count of unique values found for\u00a0SalesOrderID<\/em>.<\/p>\n

\"\"<\/a><\/p>\n

To view the density vector for our index we will\u00a0use DBCC SHOW_STATISTICS.<\/a>\u00a0\u00a0Depending on your version of SQL Server, you may also use the DMF\u00a0sys.dm_db_stats_properties()<\/a>, explained here<\/a>.<\/p>\n

\"\"<\/a><\/p>\n

Equality predicates<\/h3>\n

Equality predicates are the easiest to understand because\u00a0the index will\u00a0likely be selected for use in the plan if you are filtering on one of the left-based subsets. You can see above, in the density vector, the three different combinations of columns that I will be able to get good performance out of.<\/p>\n

Single column filtering<\/h4>\n

If I query the rowguid<\/em> column, I can see that an efficient index seek is performed.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

If I query on another single column within the index, an index scan is performed instead. The index is still used but it is much less efficient because the entire index must be read rather than taking advantage of the B-tree structure to traverse down the index levels.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

Multiple column filtering<\/h4>\n

When filtering on more than one column, an index seek will be chosen if an entire left-based subset is included in the filter. In this example, I query all three columns in the index which produces the desired index seek operation.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

When I remove the SalesOrderDetailID\u00a0<\/em> from the filter, though, the operation that occurs is a seek with a predicate. First it will seek out all rows based on the\u00a0rowguid<\/em> column and then it will scan that entire result-set for the\u00a0SalesOrderID<\/em> value. The seek is unable to penetrate further than the\u00a0rowguid<\/em> because there is no density vector for\u00a0rowguid, SalesOrderID<\/em>.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

Inequality predicates<\/h3>\n

There is less of a chance of using index seeks with inequality predicates. There are two reasons for this.<\/p>\n