SQL Server Performance

Which is better??

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by meunice, Jan 21, 2007.

  1. meunice New Member

    Hi, I am a new learner here. I am wondering is it <b>WHERE Field_A &gt; ''</b> better than <b>WHERE Field_A IS NOT NULL</b>?? Can anyone help to explain why? Thanks a lot!! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  2. FrankKalis Moderator

    First of all, welcome here! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />None of your WHERE clauses is "better" than the other. Since they are in fact logically completely different, you cannot even compare them with each other. <br /><br />WHERE Field_A &gt; '' is searching for an empty string, that is a character of length 0.<br />WHERE Field_A IS NOT NULL is searching for all rows having a value at all in that column. Including an empty string. This clause will return all the row for which the value of Field_A is known at the time of execution. <br /><br />An empty string is a known value whereas NULL should represent the UNKNOWN (or missing) of a value. There might be times where you can use a DEFAULT of '' to model a missing value and there might be times where this won't work. This Three-Valued Logic is kind of special to the SQL language and you might want to read about it in BOL or search the web. This is explained quite well in many places. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  3. Adriaan New Member

    Frank, if you allow me a small correction ...

    "WHERE Field_A > '' is searching for an empty string, that is a character of length 0."

    WHERE Field_A > '' is searching for a string with a length greater than 0. This could be a single space, or a string of any number of characters.
  4. FrankKalis Moderator

    Oops, that was what I thought. Obviously it didn't make its way from the brain to the fingers. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />Thanks for catching that!<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. Roji. P. Thomas New Member

    Try this

    CREATE TABLE Test(i CHAR(10))

    INSERT INTO TEST VALUES('')
    INSERT INTO TEST VALUES(' ')
    INSERT INTO TEST VALUES(NULL)
    INSERT INTO TEST VALUES('xyz')
    INSERT INTO TEST SELECT SPACE(10)

    SELECT * FROM Test WHERE i > ''
    SELECT * FROM Test WHERE i > SPACE(0)
    SELECT * FROM Test WHERE i IS NOT NULL

    DROP TABLE Test


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  6. meunice New Member

    Thank you guys!! Arigato!! [8D]

Share This Page