Posted by Erland Sommarskog on 06/28/07 20:35

db55 (chfran@gmail.com) writes:
> How does the phrase "Is Not Null" in the where clause effect the
> effectiveness of a query?

About the same as "col = 3" will.

That is, if you say

SELECT * FROM tbl WHERE col IS NOT NULL

and col has a non-clustered index, SQL Server will use that index if only
a few rows has non-NULL values, but will ignore it it, if many rows have.
Simply because a scan is more efficient in this case.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]