CODEWITHSUNDEEP
sqlsql-servert-sql
DOMZE
DOMZE

Reputation: 1369

T-SQL ISNULL in where Condition performance

I am looking for opinions and possibly a concrete answer to the following.
This question applies for SQL Server version 2008 R2+

In a stored procedure, I have an optional query parameter of type DATE, lets call it @MyVar.

The stored procedure executes the following query:

SELECT A, B, C
FROM MyTable
WHERE MyTable.Field1 = ISNULL(@MyVar,MyTable.Field1)

What is the cost of doing the ISNULL(@MyVar,MyTable.Field1) if @MyVar is NULL? I am wondering if it's better to split the cases such as:

IF (@MyVar IS NULL)
    SELECT A, B, C
    FROM MyTable
ELSE
    SELECT A, B, C
    FROM MyTable
    WHERE MyTable.Field1 = @MyVar

Thanks!

Upvotes: 4

Views: 2646

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15140

A more elegant approach would be:

SELECT A, B, C
FROM   MyTable
WHERE  MyTable.Field1 = @MyVar
   OR  @MyVar IS NULL
OPTION(RECOMPILE)

edit

Based on suggestion by @JamesZ, added option(recompile) so that an index on field1 will be used (if it exists).

edit2 (Thanks @Y.B. for pointing it out.)

If field1 can be null as well use something like:

SELECT A, B, C
    FROM   MyTable
    WHERE  MyTable.Field1 = @MyVar
       OR  (@MyVar IS NULL AND MyTable.Field1 IS NULL)
    OPTION(RECOMPILE)

This already shows the problems with 'catch all' queries. You could also, for example, replace the AND in the last query with OR, which would give yet another (theoretical) result set, but could potentially be correct as well.

Upvotes: 11

Y.B.
Y.B.

Reputation: 3586

Please note that

SELECT A, B, C
FROM MyTable
WHERE MyTable.Field1 = ISNULL(@MyVar,MyTable.Field1)

would not return records where Field1 is NULL whereas

IF (@MyVar IS NULL)
    SELECT A, B, C
    FROM MyTable
ELSE
    SELECT A, B, C
    FROM MyTable
    WHERE MyTable.Field1 = @MyVar

would.

Technically the correct and efficient way to rewrite the original expression would be:

SELECT A, B, C
FROM MyTable
WHERE @MyVar IS NULL AND MyTable.Field1 IS NOT NULL
UNION ALL
SELECT A, B, C
FROM MyTable
WHERE @MyVar IS NOT NULL AND MyTable.Field1 = @MyVar

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

For best performance have 2 procedures, one that takes @MyVar and one that doesn't. That way you get a query plan for each case not the first one you encounter (see parameter sniffing).

Have the client call whichever one makes sense depending on whether you have a value to use or not.

If you're concerned about duplicating code add a view for the guts of your query

SELECT A, B, C
    FROM MyTable

and select from that with conditions that make sense. It's not magic. There is no one size fits all unfortunately.

Upvotes: 0

Related Questions