How does the phrase "Is Not Null" in the where clause effect the
effectiveness of a query?

If it is a determent to the effectiveness of the query, how do you
work around it?

Thanks,

On Thu, 28 Jun 2007 16:22:12 -0000, db55 <chfran (AT) gmail (DOT) com> wrote:

It really isn't an issue in general, with is the only way your
question can be answered. If you provide a specific case a more
specific response might be possible.

If the requirement is to test that something is NOT NULL then it is
intrinsic to the effectiveness of the query - the query can't be
writen without it.

As far as the efficiency of the query, the optimizer will do the best
it can to take advantage of any appropriate indexes when choosing an
execution plan.

Roy Harvey
Beacon Falls, CT

Are you sure this is the question you want to ask?

The "Is Not Null" is treated as a requirement, and will force the
optimizer to choose a query plan that will eliminate all NULL rows.

If you want to know about the effects on efficiency, well, the use of
"Is Not Null" in and of itself does not negate the use of indexes. In
other words (and somewhat simplified): if most rows are NULL and a few
rows are NOT NULL, and you have an index on this column, then the
optimizer will most likely use the index.

HTH,
Gert-Jan


db55 wrote:

db55 (chfran (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx