Re: Is Not Null and Query Optimization

db55 (chfran@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: SQL Express - Identity specification property - how to change
    ... UPDATE tbl ... You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: SQL question
    ... JOIN tbl b ON a.UserID = b.UserID ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Getting list of recently added IDENTITY items
    ... FROM tbl WITH ... COMMIT TRANSACTION ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Query training -- Complex queries
    ... > Update PositionsEOM ... Here you don't have a derived table, but a correlated subquery. ... UPDATE in Books Online. ... is not written for SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: How to check if DB Constraints are enabled in a database?
    ... SELECT name, tbl = object_name ... FROM sysobjects ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)