O elenco até o momento é sargável, mas é uma boa ideia?

O mecanismo por trás da sargabilidade do casting até hoje é chamado de busca dinâmica .

O SQL Server chama uma função interna GetRangeThroughConvertpara obter o início e o fim do intervalo.

Surpreendentemente, este não é o mesmo intervalo que seus valores literais.

Criando uma tabela com uma linha por página e 1440 linhas por dia

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 

Em seguida, correndo

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

A primeira consulta tem 1443leituras e a segunda 2883, portanto, está lendo um dia adicional inteiro e, em seguida, descartando-o contra um predicado residual.

O plano mostra que o predicado de busca é

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

Então, em vez de >= '20130101' ... < '20130102'ler > '20121231' ... < '20130102', descarta todas as 2012-12-31linhas.

Outra desvantagem de confiar nele é que as estimativas de cardinalidade podem não ser tão precisas quanto na consulta de intervalo tradicional. Isso pode ser visto em uma versão corrigida do seu SQL Fiddle .

Todas as 100 linhas na tabela agora correspondem ao predicado (com datas e horas separadas por 1 minuto, todas no mesmo dia).

A segunda consulta (intervalo) estima corretamente que 100 corresponderá e usa uma verificação de índice clusterizado. A CAST( AS DATE)consulta estima incorretamente que apenas uma linha corresponderá e produz um plano com pesquisas de chave.

As estatísticas não são completamente ignoradas. Se todas as linhas na tabela tiverem o mesmo datetimee corresponderem ao predicado (por exemplo , 20130101 00:00:00ou 20130101 01:00:00), o plano mostrará uma varredura de índice clusterizado com 31,6228 linhas estimadas.

100 ^ 0.75 = 31.6228

Portanto, nesse caso, parece que a estimativa é derivada desta fórmula :

A tabela a seguir mostra o número de conjuntos adivinhados e a seletividade resultante em função da cardinalidade da tabela de entrada de 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)      |

Se todas as linhas na tabela tiverem o mesmo datetimee não corresponderem ao predicado (por exemplo 20130102 01:00:00, ), ele retornará à contagem de linhas estimada de 1 e ao plano com pesquisas.

Para os casos em que a tabela possui mais de um DISTINCTvalor, as linhas estimadas parecem ser as mesmas como se a consulta estivesse procurando exatamente 20130101 00:00:00.

Se o histograma estatístico tiver um passo em 2013-01-01 00:00:00.000então a estimativa será baseada no EQ_ROWS(ou seja, não levando em consideração outros horários naquela data). Caso contrário, se não houver nenhuma etapa, parece que ele usa as AVG_RANGE_ROWSetapas ao redor.

Como datetimetem uma precisão de aproximadamente 3 ms em muitos sistemas, haverá muito poucos valores reais duplicados e esse número será 1.

Eu sei que isso tem uma Great Answer® de longa data de Martin, mas eu queria adicionar algumas mudanças no comportamento aqui em versões mais recentes do SQL Server. Isso parece ter sido testado apenas até 2008R2.

Com as novas USE HINTs que possibilitam algumas viagens no tempo de estimativa de cardinalidade, podemos ver quando as coisas mudaram.

Usando a mesma configuração do 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;

Podemos testar os diferentes níveis assim:

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 

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

Os planos para todos eles estão disponíveis aqui . Os níveis de compatibilidade 100 e 110 fornecem o plano de pesquisa de chave, mas começando com o nível de compatibilidade 120, começamos a obter o mesmo plano de varredura com estimativas de 100 linhas. Isso é verdade até o nível de compatibilidade 150.

A estimativa de cardinalidade para os >= '20130101', < '20130102'planos permanece em 100, o que era esperado.