Click here to Skip to main content
13,139,569 members (55,068 online)
Click here to Skip to main content
Add your own
alternative version

Stats

18.2K views
12 bookmarked
Posted 12 Oct 2014

Sargable query in SQL server.

, 12 Oct 2014
Rate this:
Please Sign up or sign in to vote.
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.

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.

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.

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:

SELECT DealerName FROM SargableDemo WHERE DealerName Like '%Toyota'

Execution plan goes here.

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

Sargable:

SELECT DealerName FROM SargableDemo WHERE DealerName Like 'Toyota%'

Execution plan goes here.

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

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:

SELECT OrderID FROM SargableDemo WHERE OrderID *3 = 33000

Execution plan goes here.

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

Sargable:

SELECT OrderID FROM SargableDemo WHERE OrderID = 33000/3

Execution plan goes here.

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.

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: 

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

Execution plan goes here.

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

Sargable: 

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

Execution plan goes here.

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

Here are some more example :
Non-Sargable:

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


Sargable:

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

Non-Sargable:

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


Sargable:

SELECT... WHERE FullName LIKE 'Jite%'

Non-Sargable:

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:

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)

Share

About the Author

Jitendra Ku. Sahoo
Software Developer
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionNice article but.... Pin
digimanus15-Jun-15 5:09
memberdigimanus15-Jun-15 5:09 
AnswerRe: Nice article but.... Pin
Jitendra Ku. Sahoo16-Jun-15 19:59
memberJitendra Ku. Sahoo16-Jun-15 19:59 
QuestionKeep performance with EF autogenerate sql Pin
Eti_300716-Oct-14 4:01
memberEti_300716-Oct-14 4:01 
AnswerRe: Keep performance with EF autogenerate sql Pin
Jitendra Ku. Sahoo28-Oct-14 2:37
memberJitendra 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 
GeneralRe: My Vote of Four Pin
Jitendra Ku. Sahoo13-Oct-14 3:54
memberJitendra 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
memberJitendra 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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 13 Oct 2014
Article Copyright 2014 by Jitendra Ku. Sahoo
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid