lmu92 (3/24/2010)
Maybe this argument will help, too:Within one query, SQL server will only use one index per table. So, if you have the following query
SELECT col1, col2, col3, col4FROM table
WHERE col1='something' AND col2='something different'
and you have an index on each and every single column then the query optimizer may not consider any of those indexes, since a bookmark lookup would be required to get all values used in the SELECT clause.
It might be appropriate to use a single index on col1 and col2 with col3 an col4 as included columns.
SQL server will not combine various indexes for one table within one query.
Furthermore, it might even drop performance if the table in question is heavily used for updates/inserts/deletes, since each and every index would need to be changed.
Your statement is false. The optimizer can use more than one index in a select statement on a single table. From this location in Books Online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7c1f2130-5574-4058-bcfb-31c115e9bd00.htm)
--------------------
Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query. Consider the sample query:
SELECT * FROM lineitem
WHERE partkey BETWEEN 17000 AND 17100 AND
shipdate BETWEEN '1/1/1994' AND '1/31/1994'
SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection.
--------------------
I also note that this thread has nothing to do with indexes - it is about statistics.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service