在SQL Server 2008中,添加了日期数据类型。

datetime列投射到date是可修改的,并且可以在datetime列上使用索引。

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


另一个选择是使用范围。

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


这些查询是否同样好,还是应该优先于其他?

评论

我不禁注意到LINQ2SQL在使用C#呈现时类似于obj.date_column.Date == date_variable的地方生成了cast(date_column as date)='value'的SQL。

#1 楼

迄今为止,可铸造性背后的机制称为动态寻道。

SQL Server调用内部函数GetRangeThroughConvert来获取范围的开始和结束。

令人惊讶的是,该范围与您的文字值不同。

创建一个每页一行,每天1440行的表

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 


然后运行

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'; 


第一个查询读取1443,第二个查询读取2883,因此它将读取一整天,然后将其丢弃以保留谓词。

该计划显示查找谓词为

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


因此,代替>= '20130101' ... < '20130102'而是读取> '20121231' ... < '20130102',然后丢弃所有2012-12-31行。

依赖它的另一个缺点是基数估计值可能不如与传统的范围查询。这可以在SQL Fiddle的修订版中看到。

表中的所有100行现在都与谓词匹配(日期时间在同一天都相隔1分钟)。

第二个(范围)查询正确估计100将匹配并使用聚簇索引扫描。 CAST( AS DATE)查询错误地估计只有一行会匹配,并生成带有关键查找的计划。

统计信息不会被完全忽略。如果表中的所有行都具有相同的datetime且与谓词匹配(例如20130101 00:00:0020130101 01:00:00),则该计划将显示一个聚集索引扫描,估计有31.6228行。

100 ^ 0.75 = 31.6228


因此,在这种情况下,估计值似乎来自于以下公式:


下表显示了猜想出合取词和所得的选择性与N的输入表基数的关系:

 | Conjuncts | Cardinality | Selectivity |
|-----------|-------------|-------------|
| 1         | N^(3/4)     | N^(-1/4)    |
| 2         | N^(11/16)   | N^(-5/16)   |
| 3         | N^(43/64)   | N^(-21/64)  |
| 4         | N^(171/256) | N^(-85/256) |
| 5         | N^(170/256) | N^(-86/256) |
| 6         | N^(169/256) | N^(-87/256) |
| 7         | N^(168/256) | N^(-88/256) |
| ...       |             |             |
| 175       | N^(0/256)   | N^(-1)      |
 



如果表中的所有行都具有相同的datetime并且与谓词不匹配(例如20130102 01:00:00),则它会退回到估计的行数1和具有查找的计划。

对于表具有多个DISTINCT值的情况,估计的行似乎与查询恰好在寻找20130101 00:00:00相同。

如果统计直方图恰好在2013-01-01 00:00:00.000处步进,则估算将基于EQ_ROWS(即不考虑该日期的其他时间)。否则,如果没有任何步调,则看起来好像使用了周围步骤中的AVG_RANGE_ROWS

由于datetime在许多系统中的精度约为3ms,因此实际重复值将很少,并且该数字将是1.

评论


嗨,马丁,您是否可以添加一个TL; DR零件,并在其中加上一些要点,并在不同情况下增加在这种情况下的转换,是否是个好主意?

– TT。
16年11月11日在8:25

@TT。我认为关键是这不是一个好主意。为什么要使用需要备忘单的方法?

–亚伦·伯特兰(Aaron Bertrand)
17年1月20日在14:50

#2 楼

我知道它具有Martin长期以来的GreatAnswer®,但是我想在SQL Server的较新版本中对行为进行一些更改。这似乎只在2008R2之前进行了测试。

借助新的USE HINT,可以进行一些基数估计时间旅行,我们可以看到何时发生了变化。

使用与SQL Fiddle中的设置相同。

CREATE TABLE T ( ID INT IDENTITY PRIMARY KEY, DateTimeCol DATETIME, Filler CHAR(8000) NULL );

CREATE INDEX IX_T_DateTimeCol ON T ( DateTimeCol );


WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
     E02(N) AS (SELECT 1 FROM E00 a, E00 b),
     E04(N) AS (SELECT 1 FROM E02 a, E02 b),
     E08(N) AS (SELECT 1 FROM E04 a, E04 b),
     Num(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY E08.N) FROM E08)
INSERT INTO T(DateTimeCol)
SELECT TOP 100 DATEADD(MINUTE, Num.N, '20130101')
FROM Num;


我们可以像这样测试不同的级别:

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100' ));
GO

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130' ));
GO 

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101'
OPTION ( USE HINT ( 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140' ));
GO 


此处提供了所有这些计划。兼容级别100和110都给出了关键查找计划,但是从兼容级别120开始,我们开始获得具有100行估计的相同扫描计划。达到兼容性级别140才是正确的。







>= '20130101', < '20130102'计划的基数估计保持在预期的100。