Creare indici filtrati

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure SìIstanza gestita di SQL di Azure sìAzure Synapse Analytics sìParallel Data Warehouse

Questo articolo descrive come creare un indice filtrato SQL Server Management Studio usando (SSMS) o Transact-SQL. Un indice filtrato è un indice rowstore non cluster ottimizzato basato su disco particolarmente adatto per coprire le query che selezionano da un subset di dati ben definito. Un indice di questo tipo utilizza un predicato del filtro per indicizzare una parte di righe nella tabella. Un indice filtrato ben progettato può migliorare le prestazioni delle query e ridurre i costi di manutenzione e archiviazione degli indici rispetto agli indici con tabella completa.

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

  1. Miglioramento delle prestazioni delle query e della qualità del piano.

    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.

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

  3. Riduzione dei costi di archiviazione degli indici.

    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 sulla progettazione

Quando una colonna dispone solo di un numero ridotto di valori rilevanti per le query, è possibile creare un indice filtrato sul subset 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, la clausola WHERE dell'indice filtrato deve essere un subset della clausola WHERE di una query per trarre vantaggio dall'indice filtrato.

  • Quando i valori in una colonna sono per lo più NULL e la query seleziona solo i 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 processo di flusso di lavoro o coda ricorrente. Nel corso del tempo, la maggior parte delle righe della tabella verrà contrassegnata come elaborata. Un indice filtrato per le righe non ancora elaborate potrebbe trarre 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 la guida all'architettura e alla 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 vincoli di chiave primaria o univoca, ma possono essere applicati agli indici con la proprietà UNIQUE.

Autorizzazioni

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

Creare un indice filtrato con SSMS

  1. In Esplora oggetti selezionare il segno più per espandere il database contenente la tabella in cui si vuole creare un indice filtrato.

  2. Selezionare il segno più per espandere la cartella Tabelle.

  3. Selezionare il segno più per espandere la tabella in cui si vuole creare un indice filtrato.

  4. Fare clic con il pulsante destro del mouse sulla cartella Indici, scegliere Nuovo indice e selezionare Indice non cluster.

  5. Nella pagina Generale della finestra di dialogo Nuovo indice immettere il nome del nuovo indice nella casella Nome indice .

  6. In Colonne chiave indice selezionare Aggiungi.

  7. Nella finestra di dialogo Seleziona colonne danome_tabella selezionare le caselle di controllo delle colonne della tabella da aggiungere all'indice.

  8. Selezionare OK.

  9. Nella pagina Filtro immettere l'espressione SQL che verrà usata per creare l'indice filtrato in Espressione filtro.

  10. Selezionare OK.

Creare un indice filtrato con Transact-SQL

In questo esempio viene utilizzato il AdventureWorks2019 database , disponibile per il download in Database di esempio AdventureWorks.

  1. In Esplora oggetti connettersi a un'istanza del Motore di database.

  2. Sulla barra Standard selezionare Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra della query e selezionare Esegui.

USE AdventureWorks2019;  
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 Query Optimizer ha usato l'indice filtrato.

USE AdventureWorks2019;  
GO  

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

Passaggi successivi

Per altre informazioni sulla creazione di indici e concetti correlati, vedere gli articoli seguenti: