Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involve
d,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)

> TIA,
> Norbert