Search Arguments : impact on query plans

For those who may be starting out with query execution plans, it may be interesting to consider the use of Search arguments (SARG) in queries. The use of SARG has 2 objectives :

1. Limit the number of rows returned by the query
2. Use an index seek operation to improve the performance of the query

If the filter is not a SARG then typically an index scan or table scan is performed on the entire table or index.
2 points to remember :

1. A filter expression is not a SARG if the column is used in an expression  such as YEAR(OrderDate) or LEFT(Employee.Name,1)=’B’
2. The use of the COLLATE operator on a column invalidates the use of  and index on that column.

A simple Example using AdventureWorks2008:
CREATE NONCLUSTERED INDEX OrderDateIndex ON Sales.SalesOrderHeader ( Orderdate  )
GO

SELECT COUNT(*) FROM Sales.SalesOrderHeader soh      
WHERE YEAR(soh.OrderDate )      
=2004 

SELECT COUNT(*) FROM Sales.SalesOrderHeader soh      
WHERE soh.OrderDate >= '20040101' AND soh.OrderDate < '20050101'

These 2 queries will generate the following Query plans:

            The first query uses and index scan with the following cost :

as you can see the second query plan cost is reduced by using a SARG in the query resulting in the use of the index seek operator, that allows SQL server to use a balanced tree to search the records.