Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts

Wednesday, March 21, 2012

Index parameter

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!Anita (anonymous@.devdex.com) writes:
> 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 ?

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/techin.../2000/books.asp

Index out of range.....what is this??

I am getting an error:
"Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index"
when I run my report on the server. It works fine when it is run locally.
Does anyone know what this message means? Have I screwed up something?
Thanks,
CraigI found this reference,
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=214062&SiteID=1
Hope that helps!
"Craig" wrote:
> I am getting an error:
> "Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index"
> when I run my report on the server. It works fine when it is run locally.
> Does anyone know what this message means? Have I screwed up something?
> Thanks,
> Craig
>
>|||Yeah this kind of was the problem. I had a rogue line that was on the edge
of the report. Once I removed that line the exception disappeared. It must
have tried to print that line on the next page.
"daw" <daw@.discussions.microsoft.com> wrote in message
news:C3E2873F-B9A1-4089-B4B0-DB2B0AD94A21@.microsoft.com...
>I found this reference,
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=214062&SiteID=1
> Hope that helps!
> "Craig" wrote:
>> I am getting an error:
>> "Index was out of range. Must be non-negative and less than the size of
>> the
>> collection. Parameter name: index"
>> when I run my report on the server. It works fine when it is run
>> locally.
>> Does anyone know what this message means? Have I screwed up something?
>> Thanks,
>> Craig
>>