Non Saragable Where Clause

Let's create an sample table.create table #test(
id int,
hiredate datetime,
name varchar(30)
)


Now we are going to insert some sample records.

insert into #test values(10,'13 sep 2012 15:12:16','emp10')
insert into #test values(1454,'13 sep 2012 00:00:00','emp1454')
insert into #test values(17,'13 sep 2012 23:59:59','emp17')
insert into #test values(1433,'14 sep 2012 00:00:00','emp1433')
insert into #test values(103,'14 sep 2012 23:59:59','emp103')
insert into #test values(1035,'14 sep 2012 13:12:16','emp1035')
insert into #test values(8102,'13 sep 2013 15:12:16','emp8102')


Non clustered index on hire date column.

create nonclustered index non_cluind_hiredate on #test(hiredate);

select * from #test

id          hiredate                                               name                          
----------- ------------------------------------------------------ ------------------------------
10             2012-09-13 15:12:16.000                                emp10
1454         2012-09-13 00:00:00.000                                emp1454
17            2012-09-13 23:59:59.000                                 emp17
1433        2012-09-14 00:00:00.000                                 emp1433
103          2012-09-14 23:59:59.000                                 emp103
1035        2012-09-14 13:12:16.000                                 emp1035
8102        2013-09-13 15:12:16.000                                 emp8102

(7 row(s) affected)


Now we need to get all records those have hiredate on Sep 13th 2012.

Our first attempt is:

declare
@tardate datetime
set @tardate='13 sep 2012'
select hiredate from #test where hiredate=@tardate


id          hiredate                                               name                          
----------- ------------------------------------------------------ ------------------------------
1454        2012-09-13 00:00:00.000                                emp1454

(1 row(s) affected)


Here we got only one row,because internally @tardate is '13 Sep 2012 00:00:00' so only one record matched with this value,hence this is not our expected result.

Second attempt is:

declare
@tardate datetime
set @tardate='13 sep 2012'
select hiredate from #test
where datediff(dd,@tardate,hiredate)=0

id          hiredate                                               name                          
----------- ------------------------------------------------------ ------------------------------
10          2012-09-13 15:12:16.000                                emp10
1454        2012-09-13 00:00:00.000                                emp1454
17          2012-09-13 23:59:59.000                                emp17

(3 row(s) affected)

Here we get expected result, but if we look at the execution plan it is going for the index scan rather than index seek why?

Execution plan:

Because of piece of code "datediff(dd,@tardate,hiredate)=0"

This condition is non-sargable means that the WHERE clause (or at least part of it) cannot take advantage of an index,
instead performing a table/index scan, which may cause the query’s performance to suffer.

On other hand saragable means that it can take advantage of a useful index (assuming one is available) to speed completion of the query.

Now we need to make our query take advantage of index seek

we are modifiing the piece of code as below.

where hiredate>=@tardate and hiredate<dateadd(dd,1,@tardate)


declare
@tardate datetime
set @tardate='13 sep 2012'
select hiredate from #test where hiredate>=@tardate and hiredate<dateadd(dd,1,@tardate)  --0.00328

id          hiredate                                               name                          
----------- ------------------------------------------------------ ------------------------------
10          2012-09-13 15:12:16.000                                emp10
17          2012-09-13 23:59:59.000                                emp17
1433        2012-09-14 00:00:00.000                                emp1433

(3 row(s) affected)


Now if we look at the execution plan it is going for index seek.

Execution Plan:

Similar situations:

The where conditions which use date functions [year (), month ()] on index column (hiredate) are non saragable clauses these where clauses won’t take advantage of index seek operation, always go for index scan.

Then we slightly modify the piece of code to take advantage of index seek operation.

1. We need to get all records those who got hired on current month.

Non saragable clause:

Where year (hiredate) = year (getdate ())

And month (hiredate) = month (getdate ())

You would change it to this:

Saragable clause:

Where hiredate >= dateadd (mm, datediff (mm, 0, getdate ()) +0, 0)

And hiredate < dateadd (mm, datediff (mm, 0, getdate ()) +1, 0)

Here date(01/01/1900), which can be represented as a 0.

2. We need to get all records those who got hired on current year.

Non saragable clause:

Where year (hiredate) = year (getdate ())

You would change it to this:
      Saragable clause:

Where hiredate >= dateadd (YYYY, datediff (YYYY, 0, getdate ()) +0, 0)

And hiredate < dateadd (YYYY, datediff (YYYY, 0, getdate ()) +1, 0)

Here date(01/01/1900), which can be represented as a  0. 

Some more Examples:

--Query 1:: Non SARGable query

select count(*) from #test
where year(hiredate) = 2011

--Query 2:: Non SARGable query

select count(*) from #test
where hiredate >= '01-01-2011' AND
hiredate  < '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 makes where clause as non saragable so Query optimizer cannot make use of the appropriate indexes properly.

Example : List all employees which starts with 'em'

--Query3 : Non-SARGable
select count(*) from #test where LEFT(name,2) = 'ab'
GO

--Query4 : SARGable
select count(*) from #test where name LIKE 'ab%'
GO

In Query 3 we have again used a function on a column directly in the WHERE clause which makes where clause as non saragable.

So it would be slower and at the same time Query 4 would be faster.

--Query 4: Non-SARGable
select count(*) from #test where name 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 #test where name LIKE '%ab'
GO

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

Thanks...