Will my index be used if all columns are not used?

Question

I have an index on columns A, B, C, D of table T

I have a query that pulls from T with A, B, C in the WHERE clause.

Will the index be used or will a separate index be needed that only includes A, B, C?

Answer 1

David B is right that you should check the execution plan to verify the index is being used.

Will the index be used or will a separate index be needed that only includes A, B, C?

To answer this last part of the question, which I think is the core underlying topic (as opposed to the immediate solution), there is almost never a reason to index a subset of your indexed columns. If your index is (A, B, C, D), a WHERE against (A, B, C) will most likely result in an index seek, which is the ideal situation -- the index includes all the information the engine needs to go directly to the result set. I believe this is holds true for numeric types and for equality tests in string types, though it can break down with LIKE '%'s). On the other hand, if your WHERE only referenced D, you would most likely end up with an index scan, which would mean that the SQL engine would have to scan across all combinations of A, B, and C, and then check whether D met your criteria before deciding whether to add the row to the result set. On a particularly large table, when I found myself having to do a lot of queries against column "D", I added an additional index for D only, and saw about 90% performance improvement.

Edit: I should also recommend using the Database Engine Tuning Advisor in SQL Management Studio. It will tell you if your table isn't indexed ideally for the query you want to run.

Answer 2

It depends!

WHERE A like '%x%'
  and B = 1
  and C = 1
//
WHERE A = 1
  OR B = 1
  OR C = 1
//
WHERE DateAdd(dd, 1, A) = '2008-01-01'
  AND B = 1
  AND C = 1

These will not rely on the index, because the index is not useful.

Click on "display estimated execution plan" to confirm potential index usage.