T-SQL: CASE doesn’t go well with NULL

Standard

I recently came across a situation that requires the following simple case:

SELECT CASE Len(Article.Title)
WHEN 0 THEN 'No Title'
ELSE Article.Title END
AS Title

This will not work if the article title is a NULL value, an even simpler test can easily illustrate the problem:

SELECT CASE NULL
WHEN NULL THEN 'Null'
ELSE 'Not null' END

This will always return ‘Not null’. The CASE statement simply doesn’t work with NULL values. What I did to get around this problem is by using the Isnull function:

SELECT CASE Isnull(NULL, 0)
WHEN 0 THEN 'Null'
ELSE 'Not null' END

Update: jba has pointed out a critical performance issue with this method. The ISNULL function will be evaluated for each row of data that the query is processing. This can effectively turn any index-able query to use a table scan. Thanks for the tip John 🙂

Advertisements

One thought on “T-SQL: CASE doesn’t go well with NULL

  1. You should note that using ISNULL() or COALESCE() for that matter will turn a query that could potentially have used a covering index into a table scan.

    That is the ISNULL statement will have to be evaluated for every row of data that it is used in (if used in a where clause).

    So while ISNULL is handy, it’s best to try and avoid it’s need in database design where ever possible.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s