General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Ok, I'll add my two cents...
I agree with Cetin that the datetime and BETWEEN has problems. However, there is a simple workaround that does not seem to suffer a performance hit and that is to cast the datetime to date.
I've a simple dataset and I want to find all the Datetime2 data in column myDate for the month of January in 2018. The column myDate has a non-clustered index.
*********************************************************
*********************************************************Example 1
*********************************************************
Query 1 the Gold Standard:
select myDate
from DateTester
where myDate >= '20180101' and myDate < '20180201'
order by myDate;
Query Cost: 50% (uses index seek)
Query 2:
select myDate
from DateTester
where cast(myDate as date) between '20180101' and '20180131'
order by myDate;
Query Cost: 50% (uses index seek)
*********************************************************
Basically, the above two queries do the same thing returning the same data. While their execution plans are slightly different from each other, the cost is the same and they both use an Index Seek to do the real work.
*********************************************************
*********************************************************Example 2
*********************************************************
Query 1:
select myDate
from DateTester
where myDate >= '20180101' and myDate < '20180201'
order by myDate;
Uses the same Index Seek and the Query Cost (relative to the batch) is 12%
Query 2:
select myDate
from DateTester
where myDate like '2018-01%'
order by myDate;
Uses a slower Index SCAN and the Query Cost (relative to the batch) is 88%
Also, you get this warning about the execution plan:
Type conversion in expression (CONVERT_IMPLICIT(varchar(40),[Junk].[dbo].[DateTester].[myDate],121)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(varchar(40),[Junk].[dbo].[DateTester].[myDate],121)>='2018-00þ') may affect "SeekPlan" in query plan choice
****************************************
Clearly, the choice is to avoid using LIKE and strings for date or any kind of datetime queries.
Yes, the BETWEEN is problematic, but casting to a date solves that problem.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only