SQL Server : Search Arguments (SARG)

Always try to make the queries sargable to optimize the query. If the query is not sargable the query optimizer has to scan all the rows in the table even if that column has an index.

Here are some examples of non-sargable queries and how they can be fixed.

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:

 

SELECT ... FROM ...
WHERE Year(myDate) = 2008

 

The SQL optimizer cant use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

 

Some other examples:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())