Linee guida per la progettazione di indici filtrati

Quando una colonna dispone solo di un numero ridotto di valori rilevanti per le query, è possibile creare un indice filtrato sul subset di valori. Ad esempio, quando la maggior parte dei valori di una colonna è costituita da valori NULL e la query esegue la selezione solo dai valori non NULL, è possibile creare un indice filtrato per le righe di dati non NULL. L'indice risultante sarà minore e sarà possibile gestirlo con costi ridotti rispetto a un indice non cluster di tabella completa definito sulle stesse colonne chiave.

Nel database AdventureWorks2008R2 è presente ad esempio una tabella Production.BillOfMaterials con 2679 righe. La colonna EndDate dispone solo di 199 righe che contengono un valore non NULL, mentre le altre 2480 righe contengono valori NULL. L'indice filtrato seguente coprirà query che restituiscono le colonne definite nell'indice e che selezionano solo righe con un valore non NULL per EndDate.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

L'indice filtrato FIBillOfMaterialsWithEndDate è valido per la query seguente. È possibile visualizzare il piano di esecuzione della query per determinare se Query Optimizer ha utilizzato l'indice filtrato. Per informazioni sulla visualizzazione del piano di esecuzione della query, vedere Analisi di una query.

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

Per ulteriori informazioni sulla creazione di indici filtrati e sulla definizione dell'espressione del predicato dell'indice filtrato, vedere CREATE INDEX (Transact-SQL).

Se in una tabella sono presenti righe di dati eterogenei, è possibile creare un indice filtrato per una o più categorie di dati.

Ogni prodotto elencato nella tabella Production.Product, ad esempio, è assegnato a un ProductSubcategoryID, associato a sua volta alle categorie di prodotti Bikes, Components, Clothing o Accessories. Queste categorie sono eterogenee poiché i valori di colonna relativi nella tabella Production.Product non sono strettamente correlati. Ad esempio Color, ReorderPoint, ListPrice, Weight, Class e Style dispongono di caratteristiche univoche per ogni categoria di prodotti. Se vengono eseguite query frequenti su Accessories con sottocategorie 27-36, è possibile migliorarne le prestazioni creando un indice filtrato sulla sottocategoria Accessories.

Nell'esempio seguente viene creato un indice filtrato su tutti i prodotti delle sottocategorie Accessories nella tabella Production.Product.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

L'indice filtrato FIProductAccessories è valido per la query seguente, in quanto i risultati

della query sono contenuti nell'indice e il piano della query non include una ricerca nella tabella di base. Ad esempio, l'espressione del predicato di query ProductSubcategoryID = 33 è un subset del predicato dell'indice filtrato ProductSubcategoryID >= 27 e ProductSubcategoryID <= 36, le colonne ProductSubcategoryID e ListPrice nel predicato di query sono entrambe colonne chiave nell'indice e il nome è archiviato al livello foglia dell'indice come colonna inclusa.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

Una vista è una tabella virtuale in cui viene archiviata la definizione di una query ed è caratterizzata da uno scopo e da una funzionalità più ampi rispetto a un indice filtrato. Per ulteriori informazioni sulle viste, vedere Informazioni sulle viste e Scenari di utilizzo delle viste. Nella tabella seguente vengono confrontate alcune funzionalità consentite nelle viste con quelle relative agli indici filtrati.

*Per informazioni sulla logica di confronto semplice in un predicato, vedere la sintassi della clausola WHERE in CREATE INDEX.

**Per informazioni sulla logica di confronto complessa in un predicato, vedere la sintassi della clausola WHERE per SELECT.

Non è possibile creare un indice filtrato su una vista. Query Optimizer è tuttavia in grado di sfruttare i vantaggi offerti da un indice filtrato definito in una tabella cui si fa riferimento in una vista e considera un indice filtrato per una query che esegue la selezione da una vista se i risultati della query saranno corretti. Nell'esempio seguente vengono creati una vista con date di inizio successive all'1 aprile 2000 e un indice filtrato con date di inizio successive all'1 agosto 2000.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

Nell'esempio seguente la query seleziona le date di inizio successive all'1 settembre 2004, tutte contenute nell'indice e nella vista filtrati. Query Optimizer considera l'indice filtrato FIBillOfMaterialsByStartDate poiché contiene i risultati corretti per la query.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

Nell'esempio seguente la query seleziona le date di inizio successive all'1 giugno 2004, tutte contenute nella vista, ma non nell'indice filtrato. Query Optimizer non considera l'indice filtrato FIBillOfMaterialsByStartDate perché la query, se utilizza l'indice filtrato, può restituire risultati diversi confrontati con i risultati corretti restituiti quando la query esegue la selezione dalla vista.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

Rispetto alle viste indicizzate gli indici filtrati offrono i vantaggi seguenti:

• Costi di manutenzione dell'indice ridotti. Query Processor, ad esempio, utilizza una quantità inferiore di risorse della CPU per aggiornare un indice filtrato rispetto a una vista indicizzata.

