Skip to main content

What does Avoid non SARGable where clause mean?

SARGable is the short form of  "Search ARGument able". A condition in the SQL Query is said to be SARGable if the database engine can take advantage of an available Indexes and do an INDEX SEEK instead of Table Scan / Index scan to speed up the execution of that query.

One of the major mistakes developers do which makes a query non-SARGable is they use functions directly on a column in the WHERE Clause. The next common mistake i have seen is the issues created because of "implicit data type conversions". In this post I would explain those with few examples.

--Sample table
CREATE TABLE tblSARGTest
(
ProductID INT IDENTITY PRIMARY KEY,
ProductName VARCHAR(50) NOT NULL,
Manufacturing_Date DATETIME NOT NULL
)
GO

--Non clustered index on Manufacturing Date
CREATE NONCLUSTERED INDEX [nc_ix_manufacturing_dt] ON [dbo].[tblSARGTest] 
(
[Manufacturing_Date] ASC
)
GO

--Non clustered index on Product Name
CREATE NONCLUSTERED INDEX [nc_ix_productName] ON [dbo].[tblSARGTest] 
(
[ProductName] ASC
)
GO

-- Populate the table with some random 10K records. 
-- Refer my earlier post on Generating random test records
SET NOCOUNT ON
GO
INSERT INTO tblSARGTest 
SELECT 
dbo.udf_StringGenerator('A', 50),
GETDATE() + (RAND()* (39 * 365))
Go 10000

Now that we have a table with sample data and Indexes for demonstration purpose lets see some sample queries to understand the concept better.

Example 1: Find total records for the year 2011

--Query 1:: Non SARGable query 
SELECT COUNT(*) FROM tblSARGTest 
WHERE YEAR(Manufacturing_Date) = 2011

--Query 2:: SARGable query
SELECT COUNT(*) FROM tblSARGTest
WHERE Manufacturing_Date >= '01-01-2011' AND 
               Manufacturing_Date < '01-01-2012'

Both the queries would return the same number of records but "Query 2" would execute faster as it is SARGable. In "Query 1" we are using the function YEAR directly on the column which is a mistake so Query optimizer cannot make use of the appropriate indexes properly. Check the below screenshot to see the first query is making use of Index Scan while the second query is making use of Index Seek. Also we can see the difference it has on the "Query Cost".


Example 2: List all Products which starts with 'ab'

--Query 3: Non-SARGable
SELECT COUNT(*) FROM tblSARGTest WHERE LEFT(ProductName,2) = 'ab'
GO

--Query 4: SARGable
SELECT COUNT(*) FROM tblSARGTest WHERE ProductName LIKE 'ab%'
GO

In Query 3 we have again used a function on a column directly in the WHERE clause. So it would be slower and at the same time Query 4 would be faster.


Example 3: List all products which has the text 'ab' anywhere within it!

--Query 4: Non-SARGable
SELECT COUNT(*) FROM tblSARGTest WHERE ProductName LIKE '%ab%'
GO

Using wild card on both side of the search string is bad and it would result in a Table scan or an Index scan only. If we need to do these sort of searches then it is better to consider "Full text" searching option.

If we have wild card at the start of the search string, SQL Server has to evaluate each and every row irrespective of whether there is an Index or not!! The Query 4.1 shown below would also can't make use of INDEX SEEK.

--Query 4.1: Non-SARGable
SELECT COUNT(*) FROM tblSARGTest WHERE ProductName LIKE '%ab'
GO

So bottom-line is never prefix a search string with a wild card as it would make the query non-SARGable.

Example 4: Never use an expression which uses the same column name on both sides of the operator!

--Though there is a Clustered Index on ProductID it would not be used!
SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID
GO


Example 5: Beware of Implicit data type conversion

Whenever implicit conversions occur while evaluating two values against each other, the value with the lowest data type in precedence is converted to the higher precedence. While doing so, SQL Server will try to choose the conversion that is least likely either to fail due to an overflow or to lose precision.

For example, if in a query we are equating an INT column with a BIGINT column then the INT column will be converted to an BIGINT!

Why does SQL Server do so? Why can't it do the vice-versa? Its simple, any INT value would be a subset of a BIGINT whereas if it tries to convert BIGINT into INT an overflow error would be thrown as INT cannot accommodate a BIGINT in it! So if we are careless we might see a performance hit on the query.

SET STATISTICS PROFILE ON
GO

--Query 6: Non-Sargable.
SELECT COUNT(*) FROM tblSARGTest WHERE ProductName LIKE N'ab%'
GO

--Query 7: Sargable
SELECT COUNT(*) FROM tblSARGTest WHERE ProductName LIKE 'ab%'
GO

In "Query 6" the unicode N'ab%' will force an implicit conversion of the ProductName column into NVARCHAR. The end result is we would get the costly Index Scan instead of an Index Seek.


So almost always use the right type for the columns in the queries.

Comments

Vidhi Shah said…
Hey there. That was a very good explaination. Thanks for it. I had a doubt as i am a new beeie..
In the Example 3 shown, you suggested not to use wildcards on both the side like '%ab%' as it will perform whole table scan for searching (correct me if i am wrong). So what is the alternate for such a query!?

Popular posts from this blog

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul