Replacing ISNULL In A WHERE Clause

I like blogging

Really, I do. But sometimes I like a change of pace.

Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky for people to post responses in, when they involve code.

For example, there were a couple great answers on my question from Users DMason and Joe Obbish, neither of which would have been possible in blog comments.

I think I’ll do that more often

It was fun putting all the scripts and examples together and not wrestling with WordPress to get things how I wanted them. I encourage our readers to sign up and start posting and answering questions there as well.

If that type of community isn’t your speed, then there are some great forums over at SQL Server Central and SQL Team.

Thanks for reading!

, , ,
Previous Post
The Surprising Behavior of Trailing Spaces
Next Post
[Video] Office Hours 2017/04/12 (With Transcriptions)

4 Comments. Leave new

  • Hi Team,
    I have a where clause as follows:
    where table1.col1=isnull(table2.col1,table1.col1)
    This condition does not utilize the index in table2.col1 and performance is slow.
    So I replaced it with following case statement:
    where table1.col1= case table2.col1 when null then table1.col1 else table2.col1 end
    Performance increased dramatically, utilizing the index , however no of records returned is different when table1.col1 has null records in it.
    (Table1 was joined in different column col2 during the join process using inner join.)
    Please suggest how to improve performance of isnull condition in where clause.
    Thanks in advance for your precious suggestions !!

    Reply
    • Erik Darling
      May 16, 2017 7:22 am

      The best place for questions like this is linked in the blog post — https://dba.stackexchange.com/

      Reply
    • Edited :
      I have a where clause as follows:
      where table1.col1=isnull(table2.col1,table1.col1)
      This condition does not utilize the index in table2.col1 and performance is slow.
      So I replaced it with following case statement:
      where table1.col1= case table2.col1 when null then table1.col1 else table2.col1 end
      Performance increased dramatically, utilizing the index , however no of records returned is different when table2.col1 has null records in it.
      (col2 in Table2 is used during the join process, using inner join.)
      Please suggest how to improve performance of isnull condition in where clause.
      Thanks in advance for your precious suggestions !!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}