• Qualità del piano migliorata. Durante la compilazione della query, ad esempio, Query Optimizer preferisce in molte situazioni utilizzare l'indice filtrato anziché la vista indicizzata equivalente.

• L'indice online viene ricompilato. È possibile ricompilare gli indici filtrati mentre sono disponibili per le query. La ricompilazione dell'indice online non è supportata per le viste indicizzate. Per ulteriori informazioni, vedere l'opzione REBUILD per ALTER INDEX (Transact-SQL).

• Indici non univoci. Gli indici filtrati possono essere non univoci, mentre le viste indicizzate devono essere univoche.

Per le ragioni elencate in precedenza, è consigliabile utilizzare un indice filtrato anziché una vista indicizzata, quando possibile. È possibile utilizzare un indice filtrato anziché una vista indicizzata quando vengono soddisfatte le condizioni seguenti: la vista fa riferimento a una sola tabella, le query non restituiscono colonne calcolate e il predicato della vista utilizza una logica di confronto semplice. L'espressione del predicato seguente, ad esempio, è consentita in una definizione di una vista ma non in indici filtrati poiché contiene l'operatore LIKE.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

È consigliabile inserire un numero ridotto di colonne chiave o incluse in una definizione di indice filtrato e incorporare solo le colonne necessarie affinché Query Optimizer scelga l'indice filtrato per il piano di esecuzione della query. Sebbene Query Optimizer possa scegliere un indice filtrato per la query indipendentemente dal fatto che la copra o meno, tuttavia è più probabile che venga scelto un indice filtrato che copre la query. Per ulteriori informazioni sulla copertura di query, vedere Creazione di indici con colonne incluse.

In alcuni casi, un indice filtrato copre la query senza includere le colonne nell'espressione che lo definisce come colonne chiave o incluse nella definizione dell'indice stesso. Le linee guida seguenti indicano quando una colonna nell'espressione che definisce l'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato stesso. Gli esempi si riferiscono all'indice filtrato FIBillOfMaterialsWithEndDate creato in precedenza.

Non è necessario che una colonna nell'espressione che definisce l'indice filtrato sia una colonna chiave o inclusa nella definizione dell'indice stesso se l'espressione che definisce l'indice filtrato è equivalente al predicato della query e la query non restituisce la colonna in tale espressione con i risultati della query. L'indice FIBillOfMaterialsWithEndDate, ad esempio, copre la query seguente perché il predicato della query è equivalente all'espressione di filtro ed EndDate non viene restituito con i risultati della query. Per FIBillOfMaterialsWithEndDate non è necessario utilizzare EndDate come colonna chiave o inclusa nella definizione dell'indice filtrato.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

Una colonna nell'espressione che definisce l'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice se il predicato della query la utilizza in un confronto non equivalente all'espressione che definisce l'indice filtrato. L'indice FIBillOfMaterialsWithEndDate, ad esempio, è valido per la query seguente perché seleziona un subset di righe dall'indice filtrato. Tale indice tuttavia non copre la query poiché EndDate viene utilizzato nel confronto EndDate > '20040101', che non è equivalente all'espressione che definisce l'indice filtrato. Poiché Query Processor non è in grado di eseguire questa query senza cercare i valori di EndDate, Di conseguenzaEndDate deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

Una colonna nell'espressione che definisce l'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice se è presente nel set di risultati della query. L'indice FIBillOfMaterialsWithEndDate, ad esempio, non copre la query seguente perché restituisce la colonna EndDate nei risultati della query. Di conseguenza EndDate deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

Non è necessario che la chiave primaria della tabella sia una colonna chiave o inclusa nella definizione dell'indice filtrato poiché viene inclusa automaticamente in tutti gli indici non cluster, inclusi quelli filtrati.

Se l'operatore di confronto specificato nell'espressione che definisce l'indice filtrato determina una conversione dei dati implicita o esplicita, si verificherà un errore se la conversione viene eseguita sul lato sinistro di un operatore di confronto. Una soluzione consiste nello scrivere l'espressione che definisce l'indice filtrato con l'operatore di conversione dei dati (CAST o CONVERT) sul lato destro dell'operatore di confronto.

Nell'esempio seguente viene creata una tabella con tipi di dati diversi.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

Nella definizione dell'indice filtrato seguente la colonna b viene convertita implicitamente a un tipo di dati integer per eseguire il confronto con la costante 1. Verrà generato un messaggio di errore 10611 poiché la conversione viene eseguita sul lato sinistro dell'operatore nel predicato filtrato.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

La soluzione consiste nel convertire la costante sul lato destro in modo che sia dello stesso tipo della colonna b, come illustrato nell'esempio seguente:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Lo spostamento della conversione dei dati dal lato sinistro a quello destro di un operatore di confronto potrebbe modificare il significato della conversione. Nell'esempio precedente, quando l'operatore CONVERT è stato aggiunto al lato destro, il confronto è stato modificato da un confronto di un tipo integer in un confronto di tipo varbinary.

