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

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 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,
> 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

Question on Indexes...errr, indices...

Hey all,
Lets say I have stored proc "x" and "x" pulls it's data from a certain
table. However, on that table there are 3 indices and each index
contains the field specified in "x"s WHERE clause.
How does SQL Server decide which query to use? IOW, I guess my root
question is, what is the best way to design indices to get maximum
efficiency?SQL Server looks at a variety of factors, including index statistics, what
columns are present in the index other than the key column (i.e. if the
index "covers" the query), and various other factors. There are lots of
good articles at http://www.sql-server-performance.com on this and related
topics.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
<roy.anderson@.gmail.com> wrote in message
news:1108562342.303000.70650@.l41g2000cwc.googlegroups.com...
> Hey all,
> Lets say I have stored proc "x" and "x" pulls it's data from a certain
> table. However, on that table there are 3 indices and each index
> contains the field specified in "x"s WHERE clause.
> How does SQL Server decide which query to use? IOW, I guess my root
> question is, what is the best way to design indices to get maximum
> efficiency?
>