Skip to main content

Posts

Showing posts from November, 2011

What does Avoid non SARGable where clause mean?

SARG able is the short form of  " S earch ARG ument 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_productNam