GETDATE - Vorletzte Woche
Frage
Alle Antworten
-
Hallo Lutz,
damit bist Du leider auf dem Holzweg, da datepart in Verbindung mit week einen Fehler liefert.
ALTER TABLE dbo.index_table ADD col_datecw AS DATEPART(week, coldate) PERSISTED; Msg 4936, Level 16, State 1, Line 1 Computed column 'col_datecw' in table 'index_table' cannot be persisted because the column is non-deterministic.
Dennoch ist die Idee nicht schlecht. Man kann tatsächlich mit einem Index auf einem kalkulierten Attribut arbeiten. I. d. R. wird er auch verwendet!
USE tempdb GO DROP TABLE dbo.index_table; GO CREATE TABLE dbo.index_table ( id int not null identity (1, 1), colDate datetime not null DEFAULT (DATEDIFF(dd, RAND() * 1000, getdate())) CONSTRAINT pk_index_table PRIMARY KEY CLUSTERED (id) ); SET NOCOUNT ON GO INSERT INTO dbo.index_table DEFAULT VALUES GO 10000 SELECT * FROM dbo.index_table WHERE colDate >= DATEADD(dd, -14, getdate()) AND colDate <= DATEADD(dd, -7, getdate()) CREATE INDEX ix_index_table_coldate ON dbo.index_table (coldate); SELECT * FROM dbo.index_table WHERE colDate >= DATEADD(dd, -14, getdate()) AND colDate <= DATEADD(dd, -7, getdate())
Das obige Beispiel erstellt eine einfache Tabelle führt die von mir gemachte Abfrage einmal aus. Anschließend wird ein Index auf das Attribute coldate platziert um zu zeigen, dass eine Optimierung auf diesem Attribut stattfinde. Das Ergebnis des Ausführungsplans sieht dann wie folgt aus:
Man kann sehr gut erkennen, dass bei Abfrage 1 ein Table Scan ausgeführt wird, während bei der zweiten Abfrage der zuvor gesetzte Index verwendet wird. Somit wäre das - zunächst - eine sehr gute Lösung.
Nun füge ich ein berechnetes Attribut ein (als Beispiel Jahr des Datums) und setze auf dieses Attribut eine Index. Wichtig ist dabei, dass das Attribut PERSISTED angelegt wird:
-- Now we create a persistent column which stores the datediff ALTER TABLE dbo.index_table ADD col_dateyear AS CAST(DATEPART(year, coldate) AS int) PERSISTED; CREATE INDEX ix_index_table_year ON dbo.index_table (col_dateyear);
Die Ausführung auf das Jahr 2013 sieht dann wie folgt aus:
Es wird ein INDEX-SEEK unter Verwendung des zuvor angelegten Indexes durchgeführt. Wichtig für die Ausführungstrategie ist nicht der Index allein sondern auch die Histogrammdaten aus DBCC SHOW_STATISTICS () WITH HISTOGRAM. Ist der abzufragende Bereich >= 30% der Gesamtdatenmenge, wird wieder ein Index-Scan ausgeführt!
Ob und welcher Index verwendet wird, hängt immer davon ab, wie der Index selbst definiert ist. Würde ich z. B. auch noch das Attribut coldate in die Abfrage einbeziehen, kann es sein, dass SQL Server trotz des performanten Index auf col_dateyear einen Clustered Index Scan durchführt, um so die teureren Bookmark-Lookups zu vermeiden. Das ist ein sehr komplexes Thema und ich glaube, ich sollte dazu mal was in meinem Blog schreiben :D
Uwe Ricken
MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development
db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)- Bearbeitet Uwe RickenMVP Dienstag, 26. Februar 2013 13:00
