Post reply

Index seeks on a null value

  • September 25, 2009 at 7:22 am

    #134108

    Hi,

    I've just read the following in an article "Index seeks cannot be made on NULL values, so A IS NULL and B IS NOT NULL should also be avoided. "

    Is this true? As i've just built a little sample (see attachment) to populate a table with some values, including a null, then I've added an index onto it and run a select and looked at the execution plan, and there is an index seek used.

    Am I mis-understanding something.:hehe:

    full article at : http://www.sql-server-pro.com/sql-where-clause-optimization.html

    Thanks

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • September 25, 2009 at 8:27 am

    #1058483

    MrT-SQL (9/25/2009)

    Hi,

    I've just read the following in an article "Index seeks cannot be made on NULL values, so A IS NULL and B IS NOT NULL should also be avoided. "

    Is this true? As i've just built a little sample (see attachment) to populate a table with some values, including a null, then I've added an index onto it and run a select and looked at the execution plan, and there is an index seek used.

    Am I mis-understanding something.:hehe:

    full article at : http://www.sql-server-pro.com/sql-where-clause-optimization.html

    Thanks

    Dont think so, your sample shows that a index seek is used. most of the settings regarding the use of null are used when you do comparisions with null and other values.

    So I would like for someone to give an example where it does result in a table scan as teh article illustrates.

    I know that in old school terms, NULL is considered as 'Unknown' for relational database and some other articles also say that an index will not be used. but still would like to see some valid examples.

    I ran your sample on both sql 2008 and sql 2000 and it still resulted in index seek.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • September 25, 2009 at 10:00 am

    #1058539

    You are absolutely correct.

    I am the guilty author of that article (written about a year ago) and have no idea why I wrote that.

    I will correct the mistake ASAP.

    Thanks for pointing it out.

    Richard


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • September 25, 2009 at 10:09 am

    #1058549

    Richard Fryar (9/25/2009)

    You are absolutely correct.

    I am the guilty author of that article (written about a year ago) and have no idea why I wrote that.

    I will correct the mistake ASAP.

    Thanks for pointing it out.

    Richard

    No problem Richard, just wasnt sure if I was missing something or not.

    Thanks.

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply