Dotychczasowa obsada jest możliwa do sprzedania, ale czy to dobry pomysł?

W SQL Server 2008 dodano typ danych daty .

Rzutowanie datetimekolumny na sargabledate jest możliwe i można użyć indeksu na datetimekolumnie.

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

Inną opcją jest użycie zakresu.

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

Czy te zapytania są równie dobre, czy jedno powinno być preferowane nad drugim?

Mechanizm leżący u podstaw możliwości rzutowania nazywa się dynamicznym wyszukiwaniem .

SQL Server wywołuje funkcję wewnętrzną, GetRangeThroughConvertaby uzyskać początek i koniec zakresu.

Nieoczekiwanie nie jest to ten sam zakres, co twoje dosłowne wartości.

Tworzenie tabeli z wierszem na stronę i 1440 wierszami dziennie

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 

Potem biegnie

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

Pierwsze zapytanie zawiera 1443odczyty, a drugie 2883odczytuje cały dodatkowy dzień, a następnie odrzuca je względem pozostałego predykatu.

Plan pokazuje, że predykatem wyszukiwania jest

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

Zamiast >= '20130101' ... < '20130102'tego czyta, > '20121231' ... < '20130102'a następnie odrzuca wszystkie 2012-12-31wiersze.

Kolejną wadą polegającą na tym jest to, że szacunki liczności mogą nie być tak dokładne, jak w przypadku tradycyjnego zapytania o zakres. Można to zobaczyć w poprawionej wersji SQL Fiddle .

Wszystkie 100 wierszy w tabeli jest teraz zgodnych z predykatem (z odstępami czasu 1 minuta wszystkie w tym samym dniu).

Drugie zapytanie (zakres) poprawnie szacuje, że 100 będzie pasowało i używa skanowania indeksu klastrowego. CAST( AS DATE)Zapytania nieprawidłowo szacuje, że tylko jeden wiersz będzie pasował i tworzy plan z najważniejszych wyszukiwań.

Statystyki nie są całkowicie ignorowane. Jeśli wszystkie wiersze w tabeli mają to samo datetimei pasuje do predykatu (np. 20130101 00:00:00Lub 20130101 01:00:00), wówczas plan pokazuje skanowanie indeksu klastrowego z szacowanymi 31,6228 wierszami.

100 ^ 0.75 = 31.6228

Więc w takim przypadku wydaje się, że szacunek pochodzi z formuły tutaj .

Jeśli wszystkie wiersze w tabeli mają to samo datetimei nie pasuje do predykatu (np. 20130102 01:00:00), Wówczas wraca do szacowanej liczby wierszy 1 i planu z przeglądami.

W przypadkach, w których tabela ma więcej niż jedną DISTINCTwartość, szacunkowe wiersze wydają się być takie same, jakby zapytanie szukało dokładnie 20130101 00:00:00.

Jeśli histogram statystyczny zawiera krok, 2013-01-01 00:00:00.000wówczas szacunek będzie oparty na EQ_ROWS(tzn. Nie będzie uwzględniał innych czasów w tym dniu). W przeciwnym razie, jeśli nie ma kroku, wygląda na to, że wykorzystuje AVG_RANGE_ROWSkroki z otaczających kroków.

Ponieważ datetimedokładność wynosi około 3 ms w wielu systemach, będzie bardzo niewiele rzeczywistych zduplikowanych wartości, a liczba ta będzie wynosić 1.

Wiem, że ma to od dawna Great Answer® od Martina, ale chciałem dodać tutaj pewne zmiany w zachowaniu w nowszych wersjach SQL Server. Wydaje się, że zostało to przetestowane tylko do 2008R2.

Dzięki nowym WSKAZÓWKOM UŻYTKOWANIA, które umożliwiają wykonanie podróży w czasie oszacowania liczności, możemy zobaczyć, kiedy coś się zmieniło.

Przy użyciu tej samej konfiguracji, co w 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;

Możemy przetestować różne poziomy:

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 

Plany wszystkich z nich są dostępne tutaj . Oba poziomy kompatowania 100 i 110 dają kluczowy plan wyszukiwania, ale zaczynając od poziomu zgodności 120, zaczynamy uzyskiwać ten sam plan skanowania z szacunkami 100 wierszy. Dotyczy to poziomu zgodności 140.

Oszacowanie liczności dla >= '20130101', < '20130102'planów pozostaje na poziomie 100, czego oczekiwano.