Menu
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?
540
You might confuse between SQL Server ISNULL and IS NULL. We use IS NULL to identify NULL values in a table. For example, if we want to identify records in the employee table with NULL values in the Salary column, we can use IS NULL in where clause.
The IS NULL condition is satisfied if the column contains a null value or if the expression cannot be evaluated because it contains one or more null values. If you use the IS NOT NULL operator, the condition is satisfied when the operand is column value that is not null, or an expression that does not evaluate to null.
ISNULL replaced the Oracle NVL function in the SQL server. When an expression in SQL server is NULL, the ISNULL function allows you to return an alternative value for the null. ISNULL checks whether the value or an expression is true or false.
What is a SQL NULL value? In terms of the relational database model, a NULL value indicates an unknown value. If we widen this theoretical explanation, the NULL value points to an unknown value but this unknown value does not equivalent to a zero value or a field that contains spaces.
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.
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
