One bad T-SQL practice: ISNULL in the WHERE clause

This is a really common T-SQL pattern that I see over and over. Let me show you an example.

--Table Schema
CREATE TABLE [dbo].[Teachers](
	[Names] [varchar](100) NULL,
	[Type] [int] NULL
) 
GO
CREATE NONCLUSTERED INDEX [ix_TeachersType] ON [dbo].[Teachers]([Type])
GO

Now, I’ll added some data into Names from StackOverflow2010’s display names, and I assigned Type randomly, with some NULL values.

Here’s a basic query using ISNULL in the WHERE clause

set statistics io on;
SELECT * FROM Teachers WHERE ISNULL(Type,3) =3 

Statistics and execution plan:

Table 'Teachers'. Scan count 1, logical reads 895, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The alternative: WHERE Type = 3 or Type is null

That’s a better alternative, but don’t take my word for it. Let’s look at the example again.

set statistics io on;
SELECT * FROM Teachers WHERE Type = 3 or Type is null

Statistics and execution plan:

Table 'Teachers'. Scan count 2, logical reads 68, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Why is there a difference?

In the example shown, the query with ISNULL did more page reads and chose to use an Index Scan. When simply re-written to use “OR IS NULL”, the query did less work to retrieve the same results. But why?

The answer is in the execution plans, specifically the predicates. First, here’s the predicates in the ISNULL(Type,3) query.

For comparison, here’s the OR IS NULL re-write:

More than just ISNULL()

This is just one example of using a function in the WHERE clause. When writing database queries, avoid applying any functions to columns in the query. It adds another layer that can cause poor performance.