Ein gefilterter Index ist ein nicht-geclusterter Index, der für eine genau definierte Datenteilmenge in einem SQL-Server-Tabellenobjekt erstellt wird. "Genau definiert" bedeutet in diesem Fall diejenigen Datenteilmengen, die ausschließlich zur Erfüllung der Abfragekriterien verwendet werden. Wenn beispielsweise ein Feld in einer Tabelle enthalten ist, die vorwiegend NULL-Werte aufweist, könnte das Erstellen eines gefilterten Index von Vorteil sein, der ausschließlich die Werte enthält, die NICHT NULL sind. Allerdings lassen sich keine geclusterten Indexe mit einem Filter definieren.
Warum ein gefilterter Index?
Gefilterte Indexe können Performancevorteile in Szenarien bieten, in denen ein Großteil der Abfragen in einer Tabelle eine Filterung für eine bestimmte Datenteilmenge durchführen. Diese Indexe sind meist viel kleiner als ein Index für das gesamte Feld, so dass weniger Indexspeicher erforderlich ist. Außerdem erfordern gefilterte Indexe in der Regel weniger Administrationsaufwand. Da der gefilterte Index kleiner ist, betreffen an den Daten vorgenommene Bearbeitungsvorgänge kleinere Teilmengen des Index und erfordern somit weniger Datenbank-I/O-Operationen.
Erstellen eines gefilterten Index
Im Folgenden wird dargestellt, wie man einen gefilterten Index erstellt und wie sich die durch seine Nutzung erreichten Performance-Vorteile auswirken. Dazu muss man als Erstes das folgende Script ausführen, um die SalesHistory-Tabelle zu erstellen und zu bevölkern.
Mit einigen Einträgen in der SalesHistory-Tabelle aktualisiert man dann das SaleDate auf NULL, und zwar für 6 von 7 Einträgen in der Tabelle. So entsteht eine recht spärliche Verteilung von Werten im SaleDate-Feld. Nach der Aktualisierung soll ein normaler nicht-geclusterter Index im SaleDate-Feld erstellt werden.
Dann den folgenden Befehl ausführen, um eine IO-Statistik hierfür auszuführen. So kann man die IO-Werte für jeden ausgeführten TSQL-Befehl anzeigen.
Die folgende Abfrage gibt alle Zeilen aus SalesHistory-Tabelle aus, in denen SaleDate einen Wert enthält. Diese Abfrage verwendet den vorher erstellten Index idx_SalesHistory_SaleDate sowie eine Index-Seek-Operation zur Ausgabe von 2142 Zeilen. Diese Abfrage erfordert 8 logische Lesevorgänge aus der Datenbank, um die erforderlichen Zeilen auszugeben.
Dabei ist zu beachten, dass die in diesem Artikel verwendeten Abfragen nur das SaleDate-Feld im Resultset ausgeben. Das liegt daran, dass ein Ausschließen von Feldern oder Hinzufügen zusätzlicher Felder in der SELECT-Liste den Ausführungsplan verändert. Für den Zweck dieses Artikels soll nur das Feld ausgegeben werden, für das Kriterien festgelegt werden.
Die indexbezogenen Daten für den idx_SalesHistory_SaleDate-Index können durch Abfragen einiger System-Views angezeigt werden.
Die folgende Abfrage ähnelt der oben genannten Abfrage, filtert jedoch die Zeilen, in denen SaleDate einen NULL-Wert enthält. Diese Abfrage führt außerdem einen Index-Seek für den oben erstellten Index durch, benötigt dabei jedoch 31 logische Lesevorgänge aus der Datenbank, da die Abfrage 12858 Einträge ausgibt.
Lesermeinungen zum Artikel
MS schrieb mir dazu auf meinen Wunsch:
Missing a possibillity (like in MSAccess) to have an unique Index which allows (ignores) multiple NULL values.
In SQL Server 2008 you can work around this by creating a unique filterered index.
Example:
CREATE TABLE dbo.TestTable (
Col1 INT NULL
);
CREATE UNIQUE INDEX Col1Index ON dbo.TestTable (Col1) WHERE Col1 IS NOT NULL;
GO
INSERT dbo.TestTable (Col1) VALUES (1); -- Succeeds
INSERT dbo.TestTable (Col1) VALUES (1); -- Fails
INSERT dbo.TestTable (Col1) VALUES (NULL); -- Succeeds
INSERT dbo.TestTable (Col1) VALUES (NULL); -- Succeeds
For SQL Server 2005 you can get the same behaviour with an indexed view.
mit freundlichen Grüßen aus Nürnberg
Klaus Oberdalhoff