Creare indici filtratiCreate Filtered Indexes

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In questo argomento si descrive come creare un indice filtrato in SQL Server 2017SQL Server 2017 tramite SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL.This topic describes how to create a filtered index in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-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.A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. Un indice di questo tipo utilizza un predicato del filtro per indicizzare una parte di righe nella tabella.It uses a filter predicate to index a portion of rows in the table. 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.A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Rispetto agli indici di tabella completa, gli indici filtrati consentono di ottenere i vantaggi seguenti:Filtered indexes can provide the following advantages over full-table indexes:

  • Prestazioni di esecuzione delle query e qualità del piano migliorateImproved query performance and plan quality

    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.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Queste ultime sono più accurate delle statistiche di tabella completa poiché coprono solo le righe nell'indice filtrato.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Costi di manutenzione dell'indice ridottiReduced index maintenance costs

    La manutenzione di un indice viene eseguita solo quando le istruzioni DML (Data Manipulation Language) influiscono sui dati relativi all'indice.An index is maintained only when data manipulation language (DML) statements affect the data in the index. 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.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. È possibile disporre di un numero elevato di indici filtrati, soprattutto quando in essi sono contenuti dati modificati raramente.It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. 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.Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • Costi di archiviazione dell'indice ridottiReduced index storage costs

    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.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. È possibile sostituire un indice non cluster di tabella completa con più indici filtrati senza aumentare in modo significativo i requisiti di archiviazione.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

    Contenuto dell'argomentoIn This Topic

  • Prima di iniziare:Before you begin:

    Considerazioni sulla progettazioneDesign Considerations

    Limitazioni e restrizioniLimitations and Restrictions

    SicurezzaSecurity

  • Per creare un indice filtrato tramite:To create a filtered index, using:

    SQL Server Management StudioSQL Server Management Studio

    Transact-SQLTransact-SQL

Prima di iniziare Before You Begin

Considerazioni sulla progettazione Design Considerations

  • Quando una colonna dispone solo di un numero ridotto di valori rilevanti per le query, è possibile creare un indice filtrato sul subset di valori.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. 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.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. 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.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • Se in una tabella sono presenti righe di dati eterogenei, è possibile creare un indice filtrato per una o più categorie di dati.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. 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.This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. L'indice risultante sarà di nuovo più piccolo e sarà possibile gestirlo con costi ridotti rispetto a un indice non cluster di tabella completa.Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

Limitazioni e restrizioni Limitations and Restrictions

  • Non è possibile creare un indice filtrato in una vista.You cannot create a filtered index on a view. Con Query Optimizer è tuttavia possibile sfruttare i vantaggi offerti da un indice filtrato definito in una tabella a cui si fa riferimento in una vistaHowever, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. 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.The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • Rispetto alle viste indicizzate gli indici filtrati offrono i vantaggi seguenti:Filtered indexes have the following advantages over indexed views:

    • Costi di manutenzione dell'indice ridotti.Reduced index maintenance costs. Query Processor, ad esempio, utilizza una quantità inferiore di risorse della CPU per aggiornare un indice filtrato rispetto a una vista indicizzata.For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • Qualità del piano migliorata.Improved plan quality. Durante la compilazione della query, ad esempio, Query Optimizer preferisce in molte situazioni utilizzare l'indice filtrato anziché la vista indicizzata equivalente.For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • L'indice online viene ricompilato.Online index rebuilds. È possibile ricompilare gli indici filtrati mentre sono disponibili per le query.You can rebuild filtered indexes while they are available for queries. La ricompilazione dell'indice online non è supportata per le viste indicizzate.Online index rebuilds are not supported for indexed views. Per altre informazioni, vedere l'opzione REBUILD per ALTER INDEX (Transact-SQL).For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • Indici non univoci.Non-unique indexes. Gli indici filtrati possono essere non univoci, mentre le viste indicizzate devono essere univoche.Filtered indexes can be non-unique, whereas indexed views must be unique.

  • Gli indici filtrati sono definiti in una tabella e supportano solo operatori di confronto semplici.Filtered indexes are defined on one table and only support simple comparison operators. 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.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

  • 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.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.

  • 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.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

  • 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.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

  • Non è necessario che la chiave di indice cluster della tabella sia una colonna chiave o inclusa nella definizione dell'indice filtratoThe clustered index key of the table does not need to be a key or included column in the filtered index definition. poiché viene inclusa automaticamente in tutti gli indici non cluster, inclusi quelli filtrati.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • 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.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. 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.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

  • Rivedere le opzioni SET necessarie per la creazione dell'indice filtrato nella sintassi CREATE INDEX (Transact-SQL)Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

Sicurezza Security

Autorizzazioni Permissions

È richiesta l'autorizzazione ALTER per la tabella o la vista.Requires ALTER permission on the table or view. L'utente deve essere un membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_ddladmin e db_owner .User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. Per modificare l'espressione dell'indice filtrato, utilizzare CREATE INDEX WITH DROP_EXISTING.To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

Utilizzo di SQL Server Management Studio Using SQL Server Management Studio

Per creare un indice filtratoTo create a filtered index

  1. In Esplora oggetti fare clic sul segno più per espandere il database contenente la tabella in cui si desidera creare un indice filtrato.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.

  2. Fare clic sul segno più per espandere la cartella Tabelle .Click the plus sign to expand the Tables folder.

  3. Fare clic sul segno più per espandere la tabella in cui si desidera creare un indice filtrato.Click the plus sign to expand the table on which you want to create a filtered index.

  4. Fare clic con il pulsante destro del mouse sulla cartella Indici , scegliere Nuovo indicee selezionare Indice non cluster....Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….

  5. Nella pagina Generale della finestra di dialogo Nuovo indice immettere il nome del nuovo indice nella casella Nome indice .In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. In Colonne chiave indicefare clic su Aggiungi.Under Index key columns, click Add….

  7. Nella finestra di dialogo Seleziona colonne datable_name selezionare le caselle di controllo delle colonne della tabella da aggiungere all'indice univoco.In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

  8. Scegliere OK.Click OK.

  9. In Espressione filtro della pagina Filtroimmettere l'espressione SQL che verrà utilizzata per creare l'indice filtrato.On the Filter page, under Filter Expression, enter SQL expression that you’ll use to create the filtered index.

  10. Scegliere OK.Click OK.

Utilizzo di Transact-SQL Using Transact-SQL

Per creare un indice filtratoTo create a filtered index

  1. In Esplora oggetticonnettersi a un'istanza del Motore di databaseDatabase Engine.In Object Explorer, connect to an instance of Motore di databaseDatabase Engine.

  2. Sulla barra Standard fare clic su Nuova query.On the Standard bar, click New Query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.Copy and paste the following example into the query window and click Execute.

    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.The filtered index above is valid for the following query. È possibile visualizzare il piano di esecuzione della query per determinare se in Query Optimizer è stato utilizzato l'indice filtrato.You can display the query execution plan to determine if the query optimizer used the filtered index.

    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 SQLTo ensure that a filtered index is used in a SQL query

  1. In Esplora oggetticonnettersi a un'istanza del Motore di databaseDatabase Engine.In Object Explorer, connect to an instance of Motore di databaseDatabase Engine.

  2. Sulla barra Standard fare clic su Nuova query.On the Standard bar, click New Query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.Copy and paste the following example into the query window and click Execute.

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

    Per altre informazioni, vedere CREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).