isnull vs is null

Question

I have noticed a number of queries at work and on SO are using limitations in the form:

isnull(name,'') <> '' 

Is there a particular reason why people do that and not the more terse

name is not null 

Is it a legacy or a performance issue?

Answer 1

where isnull(name,'') <> '' 

is equivalent to

where name is not null and name <> ''  

which in turn is equivalent to

where name <> '' 

(if name IS NULL that final expression would evaluate to unknown and the row not returned)

The use of the ISNULL pattern will result in a scan and is less efficient as can be seen in the below test.

SELECT ca.[name],        [number],        [type],        [low],        [high],        [status] INTO   TestTable FROM   [master].[dbo].[spt_values]        CROSS APPLY (SELECT [name]                     UNION ALL                     SELECT ''                     UNION ALL                     SELECT NULL) ca    CREATE NONCLUSTERED INDEX IX_TestTable ON dbo.TestTable(name)  GO   SELECT name FROM TestTable WHERE isnull(name,'') <> ''  SELECT name FROM TestTable WHERE name is not null and name <> '' /*Can be simplified to just WHERE name <> '' */ 

Which should give you the execution plan you need.