Linee guida per la progettazione di indici filtrati

Un indice filtrato è un indice non cluster ottimizzato, particolarmente indicato per coprire query che selezionano dati da un subset ben definito. Un indice di questo tipo utilizza un predicato del filtro per indicizzare una parte di righe nella tabella. Se confrontato con indici di tabella completa, un indice filtrato progettato correttamente consente di migliorare le prestazioni di esecuzione delle query e di ridurre i costi di manutenzione e di archiviazione dell'indice stesso.

Rispetto agli indici di tabella completa, gli indici filtrati consentono di ottenere i vantaggi seguenti:

• Prestazioni di esecuzione delle query e qualità del piano migliorate

  Un indice filtrato progettato correttamente migliora le prestazioni di esecuzione delle query e la qualità del piano di esecuzione poiché è caratterizzato da dimensioni minori rispetto a un indice non cluster di tabella completa e dispone di statistiche filtrate. Queste ultime sono più accurate delle statistiche di tabella completa poiché coprono solo le righe nell'indice filtrato.

• Costi di manutenzione dell'indice ridotti

  La manutenzione di un indice viene eseguita solo quando le istruzioni DML (Data Manipulation Language) influiscono sui dati relativi all'indice. Un indice filtrato consente di ridurre i costi di manutenzione rispetto a un indice non cluster di tabella completa poiché è caratterizzato da dimensioni minori e viene gestito solo se i dati relativi sono interessati. È possibile disporre di un numero elevato di indici filtrati, soprattutto quando questi ultimi contengono dati interessati raramente. In modo analogo, se un indice filtrato contiene dati interessati di frequente, la dimensione minore dell'indice consente di ridurre il costo di aggiornamento delle statistiche.

• Costi di archiviazione dell'indice ridotti

  La creazione di un indice filtrato può ridurre lo spazio di archiviazione su disco per gli indici non cluster nel caso in cui non sia necessario un indice di tabella completa. È possibile sostituire un indice di tabella completa non cluster con più indici filtrati senza aumentare in modo significativo i requisiti di archiviazione.

Considerazioni sulla progettazione

Per progettare indici filtrati efficaci, è importante comprendere quali sono le query utilizzate dall'applicazione e il modo in cui sono correlate ai subset dei dati. Alcuni esempi di dati che dispongono di subset ben definiti sono costituiti da colonne con la maggior parte di valori NULL, colonne con categorie eterogenee di valori e colonne con intervalli di valori distinti. Nelle considerazioni sulla progettazione seguenti viene indicata una varietà di scenari in cui un indice filtrato può fornire vantaggi rispetto agli indici di tabella completa.

Indici filtrati per subset di dati

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).

Indici filtrati per dati eterogenei

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

Confronto tra viste e indici filtrati

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.

Funzionalità consentita nelle espressioni	Viste	Indici filtrati
Colonne calcolate	Sì	No
Join	Sì	No
Più tabelle	Sì	No
Logica di confronto semplice in un predicato*	Sì	Sì
Logica complessa in un predicato**	Sì	No

*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

Viste indicizzate e indici filtrati

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

Colonne chiave

È 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.

Operatori di conversione dei dati nel predicato del filtro

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.

Dipendenze di riferimento

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.

Utilizzo degli indici filtrati

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.

Supporto per la caratteristica di indice filtrato

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.

Considerazioni sulle query

Query Optimizer può utilizzare un indice filtrato se la query seleziona gli stessi risultati indipendentemente dall'utilizzo dell'indice filtrato. L'indice filtrato FIBillOfMaterialsWithEndDate descritto in precedenza è valido per le due query seguenti. Nel primo esempio il predicato della query costituisce una corrispondenza esatta del predicato dell'indice filtrato, ovvero WHERE EndDate IS NOT NULL. Nel secondo esempio il predicato della query è più selettivo rispetto al predicato del filtro poiché contiene un subset di righe dell'indice.

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

L'indice FIBillOfMaterialsWithEndDate può essere utilizzato anche nella query seguente. Query Optimizer tuttavia potrebbe non selezionare l'indice filtrato a causa di altri fattori che determinano il costo della query, ad esempio la selettività del predicato della query. Per fare in modo che Query Optimizer scelga l'indice filtrato, è possibile utilizzare quest'ultimo come hint per la query, come illustrato nell'esempio seguente.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IN ('20040825', '20040908', '20040918');
GO

Query Optimizer non utilizzerà un indice filtrato se la query può restituire righe non presenti nell'indice filtrato. Query Optimizer non considererà ad esempio l'indice FIBillOfMaterialsWithEndDate per la query seguente perché la query potrebbe restituire una riga con un valore EndDate NULL e un valore ModifiedDate non NULL. Tale riga non può essere presente in FIBillOfMaterialsWithEndDate poiché contiene solo valori non NULL per EndDate.

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

Se un indice filtrato che potrebbe non contenere tutti i risultati della query viene utilizzato in modo esplicito come un hint di tabella, Query Optimizer genera l'errore di compilazione della query 8622. Nell'esempio seguente Query Optimizer genera l'errore 8622 perché FIBillOfMaterialsWithEndDate non è valido per la query e viene utilizzato in modo esplicito come un hint per l'indice:

SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

Query con parametri

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

Nella maggior parte dei casi Query Optimizer non esegue la parametrizzazione semplice, definita "parametrizzazione automatica" in SQL Server 2005, in una query se il piano di query include un indice filtrato. Poiché l'esecuzione della parametrizzazione semplice in tali query può ampliare l'intervallo dei valori possibili per i parametri, l'indice filtrato non potrà garantire l'accuratezza dei risultati della query. Query Optimizer potrebbe non eseguire la parametrizzazione semplice se la clausola WHERE dell'istruzione SELECT utilizza una colonna utilizzata nel predicato di un indice filtrato, in quanto è probabile che il piano di query includa un indice filtrato.

Nei casi appropriati potrebbe essere possibile eseguire la parametrizzazione della query semplicemente riscrivendola, utilizzando le linee guida descritte in questa sezione per garantire che l'indice filtrato fornisca la copertura totale della query.

Query con ricerche chiave

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.

Vedere anche

Riferimento

CREATE INDEX (Transact-SQL)

Concetti

Utilizzo di statistiche per migliorare le prestazioni di esecuzione delle query