470,596 Members | 1,092 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

Is Not Null and Query Optimization

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,

Jun 28 '07 #1
3 11605
On Thu, 28 Jun 2007 16:22:12 -0000, db55 <ch****@gmail.comwrote:
>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,
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
Jun 28 '07 #2
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:
>
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,
Jun 28 '07 #3
db55 (ch****@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, es****@sommarskog.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
Jun 28 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by ensnare | last post: by
5 posts views Thread by Kenneth Courville | last post: by
reply views Thread by hoogendi | last post: by
5 posts views Thread by AC Slater | last post: by
2 posts views Thread by Eugene | last post: by
1 post views Thread by Savas Ates | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.