Performance difference – IsNull() in where clause Vs others in SQL Server

In one of my SQL Server database development I came across a situation to use IsNull() in where clause. I tried to understand how it is good in performance with other approaches. Below are the details:

Question

Is it good to use IsNull() function in where clause?

Here I like to use small table to understand the scenario. Table I am using is an Employee table and querying is based on City column of that table.

Example

Creating table script

CREATE TABLE [dbo].[Employee](
	[EmployeeId] [int] NULL,
	[EmployeeName] [varchar](20) NULL,
	[Dept] [varchar](15) NULL,
	[city] [varchar](15) NULL
) ON [PRIMARY]

Loading sample data into Employee Table:

--Loading Data
Insert into Employee 
Select top (10000)  EmployeeId = Row_Number() over (order by (Select NULL))
	,EmployeeName = Concat ('Name ',Row_Number() over (order by (Select NULL)))
	,Dept = Concat ('Dept ',(Row_Number() over (order by (Select NULL))) % 50)
	,City = Concat ('City ',Row_Number() over (order by (Select NULL)))
		from master..spt_values s1, master..spt_values s2

Creating Clustered Index on EmployeeID:

--Creating Indexes
CREATE CLUSTERED INDEX [CI_Employee_EmployeeId] ON [dbo].[Employee] ( [EmployeeId] ASC)

Enabling Statistics and clearing cache:

--Enabling all the statistics info
Set STATISTICS IO ON
Set STATISTICS TIME ON

--Clearing Buffer
DBCC DropCleanBuffers
DBCC FreeProcCache

Now executing below query to select specific city (direct predicate on where clause):

Declare @city varchar(15) = 'City 1500'
Select * from Employee where city = @city
--It Does Clustered Index Scan

EPlan1

Now creating NonClustered index on City as below:

--Now adding Index on City
Create NonClustered Index NCI_Employee_City on dbo.Employee (city)

Declare @city varchar(15) = 'City 1500'
Select * from Employee where city =  @city
--It Does Index Seek

Eplan2

Since we have Index on City It uses Index Seek and key lookup to get appropriate results. Now coming to IsNull function on where condition as below:

--Now coming to your isnull function
--Since it forces function on each city it uses SCAN as below
Declare @city varchar(15) = 'City 1500'
Select * from Employee where city =  isnull(@city, City)
go
Declare @city varchar(15) = 'City 1500'
Select * from Employee where city is null or city = @city

In the first query It forces Scan as there is function involved to apply function on each row. Execution plan as below:

Eplan3

Statistics differences:

(1 row(s) affected)
Table 'Employee'. Scan count 1, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'Employee'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

On checking the overall percentage cost on execution plan it is evident that applying function on where clause adds more cost to performance.

My answers in StackOverflow related to this post as below

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s