SQL 2005, indexes on WHERE clause.

[xrapidx]

quick question - trying to explain something to someone, and can't quite recall exactly.

If I create THREE different indexes on three different columns on a SQL Table as follows:
[Column A] idx_a
[Column B] idx_b
[Column c] idx_c

And then run the following query:
SELECT *
FROM

WHERE [Column A] = 1
AND [Column B] = 1
AND [Column C] = 1

Only one of the indexes created will be used in that query? Is that correct?!

Not near SQL to check - and google isn't returning what I want.

 

[dequadin]

AFAIK that is correct.

The SQL query optimizer will only use one index per query, hopefully it'll pick the best one.

 

[xrapidx]

Thats what I thought - but needed to double check for MS SQL.

 

[Angstrom]

I'm not sure this is entirely correct. It seems to depend entirely on the optimizer. In addition, the cardinality of the data plays a big part in the query plan.

Google 'Index Intersection'.

I realise the following info is dated, but it may still be pertinent in current SQL Server releases:

http://technet.microsoft.com/en-us/library/dd316335.aspx

However, both SQL Server 7.0 and SQL Server 2000 support index-intersection joins, which should provide most of the benefits of bitmap indexes.

So for example, if you have a large table with a Gender column (2 values) and a StateId column (50 values), you can create individual indexes on each of these columns. If then you have a query that says WHERE Gender = 'F' and StateID = 'WA' , SQLServer is capable of using both indexes and intersecting the matching rows from each (using a join) to find the qualifying rows.

 

[xrapidx]

Interesting - thanks - I'll have to do some more reading.