Nella vista del catalogo sys.sql_expression_dependencies viene registrata ogni colonna nell'espressione che definisce l'indice filtrato come una dipendenza di riferimento. Non è possibile eliminare, rinominare o modificare la definizione di una colonna della tabella specificata in un'espressione che definisce l'indice filtrato.

Gli indici filtrati risultano particolarmente utili quando le colonne contengono subset ben definiti di dati cui le query fanno riferimento nelle istruzioni SELECT. Di seguito vengono indicati alcuni esempi:

• Colonne di tipo sparse che contengono solo un numero limitato di valori non NULL.

• Colonne eterogenee che contengono categorie di dati.

• Colonne che contengono intervalli di valori diversi, ad esempio quantità di denaro, ore e date.

• Partizioni di tabelle definite da logica di confronto semplice per i valori di colonna.

I costi di manutenzione ridotti ottenuti dall'utilizzo di indici filtrati sono più apprezzabili quando il numero di righe dell'indice non è elevato rispetto a quello di un indice di tabella completa. Se l'indice filtrato include la maggior parte delle righe della tabella, è possibile che i costi di manutenzione siano maggiori rispetto a quelli relativi a un indice di tabella completa. In questo caso è opportuno utilizzare un indice di tabella completa anziché un indice filtrato.

Gli indici filtrati sono definiti in una tabella e supportano solo operatori di confronto semplici. Se è necessaria un'espressione di filtro che fa riferimento a più tabelle o dispone di logica complessa, è necessario creare una vista.

In generale, nel Motore di database e negli strumenti viene fornito per gli indici filtrati lo stesso supporto disponibile per gli indici non cluster di tabella completa, considerando gli indici filtrati come un tipo particolare di indici non cluster. Nell'elenco seguente sono disponibili note sugli strumenti e sulle caratteristiche che supportano, non supportano o supportano solo in parte gli indici filtrati.

• ALTER INDEX supporta gli indici filtrati. Per modificare l'espressione che definisce l'indice filtrato, utilizzare CREATE INDEX WITH DROP_EXISTING.

• La caratteristica degli indici mancanti non suggerisce indici filtrati.

• In Ottimizzazione guidata Motore di database vengono considerati gli indici filtrati quando viene consigliata l'opzione di ottimizzazione di indici ed è possibile che venga indicato di utilizzare un indice filtrato is not null.

• Le operazioni sugli indici online supportano gli indici filtrati.

• Gli hint di tabella supportano gli indici filtrati, ma sono caratterizzati da alcune restrizioni che non si applicano agli indici non filtrati. Tali restrizioni vengono illustrate nella sezione seguente.

In alcuni casi, una query con parametri non contiene informazioni sufficienti in fase di compilazione affinché Query Optimizer scelga un indice filtrato. Per fornire le informazioni mancanti, è possibile riscrivere la query. Nell'esempio seguente Query Optimizer non considera l'indice filtrato FIBillOfMaterialsWithComponentID per l'istruzione SELECT perché i valori del parametro per @p e @q non sono noti in fase di compilazione. L'esempio di query seguente viene eseguito con SHOWPLAN_XML impostato su ON, in modo che sia possibile visualizzare gli indici filtrati non corrispondenti per le query con parametri nell'output di SHOWPLAN_XML.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

L'elemento UnmatchedIndexes e il sottoelemento Parameterization nell'output di SHOWPLAN_XML indicano che l'indice filtrato non costituiva una corrispondenza per la query. Per informazioni sulla visualizzazione dell'output di SHOWPLAN_XML, vedere Showplan XML.

La soluzione consiste nel modificare la query in modo che i relativi risultati siano vuoti quando un'espressione con parametri non è un subset del predicato del filtro. Nella query seguente viene dimostrata questa modifica. Se alla clausola WHERE si aggiunge l'espressione ComponentID in (533, 324, 753), i risultati della query costituiranno un subset dell'espressione del predicato filtrata. Grazie a questa modifica, Query Optimizer può considerare l'indice filtrato FIBillOfMaterialsWithComponentID per l'istruzione SELECT seguente.

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Parametrizzazione semplice

Query Optimizer può utilizzare un indice filtrato, anche se non copre la query, eseguendo una ricerca chiave per recuperare le colonne rimanenti non coperte dall'indice filtrato. Per ulteriori informazioni sulle ricerche chiave, vedere Operatore Showplan Key Lookup. Query Optimizer potrebbe adottare questo comportamento se il numero stimato di ricerche chiave non è elevato. Nella query seguente viene utilizzato un hint per l'indice per fare in modo che Query Processor utilizzi FIBillOfMaterialsWithEndDate con le ricerche tramite segnalibro per EndDate. La ricerca chiave viene eseguita per il confronto EndDate > @date nel predicato della query.

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Si noti che EndDate > @Date non costituisce una corrispondenza esatta con l'espressione dell'indice filtrato EndDate IS NOT NULL. L'indice filtrato è ancora valido per questa query con parametri perché restituisce un subset delle righe specificate dall'espressione che definisce l'indice filtrato.