second field in index  
Author Message
downloadkid





PostPosted: Mon Nov 08 23:51:21 CST 2004 Top

SQL Server >> second field in index

If I have a unique index on fields A and B, and both A and B are
already very selective fields just not quite unique by themselves, and
then I do a select where B = 'abc' and that is the *only*
qualification, does SQLServer use the index to search even though B is
the second field?

Thanks.

Josh

ps - yes I should just try it and look at the plan, but I thought I'd
ask and maybe turn up any additional wisdom on the matter.

pps - OK it does an index scan. How 'bout that? Have I missed
anything?

SQL Server57  
 
 
Steve





PostPosted: Mon Nov 08 23:51:21 CST 2004 Top

SQL Server >> second field in index Josh,

With a unique index on (A,B), the query processor cannot use an index
seek on this index to select rows where B = 'abc' . That doesn't mean
it won't use this index - just not to seek directly. Depending on your
query and what other indexes exist, this index could still be used,
perhaps with an index scan, if it is the narrowest index containing the
values of B, and that's part of a low-cost query plan. But in general,
if you want to take advantage of an index to seek B = 'abc' directly,
you will need an index where B is the first column, such as on (B) or (B,A).

Steve Kass
Drew University



>If I have a unique index on fields A and B, and both A and B are
>already very selective fields just not quite unique by themselves, and
>then I do a select where B = 'abc' and that is the *only*
>qualification, does SQLServer use the index to search even though B is
>the second field?
>
>Thanks.
>
>Josh
>
>ps - yes I should just try it and look at the plan, but I thought I'd
>ask and maybe turn up any additional wisdom on the matter.
>
>pps - OK it does an index scan. How 'bout that? Have I missed
>anything?
>
>
>
 
 
AnthonyThomas





PostPosted: Tue Nov 09 01:03:02 CST 2004 Top

SQL Server >> second field in index If B itself is also highly selective, just not quite unique, yes, it will do
an Index Scan, however, you should also see parallelism and a stream
aggregation.

If you already have an index, unique or otherwise on (A, B), just create a
non-unique index on (B) by itself. The only reason you would create a (B, A)
index would be to create parallelism or some sort of covering index; however,
the (A, B) index woul fulfill that requirement in that case. So, don't waste
the storage.

Also, rarely should you create multi-attribute indexes regardless. In this
case, it is not the index but the Unique CONSTRAINT you are trying to
establish. Multi-attribute CONSTRAINTS are acceptable.

The case on indexes gets down to statistics and the fact that SQL Server
only stores the histogram on the first attribute. Moreover, with SQL 7.0 and
later, the Optimizer can make use of the intersection of multiple indexes per
query; so, it no longer makes as much since in having these highly
specialized, multi-columned indexes.

Sincerely,


Anthony Thomas




> Josh,
>
> With a unique index on (A,B), the query processor cannot use an index
> seek on this index to select rows where B = 'abc' . That doesn't mean
> it won't use this index - just not to seek directly. Depending on your
> query and what other indexes exist, this index could still be used,
> perhaps with an index scan, if it is the narrowest index containing the
> values of B, and that's part of a low-cost query plan. But in general,
> if you want to take advantage of an index to seek B = 'abc' directly,
> you will need an index where B is the first column, such as on (B) or (B,A).
>
> Steve Kass
> Drew University
>

>
> >If I have a unique index on fields A and B, and both A and B are
> >already very selective fields just not quite unique by themselves, and
> >then I do a select where B = 'abc' and that is the *only*
> >qualification, does SQLServer use the index to search even though B is
> >the second field?
> >
> >Thanks.
> >
> >Josh
> >
> >ps - yes I should just try it and look at the plan, but I thought I'd
> >ask and maybe turn up any additional wisdom on the matter.
> >
> >pps - OK it does an index scan. How 'bout that? Have I missed
> >anything?
> >
> >
> >
>
 
 
jxstern





PostPosted: Tue Nov 09 12:11:06 CST 2004 Top

SQL Server >> second field in index On Mon, 8 Nov 2004 23:03:02 -0800, "AnthonyThomas"

>If B itself is also highly selective, just not quite unique, yes, it will do
>an Index Scan, however, you should also see parallelism and a stream
>aggregation.

Numbers:

Seek on A: 3 reads
Seek on B: 200 reads
Scan table: 64000 reads

>If you already have an index, unique or otherwise on (A, B), just create a
>non-unique index on (B) by itself. The only reason you would create a (B, A)
>index would be to create parallelism or some sort of covering index; however,
>the (A, B) index woul fulfill that requirement in that case. So, don't waste
>the storage.

Hmm, interesting argument. "Obviously" right, on consideration!

>Also, rarely should you create multi-attribute indexes regardless. In this
>case, it is not the index but the Unique CONSTRAINT you are trying to
>establish. Multi-attribute CONSTRAINTS are acceptable.

I didn't follow this. Unless you're suggesting it follows from the
index intersection argument you give below. Hmm.

>The case on indexes gets down to statistics and the fact that SQL Server
>only stores the histogram on the first attribute.

Aha, oho, that explains why the execution plan estimate for the seek
on B seemed bogus.

> Moreover, with SQL 7.0 and
>later, the Optimizer can make use of the intersection of multiple indexes per
>query; so, it no longer makes as much since in having these highly
>specialized, multi-columned indexes.

Aha again. That's very interesting. I need to consider the
implications!

Thank you very much!

J.