Composite Index and Query Optimizer

Hi
I have a question. If I have a composite primary key on table (in essence I
will have a composite clustered index as PK by default creates a clustered
index) when would the Query optimzer would not use the index on it?. Is it
like if the Query optimzer thinks that a table scan would be much faster tha
n
using a index, it would not use the index?.
MVPs, please enlighten me on this scenario.
Thanks
MIf you have a clustered index on a table, you wouldn't see a table scan, bu
t
a clustered index scan.
Whether the optimizer picks a scan of a s depends on a lot of things,
like what columns the select statement retreives, the nature of the WHERE
condition (exact matches are more likely to result in a s; operators like
LIKE or BETWEEN a scan), and the number of rows that statistics on the table
indicate would be returned by the SELECT. (For a query that statistics
indicate would return a large number of rows, the optimizer may choose an
index scan, even when you would think a s would be better).
If you also have nonclustered indexes on the table, you could also see a
s on the nonclustered index with a bookmark lookup to the clustered index
.
Again, where the optimizer chooses that path depends on the same things
mentioned above.
"Meher" wrote:

> Hi
> I have a question. If I have a composite primary key on table (in essence
I
> will have a composite clustered index as PK by default creates a clustered
> index) when would the Query optimzer would not use the index on it?. Is it
> like if the Query optimzer thinks that a table scan would be much faster t
han
> using a index, it would not use the index?.
> MVPs, please enlighten me on this scenario.
> Thanks
> M|||On Thu, 2 Feb 2006 12:20:28 -0800, Meher wrote:

>Hi
>I have a question. If I have a composite primary key on table (in essence I
>will have a composite clustered index as PK by default creates a clustered
>index) when would the Query optimzer would not use the index on it?. Is it
>like if the Query optimzer thinks that a table scan would be much faster th
an
>using a index, it would not use the index?.
Hi Meher,
Mark already gave some answers, but he was mistaken on one point and he
forgot one point.
Here's all you ever wanted to know but were afraid to ask:
- Search for equality (Col01 = 'Abdef'), and you get an index s for
an exact match. For a composite index, this can still result in more
than one row, so it's technically still an index s for a range of
rows.
- Search for a range, for instance with BETWEEN, with < and/or >
comparisons operator, with LIKE (but only if the first character is not
a wildcard - LIKE 'abc%' is good; LIKE '%abc' isn't), and you get an
index s for a range of matching values.
- An index scan will be used if the indexed value is embedded in an
expression (WHERE Col02 + 17 = 34), for a LIKE that starts with a
wildcard (LIKE '%abc' or LIKE '_abc'), and you get a clustered index
scan (which is basically the same as a table scan).
- Also, if you search for a value in the second column of a composite
index, a table scna has to be used. Compare this to finding everyone
living on Elm Street in a phone book that is sorted by last name, with
street as secondary sort order for people with equal last name.
Hugo Kornelis, SQL Server MVP