Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server 2008 the date datatype was added.

In this Connect item you can see that casting a datetime column to date is sargable and may use an index on the datetime column.

select *
from T
where cast(DateTimeCol as date) = '20130101';

The other option you have is to use a range instead.

select *
from T
where DateTimeCol >= '20130101' and
      DateTimeCol < '20130102'

Are these queries equally good or should one be preferred over the other?

SQL Fiddle

share|improve this question
2  
What does the execution plan say? –  a_horse_with_no_name Feb 4 '13 at 8:31
3  
I can't help noticing that LINQ2SQL generates SQL where cast(date_column as date) = 'value' when presented with C# similar to where obj.date_column.Date == date_variable. –  GSerg Sep 18 '13 at 8:10

1 Answer 1

up vote 24 down vote accepted

The mechanism behind the sargability of casting to date is called dynamic seek.

SQL Server calls an internal function GetRangeThroughConvert to get the start and end of the range.

Somewhat surprisingly this is not the same range as your literal values.

Creating a table with a row per page and 1440 rows per day

CREATE TABLE T
  (
     DateTimeCol DATETIME PRIMARY KEY,
     Filler      CHAR(8000) DEFAULT 'X'
  );

WITH Nums(Num)
     AS (SELECT number
         FROM   spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 1440),
     Dates(Date)
     AS (SELECT {d '2012-12-30'} UNION ALL
         SELECT {d '2012-12-31'} UNION ALL
         SELECT {d '2013-01-01'} UNION ALL
         SELECT {d '2013-01-02'} UNION ALL
         SELECT {d '2013-01-03'})
INSERT INTO T
            (DateTimeCol)
SELECT DISTINCT DATEADD(MINUTE, Num, Date)
FROM   Nums,
       Dates 

Then running

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM   T
WHERE  DateTimeCol >= '20130101'
       AND DateTimeCol < '20130102'

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'; 

The first query has 1443 reads and the second 2883 so it is reading an entire additional day then discarding it against a residual predicate.

The plan shows the seek predicate is

Seek Keys[1]: Start: DateTimeCol > Scalar Operator([Expr1006]), 
               End: DateTimeCol < Scalar Operator([Expr1007])

So instead of >= '20130101' ... < '20130102' it reads > '20121231' ... < '20130102' then discards all the 2012-12-31 rows.

Another disadvantage of relying on it is that the cardinality estimates may not be as accurate as with the traditional range query. This can be seen in an amended version of your SQL Fiddle.

All 100 rows in the table now match the predicate (with datetimes 1 minute apart all on the same day).

The second (range) query correctly estimates that 100 will match and uses a clustered index scan. The CAST( AS DATE) query incorrectly estimates that only one row will match and produces a plan with key lookups.

The statistics aren't ignored completely. If all rows in the table have the same datetime and it matches the predicate (e.g. 20130101 00:00:00 or 20130101 01:00:00) then the plan shows a clustered index scan with an estimated 31.6228 rows.

100 ^ 0.75 = 31.6228

So in that case it appears the estimate is derived from the formula here.

If all rows in the table have the same datetime and it doesn't match the predicate (e.g. 20130102 01:00:00) then it falls back to the estimated row count of 1 and the plan with lookups.

For the cases where the table has more than one DISTINCT value the estimated rows seems to be the same as if the query was looking for exactly 20130101 00:00:00.

If the statistics histogram happens to have a step at 2013-01-01 00:00:00.000 then the estimate will be based on the EQ_ROWS (i.e. not taking into account other times on that date). Otherwise if there is no step it looks as though it uses the AVG_RANGE_ROWS from the surrounding steps.

As datetime has a precision of approx 3ms in many systems there will be very few actual duplicate values and this number will be 1.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.