Index parameter

[Anita]

Hi All,

I have a table with an index. It contains column a to
column m. There are two queries that often run :
Query 1: select * from tb where a = '01' and b = '51'
Query 2: select * from tb where a = '01' and
b = '51' and c = 'yy' and d = 'zz'

If I must create only one index and (a+b) may
contains duplicate values. Which ones of the
following index parameters is better for the
two queries above ? :
Parameter1: column a, column b
Parameter2: column a, column b, column c, column d

If (a+b) is unique, which ones should I choose ?

Could anyone help me

Thanks in advance

Anita Hery





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Index parameter

Anita (anonymous@devd ex.com) writes:[color=blue]
> I have a table with an index. It contains column a to
> column m. There are two queries that often run :
> Query 1: select * from tb where a = '01' and b = '51'
> Query 2: select * from tb where a = '01' and
> b = '51' and c = 'yy' and d = 'zz'
>
> If I must create only one index and (a+b) may
> contains duplicate values. Which ones of the
> following index parameters is better for the
> two queries above ? :
> Parameter1: column a, column b
> Parameter2: column a, column b, column c, column d
>
> If (a+b) is unique, which ones should I choose ?[/color]

The last question is the easiest to address: an index on a+b should
be sufficient.

If a+b are not unique, the answer is: it depends. The fewer columns
you have in the index, the smaller are in the index pages, and it's
easier to find rows in it. But on the other hand, if a+b is not
very selection, so for each combination of a+b there may be thousand
of rows, Query 2 may not benefit from the index.

It also matter whether the index is clustered or not. And if the
index is non-clustered, the clustered index matters, since the
clustering keys appears in the non-clustered index.

Finally, consider this query:

SELECT h FROM tb WHERE a = '01' AND b = '51'

For this query, a non-clustered index on (a, b, h) can be very useful,
since this is a covering query.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp