Multiple Column Indexed Select?

There is a large table with a multiple-column index. What is the
proper way to search using these columns so the multiple-column index
is used?
Thanks.
Can you be a little more specific? You normally don't have to do anything
special for sql server to utilize an index as long as it is useful for the
query. Whether or not the index is used depends on the query, the index,
how many rows in the table, how selective the index / query is etc, etc. If
you post the DDL for the table and the query you are looking to issue maybe
we can make a suggestion.
Andrew J. Kelly SQL MVP
"localhost" <primpilus@.cohort.ces> wrote in message
news:jd4cr05eucf55m6kq2rq716e8kpkqb1069@.4ax.com...
> There is a large table with a multiple-column index. What is the
> proper way to search using these columns so the multiple-column index
> is used?
> Thanks.
>
|||localhost wrote:
> There is a large table with a multiple-column index. What is the
> proper way to search using these columns so the multiple-column index
> is used?
> Thanks.
Nothing really. You just need to be aware that in order to use the
index, you have to include columns in the query from left to right (as
they appear in the index). You don't need to include all columns in the
query, but once you skip a column, the index is only useful up to that
point.
For example:
Index: (col1, col2, col3)
Query: Where col1 = 5 and col2 = 6 and col3 = 7 (index used to its
fullest)
Query: Where col1 = 5 and col3 = 7 (index only uses col1)
Query: Where col2 = 5 and col1 = 6 (index uses col1 and col2)
Query: Where col3 = 7 (index not used at all)
Query: Where col2 = 7 (index not used at all)
Query: Where col1 = 7 (index uses col1)
But Andrew's comments are correct. Just because a query can make use of
an index, doesn't mean it will be used.
David Gugick
Imceda Software
www.imceda.com