I like the approach and style to problems/issues/subject. I have been a DBA for 10 years now and I love to know the internals,understand and apply them to resolve the issues.

DHARMA9/17/2007 8:37:37 PM

Hi Alex

I'm sorry for the delay in responding.

It turns out that slight differences in your configuration might make SQL Server's optimizer choose different plans for these queries. I tested it on several different instances to confirm the behavior I was seeing, but apparently your machine was different. It's also possible that you have performed other testing in the Northwind database to change the values in the Order Details table.

Explaining the reason for the possible difference in behavior of these two queries was actually not the purpose of this article. In the article I was just pointing out that understanding this behavior would be one of the benefits of understanding how SQL Server really works.

I have written numerous articles on indexes, and the difference between clustered and nonclustered indexes, and what kinds of queries each is good for. I invite you to search the archives of SQL Server Magazine for my past articles on the use and behavior of indexes to get many more details.

However, the short answer is that nonclustered indexes are only useful if the optimizer determines that only a few rows will satisfy the query. One of these queries returns more rows than the other, and the optimizer determine that one of the queries returned to many rows to use the nonclustered index effectively and decided it would be more efficient to just scan the table.

Thanks for writing!
Kalen Delaney

KALEN1/12/2007 11:46:08 AM

Hi Kalen,

Thanks for your advice about us learning about internals.

I ran these two queries:

SELECT * FROM [Order Details]
WHERE ProductID in ( 9, -9)

SELECT * FROM dbo.[Order Details]
WHERE ProductID in ( 5, -5)

and both query plans show a nonclustered index on ProductID being used to find the relevant rows.

I don't see why one would use non-clustered indexes and the other one a cluster index scan.

Thanks for your time and your teachings.

Alex

John Alexander11/2/2006 2:29:28 PM