Click here to Skip to main content
15,868,141 members
Articles / Database Development / SQL Server / SQL Server 2014

Sargable query in SQL server.

Rate me:
Please Sign up or sign in to vote.
4.56/5 (18 votes)
12 Oct 2014CPOL3 min read 42.6K   12   9
In this article we will discuss how to write Sargable query in SQL server.

Introduction

There are different ways to increase the performance of SQL queries. Sargable query is one way to do it.

Description:

Indexing of database do magic to improve the performance of the query. But sometime due to bad practice, Index are not used. Sometime we notice that after creating proper index also, SQL server is unable to use Index seek on desired column. Here the Index is ignored.

Understanding of the concept of SARGable expressions can do the magic to improve query performance. SARG is an acronym of “Search ARGumentable”. As per wikipedia SARGable is defined as "In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE". Find here wiki link.

So query will be called SARGable query if we can use the advantage of Index, if available in the column fully. Here the query use Index seek operation.

Index Seek:
  Here SQL server used the B-tree structure of index directly to fetch the matching record. It is fast and preferred for table with huge data.
Index scan:
  Here SQL server scan/reads all the record of the table to return the required rows. It is slow. But for table with small amount of data, where it is needed to fetch all record this process is fine.

Generally when we include a Function/operation in a column included in WHERE clause the query became Non-Sargable. Few Non-sargable  search arguments that generally prevent (but NOT always) the query optimizer from using a useful index to perform a search are “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%test′” .

Always check the execution plan of your query after you have done with it, to check if query is using available Index or not.

Let us create  test table to have demo on Sargable query.

SQL
CREATE TABLE SargableDemo
(
[ID] INT IDENTITY(1, 1) NOT NULL,
[DealerName] NVARCHAR(100) NULL,
[OrderID] INT NOT NULL,
[Date] Datetime not null,
)

Let us insert few rows into the table.

SQL
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Toyata',11,GETDATE())
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Toyata',12,dateadd(YEAR,1, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Maruti',13,dateadd(DAY,1, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Ford',1,dateadd(YEAR,2, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Toyata',21,dateadd(DAY,21, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('BMW',41,dateadd(DAY,4, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Toyata',51,dateadd(YEAR,5, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('FORD',71,dateadd(DAY,7, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('Toyata',81,dateadd(DAY,9, getdate()))
INSERT INTO SargableDemo(DealerName,OrderID,[Date]) VALUES('BMW',91,dateadd(YEAR,2, getdate()))

Wildcard comparisons:

Let us create an index on DealerName column.

SQL
CREATE NONCLUSTERED INDEX IX_SargableDemo_DealerName 
ON SargableDemo(DealerName); 
GO

Here we want to find all dealer whose name is "Toyota". Below is the non-sragable and sargable way to do it.
Non-Sargable:

SQL
SELECT DealerName FROM SargableDemo WHERE DealerName Like '%Toyota'

Execution plan goes here.

Image 1

Here the index on DealerName column is ignored, resulting into Index Scan.

Sargable:

SQL
SELECT DealerName FROM SargableDemo WHERE DealerName Like 'Toyota%'

Execution plan goes here.

Image 2

Here the index on DealerName column is used, resulting into Index Seek.

Arithmetic operators:
 Let us have a demo with arithmetic operater.

Let us create an Index on OrderID column

SQL
CREATE NONCLUSTERED INDEX IX_SargableDemo_OrderID 
ON SargableDemo(OrderID); 
GO

Here we are going to include an arithmetic operator in WHERE clause. Below is the non-sragable and sargable way to do it. 

Non-Sargable:

SQL
SELECT OrderID FROM SargableDemo WHERE OrderID *3 = 33000

Execution plan goes here.

Image 3

Here the index on OrderID column is ignored, resulting into Index Scan.

Sargable:

SQL
SELECT OrderID FROM SargableDemo WHERE OrderID = 33000/3

Execution plan goes here.

Image 4

Here the index on OrderID column is used, resulting into Index Seek.

Similarly use of ABS() function makes query non-sergable.

Scalar Function:
 Let us have a demo with YEAR() function.

Let us create an Index on Date column.

SQL
CREATE NONCLUSTERED INDEX IX_SargableDemo_Date 
ON SargableDemo([Date]); 
GO

 

Here we want to find all record placed on a particular year. Below is the non-sragable and sargable way to do it.

 Non-Sargable: 

SQL
SELECT [Date] FROM SargableDemo WHERE Year([Date]) = 2014

Execution plan goes here.

Image 5

Here the index on Date column is ignored, resulting into Index Scan. 

Sargable: 

SQL
SELECT [Date] FROM SargableDemo WHERE [Date] >= '01-01-2014' AND [Date] < '01-01-2015'

Execution plan goes here.

Image 6

Here the index on Date column is used, resulting into Index Seek.

Here are some more example :
Non-Sargable:

SQL
SELECT... WHERE isNull(FullName,'Jitendra') = 'Jitendra'


Sargable:

SQL
SELECT... WHERE ((FullName = 'Jitendra') OR (FullName IS NULL))

Non-Sargable:

SQL
SELECT ... WHERE SUBSTRING(FullName,4) = 'Jite'


Sargable:

SQL
SELECT... WHERE FullName LIKE 'Jite%'

Non-Sargable:

SQL
SELECT DealerName FROM SargableDemo WHERE UPPER(DealerName) LIKE 'FORD'

SQL server is by default case insensitive, so use of UPPER() and LOWER() function is bad here.

Sargable:

SQL
SELECT DealerName FROM SargableDemo WHERE DealerName LIKE 'FORD'

Points of Interest

So by writing inverse function/operation of non-sergable function/operation in WHERE condition, we can increase the performance of the query. So to avoid Index scan and improve performance of the query, try to make WHERE clause sargable. Check execution plan of your query after implementation of above tips to get best use of your Index.

History

Keep a running update of any changes or improvements you've made here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionCase sensitive is not always your scenario Pin
Gabriel Aizcorbe15-Mar-19 14:57
professionalGabriel Aizcorbe15-Mar-19 14:57 
QuestionNice article but.... Pin
Herman<T>.Instance15-Jun-15 5:09
Herman<T>.Instance15-Jun-15 5:09 
AnswerRe: Nice article but.... Pin
Jitendra Ku. Sahoo16-Jun-15 19:59
Jitendra Ku. Sahoo16-Jun-15 19:59 
QuestionKeep performance with EF autogenerate sql Pin
Etienne Louise (974)16-Oct-14 4:01
Etienne Louise (974)16-Oct-14 4:01 
AnswerRe: Keep performance with EF autogenerate sql Pin
Jitendra Ku. Sahoo28-Oct-14 2:37
Jitendra Ku. Sahoo28-Oct-14 2:37 
GeneralMy Vote of Four Pin
aarif moh shaikh13-Oct-14 2:45
professionalaarif moh shaikh13-Oct-14 2:45 
Good Article Jitendra sir... Smile | :)
GeneralRe: My Vote of Four Pin
Jitendra Ku. Sahoo13-Oct-14 3:54
Jitendra Ku. Sahoo13-Oct-14 3:54 
GeneralMy vote of 4 Pin
VC.J12-Oct-14 21:31
professionalVC.J12-Oct-14 21:31 
GeneralRe: My vote of 4 Pin
Jitendra Ku. Sahoo12-Oct-14 21:36
Jitendra Ku. Sahoo12-Oct-14 21:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.