This post is inspired by a discussion at MSDN forums, which is about the Performance effect of Cast,Convert and Year() functions on Datetime columns in where clause.
In MSDN forums, recently saw a question, “how to retrieve the records from a table having the Year 2005?” and found the below reply using Convert function, which I used in my earlier days of career.
Select * from TableName where convert(varchar,DateTimeColumName,101) like '%2005'
I observed that Optimizer can't use the indexes efficiently on this query.So, I checked some other variations.
- Using Year() function.
Select * from TableName where Year(ColumnName) = 2005
- Using Range Query.
Select * from TableName where ColumnName >= ‘1/1/2005’ and ColumnName < '1/1/2006'
But being a lazy programmer, opted for Year() function as its typing is simple and also believed a myth “Year() function is a In-Built function and SQL optimizer should take care about it and will uses indexes properly”. So, I replied that post using Year() function. Some other experienced programmer posted the range query also.
The Guy who asked the question saw all his replies and found that Year() function is simple and working. So, He marked it as answer. I am delighted. But suddenly moderator saw this question and he unmarked my reply as answer, and marked Range Query as answer. I felt disappointed and thought of proving to moderator that Year() and Range queries will perform same. So, I quickly created a table with 1 million records and created an index on it and tested the 3 queries and anxiously look into results.
create table testindexes
Id int identity(1,1) primary key,
;with Numbers as
select 1 as Num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9 union all select 0
insert into testindexes
select DATEADD(day,ABS(BINARY_CHECKSUM(newid())%100000),0) from Numbers N,Numbers N1,Numbers N2,Numbers N3, Numbers N4
create index idx_mydate on testindexes(MyDate)
select * from testindexes where YEAR(mydate) = 2005
select * from testindexes where mydate >= '1/1/2005' and MyDate < '12/31/2005'
select * from testindexes where RIGHT( CONVERT(varchar,Mydate,101),4) like '2005%'
But the results gave me a different picture than expected. Range query came first in race and the Convert and Year() functions got the combined second place. Range query is performing 50 Times better than the other 2 queries.
Looking at the execution plans, I Observed that, Optimizer performed “SEEK” operation on Range Query, where as for the other 2 queries, optimizer performed “SCAN” operation. As “SEEK” will work on relevant part of that index, it retrieves the results fast than entire “SCAN” of the index.
When we observe the difference between these queries, In the first 2 queries, we are wrapping up the Date Column with an expression, where as in the Range query, we are not using any expressions with Date Column , which makes the Optimizer to understand the part of the index to search to get the results.
When you generalize the Range Query, it becomes
Declare @Year int
Set @Year = 2005
Select * from TableName where ColumnName >= DATEADD(Year,@Year-1900,0) and
ColumnName < DATEADD(Year,@Year-1899,0)
Lesson Learned :
- If a column has an index, don’t wrap up that Column with the expression. Instead put your expression in right hand side and keep that column in Left hand side.
- One of my assumed myths, “SQL optimizer can take care of the evaluating built-in functions and use index properly” is also shattered.
Thanks to moderator, I learned a very nice lesson.
About the author
RamiReddy is a .NET Developer, who has a passtion towards t-sql. He has been working with .net/sql server for the Last 4 years. He spends his spare time helping people in the ASP.NET and SQL Server Forums. See Ramireddy's profile.