I have a table with millions of rows and something like 20 columns. This table have an unique identifier as primary key, but the most likely query is based on three varchar columns (col1 and col2 of type nvarchar(35) and col3 of type nvarchar(14)). For a given {col1, col2, col3} correspond at maximum 1 row. col3 is the less fragmented column. In the vast majority of cases, the table will be stressed with query like this:

    WHERE col1 = "xxx0" AND col2 = "yyy0" AND col3 = "zzz0"
    OR col1 = "xxx1" AND col2 = "yyy1" AND col3 = "zzz1"
    OR col1 = "xxxn" AND col2 = "yyyn" AND col3 = "zzzn"

Given this situation? What will be a good indexing strategy? Multi-column with col3 as first column? Three separated indexes?

  • You need to use single quotes, not double for string literals: WHERE col1 = 'xxx0' – ypercubeᵀᴹ Apr 29 '16 at 16:15
  • Little typo :-) – BAD_SEED Apr 29 '16 at 16:18

A composite index with any order of col1,col2,col3 would work here. The query you have shown in the question would be able to perform three seeks into it for the three or-ed predicates.

To determine the best order out of the 6 possible permutations I would first look at other queries, or alternatively you could look at the missing index DMVs. Quite likely you will find that the choices you make optimising this one query can help optimise others too.

If you have queries that perform equality seeks using just two of the columns then put them first so the same index supports those queries. And similarly if one of those columns is often used on its own in an equality or range predicate then make that the leading one.

Though if any of the columns is not particularly selective you would need to verify that the index is actually used for the query identified by the process above and consider whether it is worth adding included columns to make it covering if not (or considering a different index order that would benefit a different query).

| improve this answer | |

You'll want an index with all three columns in your Key Values:

ON dbo.*YourTable* (col1, col2, col3) --Key Values
INCLUDE (*Any columns commonly selected here*); --Included Columns

The order of the columns 1-3 is up to you, based on what is most important to your system. SQL Server generates statistics based the order of Key Values. This is called the Density Vector. The index will evaluate how specific your first key value is, and attempt to provide statistics on that key value, along with the combination of that key value, and your 2nd value, and then more statistics on the combination of all three values. There is much more to SQL Server Statistics than I can provide in this message, so I will link some resources if you want to know more:

I'm a fan of Kimberly Tripp's explanation of statistics here.

Official Microsoft Statistics page: Books Online

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.