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 than
using a index, it would not use the index?.

MVPs, please enlighten me on this scenario.

Thanks

M

If  you have a clustered index on a table, you wouldn't see a table scan, but
a clustered index scan.

Whether the optimizer picks a scan of a seek 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 seek; 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 seek would be better).

If you also have nonclustered indexes on the table, you could also see a
seek 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.

Show quoteHide quote

On Thu, 2 Feb 2006 12:20:28 -0800, Meher wrote:

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 seek for
an exact match. For a composite index, this can still result in more
than one row, so it's technically still an index seek 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 seek 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.

How can you prevent the data changes message during editing a row
getting dates between start date and end date
how to optimize this procedure
Oracle "Dual" table equivalent in SQL Server
Conditional Where Clause