How SQL Server Indexes are Chosen

This is probably the biggest problem most people have with understanding indexes.  How exactly does SQL Server decide when to use an index?  There’s two big errors that people make here: assuming it’ll always “just work” (it doesn’t) or that they should just force it to use the indexes they think it should be using (it’s nearly always slower if you do this).  Instead, it’s best to understand the selection process and see if you can structure your query in such a way that it accesses the indexes you want.

First things first.  It’s never going to use two indexes on the same table access.  If you use a table twice in the same query, it might use different indexes, but otherwise you should only ever see one index.  Next, and this is the really important bit: column order matters.  If you’ve got an index on three columns and your query uses two columns but not the first column, it won’t use the index.  (If you use the first and third, it might use it, but it’ll score it the same way as just using the first.)

Other considerations:

• As discussed previously, it might decide to use index covering.  If the columns match up in order as well, so much the better.
• If statistics are out of date, it can get its decisions wrong.
• If you join two columns and they’re not of the same type, it can make the wrong decision.  I’ve seen this happen when the only difference is nullability, but not in recent versions.
• Inequalities aren’t as selective as equalities.  Typically, given the choice between an index and a greater than and an index with an equals, it’ll choose the equals.  Again, this is driven by the statistics.
• If the table is very small, it might be faster to do a table scan.
• In very rare circumstances, it’ll build an index and then query it.  This almost never happens; you usually have to break up your query and create temporary tables to achieve this effect.

But the most important thing to remember is: it will only score the index based on the first columns in the index that contiguously match your query.  Miss out one column and it’ll just ignore any subsequent ones.  Miss out the first column and it’ll probably never use your query except for index covering.