Creare indici filtrati

Articolo
03/18/2024

Si applica a:SQL ServerDatabase SQL di Azure Istanza gestita di SQL di Azure

Questo articolo descrive come creare un indice filtrato usando SQL Server Management Studio (SSMS) o Transact-SQL. Un indice filtrato è un indice non cluster rowstore basato su disco ottimizzato, particolarmente indicato per coprire query per le quali i dati vengono selezionati da un sottoinsieme 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 delle query e di ridurre i costi di gestione 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 gestione rispetto a un indice non cluster di tabella completa poiché è caratterizzato da dimensioni minori e viene gestito solo se i dati relativi vengono modificati. È possibile disporre di un numero elevato di indici filtrati, soprattutto quando in essi sono contenuti dati modificati raramente. In modo analogo, se in un indice filtrato sono contenuti solo i dati modificati 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 non cluster di tabella completa con più indici filtrati senza aumentare in modo significativo i requisiti di archiviazione.

Considerazioni relative alla progettazione

Quando una colonna dispone solo di un numero ridotto di valori rilevanti per le query, è possibile creare un indice filtrato sul sottoinsieme di valori. 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.

Si consideri, ad esempio, un indice filtrato negli scenari di dati seguenti. In ogni caso, per trarre vantaggio dall'indice filtrato la clausola WHERE della query deve essere un sottoinsieme della clausola WHERE dell'indice filtrato.

Quando i valori di una colonna sono principalmente NULL e la query seleziona solo dai valori non NULL. È possibile creare un indice filtrato per le righe di dati non NULL.
Quando le righe di una tabella vengono contrassegnate come elaborate da un flusso di lavoro o da un processo di coda ricorrente. Nel corso del tempo, la maggior parte delle righe nella tabella verrà contrassegnata come elaborata. Un indice filtrato per le righe non ancora elaborate trarrà vantaggio dalla query ricorrente che cerca le righe non ancora elaborate.
Quando una tabella contiene righe di dati eterogenee. È possibile creare un indice filtrato per una o più categorie di dati. In questo modo è possibile migliorare le prestazioni delle query in queste righe di dati restringendo lo stato attivo di una query a un'area specifica della tabella. L'indice risultante sarà di nuovo più piccolo e sarà possibile gestirlo con costi ridotti rispetto a un indice non cluster di tabella completa.

Limitazioni e restrizioni

Non è possibile creare un indice filtrato in una vista. Con Query Optimizer è tuttavia possibile sfruttare i vantaggi offerti da un indice filtrato definito in una tabella a cui si fa riferimento in una vista e, se i risultati della query saranno corretti, viene considerato un indice filtrato per una query per la quale la selezione viene effettuata da una vista.
Non è possibile creare un indice filtrato su una tabella quando la colonna a cui si accede nell'espressione di filtro è di un tipo di dati CLR.
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 altre 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.
Gli indici filtrati sono definiti in una tabella e supportano solo operatori di confronto semplici. Se è necessaria un'espressione di filtro in cui viene fatto riferimento a più tabelle o in cui è presente della logica complessa, è necessario creare una vista. Gli indici filtrati non supportano gli operatori LIKE.
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.
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.
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.
Non è necessario che la chiave di indice cluster 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. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici.
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.
Rivedere le opzioni SET necessarie per la creazione dell'indice filtrato nella sintassi CREATE INDEX (Transact-SQL)
I filtri non possono essere applicati a chiavi primarie o vincoli univoci, ma possono essere applicati agli indici con la proprietà UNIQUE.
Non è possibile creare un indice filtrato su una colonna calcolata.

Autorizzazioni

È richiesta l'autorizzazione ALTER per la tabella o la vista. L'utente deve essere un membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_ddladmin e db_owner. Per modificare l'espressione di indice filtrata, usare CREATE INDEX WITH DROP_EXISTING.

Creare un indice filtrato con SSMS.

In Esplora oggetti fare clic sul segno più per espandere il database contenente la tabella in cui si desidera creare un indice filtrato.
Fare clic sul segno più per espandere la cartella Tabelle.
Fare clic sul segno più per espandere la tabella in cui si desidera creare un indice filtrato.
Fare clic con il pulsante destro del mouse sulla cartella Indici, scegliere Nuovo indicee selezionare Indice non cluster.
Nella pagina Generale della finestra di dialogo Nuovo indice immettere il nome del nuovo indice nella casella Nome indice .
In Colonne chiave indice fare clic su Aggiungi.
Nella finestra di dialogo Seleziona colonne datable_name selezionare le caselle di controllo delle colonne della tabella da aggiungere all'indice.
Seleziona OK.
Nella pagina Filtro immettere l'espressione SQL che verrà usata per creare l'indice filtrato in Espressione filtro.
Seleziona OK.

Creare un indice filtrato con Transact-SQL

Questo articolo richiede il database di esempio AdventureWorks2022, che può essere scaricato dalla pagina iniziale relativa a progetti della community ed esempi per Microsoft SQL Server.

In Esplora oggetti connettersi a un'istanza del motore di database.
Sulla barra Standard selezionare Nuova query.
Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS 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 in Query Optimizer è stato utilizzato l'indice filtrato.

USE AdventureWorks2022;
GO

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

Passaggi successivi

Altre informazioni sulla creazione di indici e sui concetti correlati sono disponibili negli articoli seguenti: