XML FeedsI was reading Itzik Ben-Gan's An Introduction to New T-SQL Programmability Features in SQL Server 2008 article yesterday after one of my friends allerted me to the following from that article
For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column:
- USE AdventureWorks;
- SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate
- FROM Sales.CurrencyRate
- WHERE CAST(CurrencyRateDate AS DATE) = '20040701';
I was surprised by this, as we all know functions/conversions on column names are generaly bad for performance.
Let's see how this works. First create this table in the tempdb database.
- use tempdb
- go
- create table TestDatetimePerf (SomeCol datetime,id int identity)
- go
This will insert 2048 rows with dates between 2008-01-01 12 AM and 2008-03-26 7 AM
- insert TestDatetimePerf(SomeCol)
- select dateadd(hh,number,'20080101')
- from master..spt_values
- where type ='P'
- go
- create index ix_Date on TestDatetimePerf(SomeCol)
- go
Turn on the execution plan
- set showplan_text on
- go
Execute the following query
- select *
- from TestDatetimePerf
- where convert(varchar(30),SomeCol,112) = '20080103'
Here is the plan
- |--Table Scan(OBJECT:([tempdb].[dbo].[TestDatetimePerf]),
- --WHERE:(CONVERT(varchar(30),[tempdb].[dbo].[TestDatetimePerf].[SomeCol],112)=[@1]))
As you can see that results in a scan.
What happens when you convert to date?
- select *
- from TestDatetimePerf
- where convert(date,SomeCol) = '20080103'
Here is the plan
- |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
- |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1006]))
- | |--Compute Scalar(DEFINE:(([Expr1007],[Expr1008],[Expr1006])=GetRangeThroughConvert('2008-01-03','2008-01-03',(62))))
- | | |--Constant Scan
- | |--Index Seek(OBJECT:([tempdb].[dbo].[TestDatetimePerf].[ix_Date]),
- --SEEK:([tempdb].[dbo].[TestDatetimePerf].[SomeCol] > [Expr1007]
- --AND [tempdb].[dbo].[TestDatetimePerf].[SomeCol] < [Expr1008]),
- --WHERE:(CONVERT(date,[tempdb].[dbo].[TestDatetimePerf].[SomeCol],0)='2008-01-03') ORDERED FORWARD)
- |--RID Lookup(OBJECT:([tempdb].[dbo].[TestDatetimePerf]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
See that? You get a seek instead, very interesting. It would be nice that when you use convert with the style optional parameter that the optimizer would be smart enough to convert that also to a seek.
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
I just wanted to point out that when using the Select All option to copy and paste into SQL Management Studio, the " ' " around the date is not correct for some reason. Just wanted to let you know. 
Dave I fixed it, it was a smart quotes plugin that caused it
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.