CODEWITHSUNDEEP
sql-servert-sqlisnull
jcolebrand
jcolebrand

Reputation: 16025

Sargable queries using ISNULL in TSQL

I'm looking to prevent non-sargable expressions in my queries, which is the better way to check for a null condition?

AND c.Account IS NOT NULL 
AND c.Account <> ''

or

AND ISNULL(c.Account,'') <> ''

It dawned on me to point out that Account is coming from a LEFT JOIN so it may be null. I want the cases where they only intersect, which means I should really just use an INNER JOIN huh? Thanks for the facepalms ;)

However, overlooking that nauseating self realization, I still want to know the answer to this in the general case where I can't make Account a NOT NULL column.

Upvotes: 7

Views: 5686

Answers (3)

Martin Smith
Martin Smith

Reputation: 453037

Just use WHERE c.Account <> ''

This doesn't evaluate to true for either null or empty string and can potentially be evaluated using a range seek on Account.

Use of either ISNULL or COALESCE would make the expression unsargable and is not needed anyway.

If you want to distinguish truly empty strings from strings consisting entirely of spaces you could use

 WHERE c.Account IS NOT NULL AND DATALENGTH(c.Account) > 0

Which combines one sargable predicate that allows an index seek to skip the nulls in an index with an unsargable one against the remaining values.

Upvotes: 4

IGNACE Bruno
IGNACE Bruno

Reputation: 1

COALESCE IS SARGABLE as it's only a shortcut expression (equivalent of using CASE WHEN IS NOT NULL THEN ELSE...).

ISNULL is a built in function so ISNULL is NOT SARGABLE

Upvotes: -3

Thomas
Thomas

Reputation: 64635

C.Account <> '' is equivalent to ISNULL( c.Account, '' ) <> ''

SQL Server is probably smart enough to translate IsNull into the equivalent SARG expression but if you are bent on using a function, then Coalesce is a better choice because it is part of the SQL Standard, allows for multiple values (instead of just two with IsNull) and avoids using quite possibly the most confusing function name Microsoft ever devised in IsNull.

Upvotes: 2

Related Questions