Creare indici filtrati

In questo argomento si descrive come creare un indice filtrato in SQL Server 2012 tramite SQL Server Management Studio o Transact-SQL. Un indice filtrato è un indice non cluster ottimizzato, particolarmente indicato per coprire query per le quali i dati vengono selezionati 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 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.

Contenuto dell'argomento

• Prima di iniziare:

  Considerazioni sulla progettazione

  Limitazioni e restrizioni

  Sicurezza

• Per creare un indice filtrato tramite:

  SQL Server Management Studio

  Transact-SQL

Prima di iniziare

Considerazioni sulla progettazione

• Quando in una colonna è disponibile solo un numero ridotto di valori rilevanti per le query, è possibile creare un indice filtrato in base al 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à più piccolo e sarà possibile gestirlo con costi ridotti rispetto a un indice non cluster di tabella completa definito nelle stesse colonne chiave.

• Se in una tabella sono presenti righe di dati eterogenei, è 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.

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

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

• Non è necessario che una colonna nell'espressione dell'indice filtrato sia una colonna chiave o inclusa nella definizione dell'indice stesso se questa espressione è equivalente al predicato della query e tramite quest'ultima non viene restituita la colonna in questa espressione con i risultati della query.

• Una colonna nell'espressione dell'indice filtrato deve essere una colonna chiave o inclusa nella definizione dell'indice se tramite il predicato della query questa colonna viene utilizzata in un confronto non equivalente all'espressione dell'indice filtrato.

• Una colonna nell'espressione dell'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.

• Se l'operatore di confronto specificato nell'espressione dell'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 dell'indice filtrato con l'operatore di conversione dei dati (CAST o CONVERT) sul lato destro dell'operatore di confronto.

Sicurezza

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 dell'indice filtrato, utilizzare CREATE INDEX WITH DROP_EXISTING.

[Inizio pagina]

Utilizzo di SQL Server Management Studio

Per creare un indice filtrato

1. In Esplora oggetti fare clic sul segno più per espandere il database contenente la tabella in cui si desidera creare un indice filtrato.

2. Fare clic sul segno più per espandere la cartella Tabelle.

3. Fare clic sul segno più per espandere la tabella in cui si desidera 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 fare clic su Aggiungi.

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

8. Scegliere OK.

9. In Espressione filtro della pagina Filtro immettere l'espressione SQL che verrà utilizzata per creare l'indice filtrato.

10. Scegliere OK.

[Inizio pagina]

Utilizzo di Transact-SQL

Per creare un indice filtrato

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

2. Sulla barra Standard fare clic su Nuova query.

3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.

   USE AdventureWorks2012;
   GO
   -- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"
   -- and deletes it from the table Production.BillOfMaterials if found. 
   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
   -- Creates a filtered index "FIBillOfMaterialsWithEndDate"
   -- on the table Production.BillOfMaterials 
   -- using the columms ComponentID and StartDate.
   
   CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
       ON Production.BillOfMaterials (ComponentID, StartDate)
       WHERE EndDate IS NOT NULL ;
   GO
   

   L'indice filtrato riportato in precedenza è 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 AdventureWorks2012;
   GO
   SELECT ProductAssemblyID, ComponentID, StartDate 
   FROM Production.BillOfMaterials
   WHERE EndDate IS NOT NULL 
       AND ComponentID = 5 
       AND StartDate > '01/01/2008' ;
   GO
   

Per assicurarsi che un indice filtrato venga utilizzato in una query SQL

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

2. Sulla barra Standard fare clic su Nuova query.

3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.

   USE AdventureWorks2012;
   GO
   SELECT ComponentID, StartDate FROM Production.BillOfMaterials
       WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) ) 
   WHERE EndDate IN ('20000825', '20000908', '20000918'); 
   GO
   

Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

[Inizio pagina]