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.