Choosing which index is best

Can sql server ever choose to use 2 indexes for a query?
tia
--
MGHurme,
Yes, certainly, from different tables, but you probably meant 'from the same
table', which is also Yes. In earlier versions, SQL Server would only use 1
index per table, but since SQL Server 7.0 it can use multiple indexes per
table at the determination of the optimizer. (Maybe some limited cases
earlier, but I don't remember any more.)
RLF
"Hurme" <michael.geles@.thomson.com> wrote in message
news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG|||In addition to Russell's response: SQL Server supports index AND-ing and
index OR-ing.
A typical example is a query like this:
SELECT *
FROM my_table
WHERE lat BETWEEN 4 AND 6
AND long BETWEEN 10 AND 12
If there is an index on my_table(lat) and an index on my_table(long),
then the optimizer might choose to seek and partially scan both indexes
and intersect the results.
However, in my experience this feature is not used much, because in many
potential situations a different approach is faster, especially if there
is a similar compound index. For the example that could be an index on
my_table(lat,long).
For index OR-ing, the 'cheaper' alternative could be a (clustered) index
scan.
Bottom line: you need really selective predicates for index AND-ing or
OR-ing to kick in.
Gert-Jan
Hurme wrote:
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG|||> (Maybe some limited cases earlier, but I don't remember any more.)
I believe that old architecture could use 2 indexes for OR, like below
WHERE col1 = 23
OR col2 = 34
But above was only case. Index intersection (AND) was added in 7.0 (along po
ssibly with other new
cases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eofv5zg1HHA.536@.TK2MSFTNGP06.phx.gbl...
> Hurme,
> Yes, certainly, from different tables, but you probably meant 'from the sa
me table', which is also
> Yes. In earlier versions, SQL Server would only use 1 index per table, bu
t since SQL Server 7.0
> it can use multiple indexes per table at the determination of the optimize
r. (Maybe some limited
> cases earlier, but I don't remember any more.)
> RLF
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
>