Just Learned - SQL Server - Using YEAR() function for a column in Where Clause will make query non-sargable

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

SQL Server - Using YEAR() function for a column in Where Clause will make query non-sargable

Aug 17 2011 7:04AM by Madhivanan

When I viewed the code written by co-worker, I found this. To find sales made in 2009, the where clause was written as

Where year(sales_date)=2009

But beware that this will not make use of index available on sales_date column. The efficient method is to modify the where clause like below which will make use of index

where sales_date>='20090101' and sales_date<'20100101'

Read More.. [0 clicks]

Published under: SQL Server Tips · · · ·

Previous: Renaming a procedure using sp_rename is not recommended
Next: Adding and Removing NoiseWords/StopWords in STOPLIST of FULLTEXT Index - SQL Server

Madhivanan

3 · 39% · 12419

Liked

Knew

Learned

Incorrect

Interesting

Forgotten


Post this to my facebook wall
Tweet this
Like This
I knew this before
Thanks, I just learned it from you
Sorry, but this is not correct
Interesting, but not my area
I knew it but i have forgotten it


	Submit

4 Comments

Hello,

What i have appreciated more especially is the second line of code and the remark about the nousability of the index. I have seen too many people asking why their queries have slowed down after the use of the year() function because of smaller code ( not execution time )

commented on Aug 17 2011 2:09PM

Patrick Lambin

161 · 1% · 296

Nice tip Madhivanan

commented on Aug 17 2011 10:45PM

Hardik Doshi

20 · 9% · 2839

This is true for almost any function in the WHERE clause. This was covered by Jacob during his Tech-Ed 2011 (Day 01) presentation, documented by me at: T-SQL Worst Practices – a compilation from Jacob’s session at Tech-Ed 2011 – Part 01, Part 02, Part 03

commented on Aug 22 2011 2:04AM

Nakul Vachhrajani

4 · 33% · 10564

I had to look up sargable

commented on Sep 13 2011 2:46AM

rhodrie

483 · 0% · 77

Your Comment

Sign Up or Login to post a comment.

"SQL Server - Using YEAR() function for a column in Where Clause will make query non-sargable" rated 5 out of 5 by 14 readers +Madhivanan