Are conditions without ISNULL stinky?

Question

I come across an article describing a different situation where the SQL code is probably incorrect. However, there is one point that is surprising to me. They claim that

it is advisable to explicitly handle NULL in nullable columns using COALESCE to provide a default value

ISNULL . They also link to this MSDN webpage , giving an example with ISNULL . The main idea here is that it’s better to use

 SELECT COUNT(*) FROM [dbo].[Table1] WHERE ISNULL([c2],0) > 2; 

then

 SELECT COUNT(*) FROM [dbo].[Table1] WHERE [c2] > 2; 

However, the first option will not be SARG, while the result is not affected by ISNULL . I understand that you need to handle NULL with ISNULL or COALESCE in the output, however, I always try to use IS NULL or IS NOT NULL to handle NULL in the predicate. Did I miss something? What is the problem of MSDN?

EDIT: to respond to the discussion and mainly to this post I prepared a simple test

  IF OBJECT_ID('dbo.LogTable', 'U') IS NOT NULL DROP TABLE dbo.LogTable SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent , CASE WHEN ( ABS(CHECKSUM(NEWID())) % 100 ) = 1 THEN NULL ELSE ( ABS(CHECKSUM(NEWID())) % 1000 ) END ivalue INTO [LogTable] FROM sys.sysobjects CROSS JOIN sys.all_columns CREATE INDEX ix_logtable_ivalue ON LogTable(ivalue asc) INCLUDE(datesent); -- Q1 select * from logtable where isnull(ivalue, 0) > 998 -- Q2 select * from logtable where ivalue > 998 

However, ivalue in Q1 is not a SARG. Is there a catch? How do I make the SARG attribute for this specific data and queries?

Answer 1

The isnull check in your example is pointless. null > 2 returns null , which is not true, and thus these strings will be excluded from the request. To boot, using isnull in this way will prevent the optimizer from using the index on c2 if you have one.

In short, this sounds like bad advice.