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

How to stop and start SQL Server service on Red Hat Linux

Sql Server 2017/VNext CTP versions released on Linux servers. Here we go thru how to stop and start a SQL Server service in RH linux server.

Checking Service status

To check status of sql Server service we can use systemctl command as below:

$ systemctl status mssql-server

This shows the current running status of SQL Server. Currently SQL Server is loaded and not running and hence it shows ‘inactive’ status as below:

LinuxInactive

Starting SQL Server on Linux

To start the service we need to use same systemctl with start keyword as below:

$ systemctl start mssql-server

This step asks user to provide password for authentication and then it starts the service:

LinuxStart

We need to check the status as to see whether Service is started. For that we can use the same systemctl along with status keyword

Linux

Stopping SQL Server on Linux

Similarly to stop SQL Server service we need to use systemctl with stop keyword as below:

$ systemctl stop mssql-server

We can use status command to verify the service status.

Calculating running total in SQL Server

In SQL Server database programming we frequently have requirements to calculate running total. This can be achieved in SQL Server using Sum() with windowing functions. Below I am going thru some of the simplest examples to understand and calculate running total.

Creating Sample data

I am considering small sales data with attributes SalesId, SalesDate, department and SalesAmount. Here we are going to calculate running total on SalesAmount based on SalesDate.

Create Table CustomerSales (SalesId int, Department varchar(15), SalesDate date, SalesAmount int)

Insert into CustomerSales(SalesId, Department, SalesDate, SalesAmount) values
 ( 1, 'DepartMent1', '2016-05-01',50)
,( 2, 'DepartMent1', '2016-05-02',20)
,( 3, 'DepartMent1', '2016-05-03',30)
,( 4, 'DepartMent2', '2016-05-04',150)
,( 5, 'DepartMent2', '2016-05-05',110)
,( 6, 'DepartMent2', '2016-05-06',120)
,( 7, 'DepartMent2', '2016-05-07',10)
,( 8, 'DepartMent2', '2016-05-08',20)
,( 9, 'DepartMent3', '2016-05-09',40)
,(10, 'DepartMent3', '2016-06-10',50)
,(11, 'DepartMent3', '2016-06-11',60)
,(12, 'DepartMent4', '2016-06-12',70)
,(13, 'DepartMent4', '2016-06-13',80)
,(14, 'DepartMent4', '2016-06-14',90)
,(15, 'DepartMent4', '2016-06-15',180)
Running total

Here we are going to use Sum() aggregation along with Windowing function over() by providing appropriate sequencing column as below:

--Running Total
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate) 
	from dbo.CustomerSales

RunningTotal

Use of order by clause in over() will get the running total.

Percent of Total sales and Running total
We can use above running total logic along with total sales logic to find percent of total sales which will be helpful to understand overall Sales. That can be achieved as below:
--Total Sales
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate) ,
	TotalSalesAmount = sum(SalesAmount) over() 
	from dbo.CustomerSales

--Percent of Total Sales
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate) ,
	TotalSalesAmount = sum(SalesAmount) over() ,
	PercentOfTotalSales = Format((sum(SalesAmount) over(order by SalesDate) * 1.0) / Sum(SalesAmount) over(), 'p')
	from dbo.CustomerSales

Here over() clause without order by provides the total salesAmount

TotalSales

To calculate Department wise running total we can use partition by in over() clause as below:

--Running Total for each Department
Select *, RunningTotal = sum(SalesAmount) over(order by SalesDate),
	DepartmentRunningTotal = sum(SalesAmount) over(Partition by Department order by SalesDate) 
	from dbo.CustomerSales

Please find detailed demo here

Some of user scenarios as below:

1. StackOverflow user scenarios with my answers

Number generation using tally tables in SQL Server

In database SQL Server programming we will have requirements to generate numbers of sequence to use along with other tables. In this scenario we can follow many of below steps to generate.

Example if we want to generate numbers 1 to 100.

1. Using system tables

We can use some of the system tables to generate numbers as we require. For that example we can use master..spt_values tables and use row_number() to generate numbers as below:

Select top (100) Num = Row_Number() over(order by (Select NULL))
      from master..spt_values s1, master..spt_values s2

Advantages with this approach is that it is easy and quick to write
Disadvantages with this approach is that we are unnecessarily using System tables for number generation.

2. Using Values and Common Table Expressions

By using ‘Values’ this approach we are going to generate numbers as below:

;With cte1 as 
  ( Select * from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(n) )
  ,Cte2 as ( select c1.* from cte1 c1, cte1 c2, cte1 c3)
  Select top(100) Num = row_number() over (order by (select null)) 
      from cte2

This approach is normally used across as we can generate required numbers

3. Using Recursive Common Table Expressions

This approach as good as looping but thru recursive Common Table expressions.

;With Cte_numbers as (
  Select 1 as Num				        --Start number
    Union all
  Select Num + 1 from Cte_numbers where Num < 100	--End number
  )
  Select * from Cte_numbers

Based on the scenarios of requirements we can use any of these approaches.

Demo for these approaches are here.

Some user scenarios are as below:

1. StackOverflow Examples with my answers as in approach 2
2. StackOverflow Examples with my answers as in approach 1