Multiple column indexes optimization for multiple column queries on SQL Server

Question

I have one table [table] with two columns that needs to be filtered: [column1] and [column2].

In my program I execute a query like:

select * from [table] where [column1] = 'foo' and [column2] = 'bar';

Which is faster:

1. Creating two indexes, one on each column. ([column1] and [column2])
2. Creating one index containing both columns. ([column1]+[column2])

This question have been bugging me for a while, I have no idea how query optimization works and how SQL Server uses the created indexes to speed up queries.

Answer 1

Second one is ALWAYS faster for this query - but you need to put the more selective one first (in the order of indexes) to benefit more. The only exception is if for performance reasons, SQL decides to use clustered index so ignores the non-clustered.

The combination of two values create a much more selective criteria. Also it helps with performance since there is no BOOKMARK LOOKUP required on a covering index.

Bookmark lookups are the source of major performance degradation and that is why covering index is always better than 2 indexes.

UPDATE

Bear in mind, if you have your index as column1+coulmn2, searches on just column2 cannot use this index so you will need a separate index on column2 as well.

Answer 2

It depends!

It depends on the selectivity of those columns.

If you were not selecting all columns '*', you might be able to utilise a really fast covering index, comprised of the where clause columns and INCLUDE'ing the columns in the SELECT list.