CODEWITHSUNDEEP
sqlsql-servert-sqlnull
Matt
Matt

Reputation: 4200

What is the difference in these two methods of determining NULL

I've got some T-SQL code that does a check to determine if a field is NULL. Normally I simply do a IS NULL check on the field, however in this code I've been given, the method used is WHERE ISNULL(MyField, '') <> ''

Is this a more accepted method? Is it better or worse than simply saying WHERE MyField IS NOT NULL

Cheers for your help

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270973

The expression:

WHERE MyField IS NOT NULL

would allow the optimizer to use an index, if appropriate (even if available, it might not be the best solution). A function on a field generally prevents an index from being used.

The expressions:

ISNULL(MyField, '') <> ''

or

COALESCE(MyField, '') <> ''

not only confuse '' with NULL. But, if the field is a numeric type, then '' will be interpreted as 0, causing further confusion.

Upvotes: 1

Keith Neuse
Keith Neuse

Reputation: 723

It accounts for both null fields and fields with an empty string. In some cases this is desired over just a null field.

Upvotes: 5

Related Questions