Guida per la progettazione di indici di SQL ServerSQL Server Index Design Guide

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzureSìAzure SQL Data Warehouse Sì Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Gli indici progettati in modo non corretto e la mancanza di indici costituiscono le cause principali dei colli di bottiglia delle applicazioni di database.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. La progettazione di indici efficienti è fondamentale per ottenere buone prestazioni del database e dell'applicazione.Designing efficient indexes is paramount to achieving good database and application performance. In questa guida per la progettazione di indici di SQL Server sono contenute informazioni e procedure consigliate che consentono di progettare indici validi per soddisfare le esigenze dell'applicazione.This SQL Server index design guide contains information and best practices to help you design effective indexes to meet the needs of your application.

In questa guida si presuppone che il lettore conosca i tipi di indice disponibili in SQL ServerSQL Server.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. Per una descrizione generale dei tipi di indice, vedere Tipi di indice.For a general description of index types, see Index Types.

Nozioni fondamentali sulla progettazione di indiciIndex Design Basics

Un indice è una struttura su disco associata a una tabella o a una vista che consente di recuperare in modo rapido le righe della tabella o della vista.An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. L'indice contiene chiavi costituite da una o più colonne della tabella o della vista.An index contains keys built from one or more columns in the table or view. Queste chiavi vengono archiviate in una struttura (albero B) che consente a SQL Server di individuare con rapidità ed efficienza la riga o le righe associate ai valori di chiave.These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

La selezione degli indici adatti a un database e al relativo carico di lavoro è un'operazione complessa che comporta la ricerca di un equilibrio tra velocità delle query e costi di aggiornamento.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Gli indici limitati, ovvero con poche colonne nella chiave di indice, richiedono meno spazio su disco e overhead di gestione.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. Gli indici estesi, d'altra parte, coprono più query.Wide indexes, on the other hand, cover more queries. Potrebbe essere necessario sperimentare diverse soluzioni prima di trovare l'indice più efficiente.You may have to experiment with several different designs before finding the most efficient index. È possibile aggiungere, modificare ed eliminare indici senza modificare lo schema del database o la struttura dell'applicazione.Indexes can be added, modified, and dropped without affecting the database schema or application design. È pertanto opportuno sperimentare il funzionamento di vari tipi di indice.Therefore, you should not hesitate to experiment with different indexes.

Query Optimizer in SQL ServerSQL Server consente di scegliere in modo affidabile l'indice più efficace nella maggior parte dei casi.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. La strategia globale di progettazione dell'indice deve offrire a Query Optimizer un'ampia gamma di indici tra cui scegliere e fare affidamento su questo strumento per la scelta.Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. In questo modo, è possibile ridurre i tempi di analisi e garantire buone prestazioni in numerose situazioni.This reduces analysis time and produces good performance over a variety of situations. Per visualizzare gli indici usati da Query Optimizer per una query specifica, in SQL Server Management StudioSQL Server Management Studioscegliere Includi piano di esecuzione effettivo dal menu Query.To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

L'utilizzo di indici non consente necessariamente di ottenere prestazioni ottimali e prestazioni ottimali non sempre sono da mettere in relazione all'utilizzo di indici.Do not always equate index usage with good performance, and good performance with efficient index use. Se l'utilizzo di un indice garantisse sempre le prestazioni migliori, il processo di Query Optimizer risulterebbe semplice.If using an index always helped produce the best performance, the job of the query optimizer would be simple. In realtà, una scelta non corretta di un indice può portare a prestazioni per niente ottimali.In reality, an incorrect index choice can cause less than optimal performance. L'attività di Query Optimizer consiste pertanto nel selezionare un indice, o una combinazione di indici, solo quando questo comporta un miglioramento delle prestazioni e nell'evitare il recupero indicizzato quando ciò potrebbe avere conseguenze negative sulle prestazioni.Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

Attività di progettazione di indiciIndex Design Tasks

Le attività seguenti costituiscono la strategia consigliata per la progettazione di indici:The follow tasks make up our recommended strategy for designing indexes:

  1. Comprendere le caratteristiche del database.Understand the characteristics of the database itself. Stabilire, ad esempio, se si tratta di un database OLTP (Online Transaction Processing) in cui avvengono frequenti modifiche dei dati o di un database DSS (Decision Support System) o di data warehouse (OLAP) contenente principalmente dati di sola lettura e tramite cui è necessario elaborare rapidamente set di dati di grandi dimensioni.For example, is it an online transaction processing (OLTP) database with frequent data modifications, or a Decision Support System (DSS) or data warehousing (OLAP) database that contains primarily read-only data and must process very large data sets quickly. In SQL Server 2012SQL Server 2012l'indice columnstore con ottimizzazione per la memoria di xVelocity è particolarmente appropriato per set di dati di data warehousing tipici.In SQL Server 2012SQL Server 2012, xVelocity memory optimized columnstore index is especially appropriate for typical data warehousing data sets. Gli indici columnstore possono trasformare l'ambiente di data warehousing per gli utenti consentendo prestazioni più veloci per le query di data warehousing comuni quali quelle di filtro, aggregazione, raggruppamento e join a stella.Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. Per altre informazioni, vedere Guida sugli indici columnstore.For more information, see Columnstore Indexes Guide.

  2. Comprendere le caratteristiche delle query utilizzate più di frequente.Understand the characteristics of the most frequently used queries. Se, ad esempio, si stabilisce che una query utilizzata di frequente unisce in join due o più tabelle, è possibile determinare il tipo più adatto di indici da utilizzare.For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. Comprendere le caratteristiche delle colonne utilizzate nelle query.Understand the characteristics of the columns used in the queries. Un indice è ad esempio ideale per colonne con tipo di dati integer e univoche o non Null.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. Per colonne con subset di dati ben definiti, è possibile utilizzare un indice filtrato in SQL Server 2008SQL Server 2008 e versioni successive.For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. Per altre informazioni, vedere Linee guida per la progettazione di indici filtrati in questa guida.For more information, see Filtered Index Design Guidelines in this guide.

  4. Determinare quali opzioni dell'indice potrebbero migliorare le prestazioni in fase di creazione o manutenzione dell'indice.Determine which index options might enhance performance when the index is created or maintained. Per la creazione, ad esempio, di un indice cluster in una tabella esistente di grandi dimensioni può essere utile utilizzare l'opzione ONLINE.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. Tale opzione consente l'esecuzione di attività simultanee sui dati sottostanti durante la creazione o la ricompilazione dell'indice.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. Per altre informazioni vedere Impostare le opzioni di indice.For more information, see Set Index Options.

  5. Determinare il percorso di archiviazione ottimale per l'indice.Determine the optimal storage location for the index. Un indice non cluster può essere archiviato nello stesso filegroup della tabella sottostante oppure in un filegroup diverso.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. Il percorso di archiviazione degli indici può consentire di migliorare le prestazioni di esecuzione delle query grazie a un aumento delle prestazioni di I/O su disco.The storage location of indexes can improve query performance by increasing disk I/O performance. L'archiviazione, ad esempio, di un indice non cluster in un filegroup in un disco diverso rispetto al filegroup della tabella può consentire di migliorare le prestazioni in quanto è possibile leggere più dischi contemporaneamente.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.

    In alternativa, per gli indici cluster e non cluster è possibile utilizzare uno schema di partizione in più filegroup.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. Il partizionamento semplifica la gestione di tabelle o indici di grandi dimensioni in quanto consente di gestire o accedere a subset di dati in modo rapido ed efficace mantenendo l'integrità della raccolta.Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. Per altre informazioni, vedere Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes. Quando si considera il partizionamento, determinare se è necessario che l'indice sia allineato, ovvero partizionato nello stesso modo della tabella, o partizionato in modo indipendente.When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

Linee guida generali per la progettazione di indiciGeneral Index Design Guidelines

Gli amministratori di database esperti in genere sono in grado di progettare un set di indici adeguato, ma questa attività è molto complessa, richiede tempo ed è soggetta ad errori anche nel caso di database e carichi di lavoro di media complessità.Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. Conoscere le caratteristiche del database, delle query e delle colonne di dati può aiutare a progettare indici ottimali.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

Considerazioni sui databaseDatabase Considerations

Quando si progetta un indice è consigliabile attenersi alle linee guida seguenti sui database:When you design an index, consider the following database guidelines:

  • Un numero elevato di indici in una tabella ha ripercussioni sulle prestazioni delle istruzioni INSERT, UPDATE, DELETE e MERGE perché, quando vengono modificati i dati nella tabella, tutti gli indici devono essere modificati di conseguenza.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Ad esempio, se una colonna viene utilizzata in molti indici e si esegue un'istruzione UPDATE tramite cui i dati della colonna vengono modificati, ogni indice contenente la colonna in questione deve essere aggiornato, nonché la colonna nella tabella di base sottostante (heap o indice cluster).For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • Evitare di creare un numero eccessivo di indici in tabelle che vengono aggiornate spesso e mantenere indici di piccole dimensioni, vale a dire con il minor numero possibile di colonne.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • Utilizzare molti indici per migliorare le prestazioni delle query nelle tabelle che vengono aggiornate raramente ma che contengono grandi volumi di dati.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. Un numero elevato di indici può contribuire a migliorare le prestazioni delle query che non modificano i dati, ad esempio delle istruzioni SELECT, perché Query Optimizer può scegliere fra un numero maggiore di indici per determinare il metodo di accesso più rapido.Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • L'indicizzazione di tabelle di piccole dimensioni può non risultare ottimale, perché Query Optimizer impiega più tempo per scorrere l'indice cercando i dati che per eseguire una semplice scansione della tabella.Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Pertanto, può accadere che gli indici delle tabelle di piccole dimensioni non vengano mai utilizzati, ma devono comunque essere gestiti in caso di modifica dei dati della tabella.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • Gli indici nelle viste possono garantire miglioramenti significativi delle prestazioni quando la vista contiene aggregazioni, join di tabella o una combinazione di aggregazioni e join.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. Non è necessario che venga fatto riferimento esplicito alla vista nella query affinché Query Optimizer la utilizzi.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • Utilizzare Ottimizzazione guidata motore di database per analizzare il database e raccogliere informazioni per gli indici.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. Per altre informazioni, vedere Database Engine Tuning Advisor.For more information, see Database Engine Tuning Advisor.

Considerazioni sulle queryQuery Considerations

Quando si progetta un indice è consigliabile attenersi alle linee guida seguenti sulle query:When you design an index, consider the following query guidelines:

  • Creare indici non cluster nelle colonne che vengono utilizzate spesso in predicati e condizioni di join nelle query.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. Tuttavia, è consigliabile non aggiungere colonne non necessarie.However, you should avoid adding unnecessary columns. L'aggiunta di un numero eccessivo di colonne di indice può avere effetti negativi sullo spazio su disco e sulle prestazioni per la gestione degli indici.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • Gli indici di copertura possono migliorare le prestazioni delle query, perché tutti i dati necessari per soddisfare i requisiti della query esistono all'interno dell'indice stesso.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. In altre parole, per recuperare i dati richiesti sono necessarie solo le pagine di indice, e non le pagine di dati della tabella o dell'indice cluster. Viene dunque ridotto l'I/O complessivo del disco.That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. Una query di colonne a e b in una tabella con un indice composto creato nelle colonne a, be c può recuperare i dati specificati dall'indice solo.For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

  • Scrivere query che inseriscono o modificano il numero più alto possibile di righe con una sola istruzione, anziché utilizzare più query per aggiornare le stesse righe.Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. L'utilizzo di una sola istruzione consente di avvalersi della gestione ottimizzata degli indici.By using only one statement, optimized index maintenance could be exploited.

  • Valutare il tipo di query e la modalità di utilizzo delle colonne nella query.Evaluate the query type and how columns are used in the query. Una colonna utilizzata in un tipo di query di corrispondenze esatte, ad esempio, potrebbe essere valida per un indice non cluster o cluster.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

Considerazioni sulle colonneColumn Considerations

Quando si progetta un indice è consigliabile attenersi alle linee guidata seguenti:When you design an index consider the following column guidelines:

  • Mantenere corta la chiave dell'indice negli indici cluster.Keep the length of the index key short for clustered indexes. Inoltre, è consigliabile creare gli indici cluster su colonne univoche o non Null.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • Le colonne dei tipi di dati ntext, text, image, varchar(max), nvarchar(max)e varbinary(max) non possono essere specificate come colonne chiave di indice.Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. Tuttavia, i tipi di dati varchar(max), nvarchar(max), varbinary(max)e xml possono essere usati in un indice non cluster come colonne di indice non chiave.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. Per altre informazioni, vedere la sezione " Indice con colonne incluse" in questa guida.For more information, see the section 'Index with Included Columns' in this guide.

  • Un tipo di dati xml può essere solo una colonna chiave esclusivamente in un indice XML.An xml data type can only be a key column only in an XML index. Per altre informazioni, vedere Indici XML (SQL Server).For more information, see XML Indexes (SQL Server). In SQL Server 2012 SP1 viene introdotto un nuovo tipo di indice XML noto come indice XML selettivo.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. Grazie al nuovo indice potranno essere migliorate le prestazioni di esecuzione delle query sui dati archiviati come XML in SQL Server, pertanto sarà possibile un'indicizzazione molto più rapida di carichi di lavoro di dati XML di grandi dimensioni, nonché un miglioramento della scalabilità riducendo i costi di archiviazione dell'indice stesso.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. Per altre informazioni vedere Indici XML selettivi (SXI).For more information, see Selective XML Indexes (SXI).

  • Esaminare l'univocità delle colonne.Examine column uniqueness. Un indice univoco al posto di un indice non univoco nella stessa combinazione di colonne fornisce informazioni aggiuntive per Query Optimizer, che rendono l'indice più utile.A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. Per altre informazioni, vedere Linee guida per la progettazione di indici univoci in questa guida.For more information, see Unique Index Design Guidelines in this guide.

  • Esaminare la distribuzione dei dati nelle colonne indicizzate.Examine data distribution in the column. Spesso l'esecuzione prolungata di una query deriva dall'indicizzazione di una colonna con pochi valori univoci o dall'esecuzione di un join su tale colonna.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. Questo problema fondamentale relativo ai dati e alle query in genere non può essere risolto senza identificare questa situazione specifica.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. Ad esempio, in un elenco telefonico ordinato alfabeticamente in base al cognome non sarà possibile velocizzare la ricerca se i nomi di tutti gli abitanti della città sono Bianchi o Rossi.For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. Per altre informazioni sulla distribuzione dei dati, vedere Statistiche.For more information about data distribution, see Statistics.

  • È consigliabile utilizzare indici filtrati su colonne dispongono di subset ben definiti, ad esempio colonne di tipo sparse, colonne con la maggior parte di valori Null, colonne con categorie di valori e colonne con intervalli di valori distinti.Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. Un indice filtrato progettato correttamente consente di migliorare le prestazioni di esecuzione delle query e di ridurre i costi di archiviazione e di manutenzione dell'indice.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • Esaminare l'ordine delle colonne se l'indice conterrà più colonne.Consider the order of the columns if the index will contain multiple columns. La colonna che viene utilizzata nella clausola WHERE in una condizione di ricerca uguale a (=), maggiore di (>), minore di (<) o BETWEEN oppure che partecipa a un join deve essere al primo posto.The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Le altre colonne devono essere ordinate in base alla presenza di valori distinct, vale a dire da quella con più valori distinct a quella con meno valori distinct.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    Se, ad esempio, l'indice è definito come LastName, FirstName l'indice risulterà utile se il criterio di ricerca è WHERE LastName = 'Smith' o WHERE LastName = Smith AND FirstName LIKE 'J%'.For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. Query Optimizer, tuttavia, non utilizzerebbe l'indice per una query che effettuasse la ricerca solo in base a FirstName (WHERE FirstName = 'Jane').However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • Valutare l'opportunità di indicizzare colonne calcolate.Consider indexing computed columns. Per altre informazioni, vedere Indici per le colonne calcolate.For more information, see Indexes on Computed Columns.

Caratteristiche degli indiciIndex Characteristics

Dopo avere determinato che un indice è adeguato per una query, è possibile scegliere il tipo di indice più adatto a seconda della situazione.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. Le caratteristiche degli indici sono:Index characteristics include the following:

  • Cluster e non clusterClustered versus nonclustered

  • Univoci e non univociUnique versus nonunique

  • A colonna singola e a più colonneSingle column versus multicolumn

  • In ordine crescente o decrescente per le colonne dell'indiceAscending or descending order on the columns in the index

  • Di tabella completa o filtrato per gli indici non clusterFull-table versus filtered for nonclustered indexes

    È inoltre possibile personalizzare le caratteristiche iniziali dell'archiviazione dell'indice per ottimizzarne le prestazioni o la gestione impostando un'opzione quale FILLFACTOR.You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. È inoltre possibile determinare la posizione di archiviazione dell'indice utilizzando filegroup o schemi di partizione per ottimizzare le prestazioni.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

Posizione degli indici nei filegroup o negli schemi di partizioniIndex Placement on Filegroups or Partitions Schemes

Durante lo sviluppo della strategia di progettazione degli indici, è opportuno considerare la posizione degli indici nei filegroup associati al database.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. Un'attenta selezione dello schema di filegroup o di partizione può contribuire a migliorare le prestazioni delle query.Careful selection of the filegroup or partition scheme can improve query performance.

Per impostazione predefinita, gli indici vengono archiviati nello stesso filegroup della tabella di base in cui viene creato l'indice.By default, indexes are stored in the same filegroup as the base table on which the index is created. Un indice cluster non partizionato e la tabella di base sono sempre inclusi nello stesso filegroup.A nonpartitioned clustered index and the base table always reside in the same filegroup. È tuttavia possibile eseguire una delle operazioni seguenti:However, you can do the following:

  • Creare indici non cluster in un filegroup diverso dal filegroup della tabella di base o un indice cluster.Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.

  • Partizionare indici cluster e non cluster tra più filegroup.Partition clustered and nonclustered indexes to span multiple filegroups.

  • Spostare una tabella da un filegroup a un altro eliminando l'indice cluster e specificando un nuovo schema di filegroup o di partizione nella clausola MOVE TO dell'istruzione DROP INDEX oppure utilizzando l'istruzione CREATE INDEX con la clausola DROP_EXISTING.Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

    La creazione dell'indice non cluster in un altro filegroup consente di migliorare le prestazioni se i filegroup utilizzano unità fisiche diverse con controller distinti.By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. In tal caso, i dati e le informazioni degli indici possono essere letti in parallelo contemporaneamente da più testine.Data and index information can then be read in parallel by the multiple disk heads. Ad esempio, se Table_A nel filegroup f1 e Index_A nel filegroup f2 vengono entrambi utilizzati dalla stessa query, è possibile che si riscontrino miglioramenti delle prestazioni, in quanto i filegroup vengono utilizzati integralmente senza contese.For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. Se invece tramite la query viene eseguita l'analisi di Table_A ma non è presente un riferimento a Index_A , verrà utilizzato solo il filegroup f1 .However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. e non si otterrà un miglioramento delle prestazioni.This creates no performance gain.

    Non potendo prevedere il tipo di accesso e tantomeno il momento in cui questo si verifica, risulta più appropriato scegliere di suddividere le tabelle e gli indici tra tutti i filegroup.Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. Poiché tutti i dati e gli indici sono suddivisi equamente in tutti i dischi, l'accesso riguarderà tutti i dischi, indipendentemente dalla modalità di accesso.This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. Questo approccio è inoltre più semplice per gli amministratori del sistema.This is also a simpler approach for system administrators.

Partizioni tra più filegroupPartitions Across Multiple Filegroups

È anche possibile scegliere di partizionare indici cluster e non cluster tra più filegroup.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. Gli indici vengono partizionati in orizzontale, ovvero per riga, in base a una funzione di partizione.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. Tale funzione definisce la modalità di mapping di ciascuna riga a un set di partizioni sulla base dei valori di colonne specifiche, dette colonne di partizionamento.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. Uno schema di partizione consente di specificare il mapping delle partizioni a un set di filegroup.A partition scheme specifies the mapping of the partitions to a set of filegroups.

Il partizionamento di un indice può offrire i vantaggi seguenti:Partitioning an index can provide the following benefits:

  • Fornire sistemi scalabili per una maggior gestibilità degli indici di grandi dimensioni.Provide scalable systems that make large indexes more manageable. I sistemi OLTP, ad esempio, possono implementare applicazioni che riconoscono le partizioni e gestiscono correttamente gli indici di grandi dimensioni.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • Rendere più rapida ed efficace l'esecuzione delle query.Make queries run faster and more efficiently. Quando le query accedono a diverse partizioni di un indice, Query Optimizer è in grado di elaborare le singole partizioni contemporaneamente e di escludere quelle non interessate dalla query.When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

    Per altre informazioni, vedere Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes.

Linee guida per la progettazione dell'ordinamento dell'indiceIndex Sort Order Design Guidelines

Quando si definiscono gli indici, è necessario valutare se la colonna chiave dell'indice deve essere archiviata in ordine crescente o decrescente.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. L'ordine crescente rappresenta l'impostazione predefinita e consente di garantire la compatibilità con le versioni precedenti di SQL ServerSQL Server.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. La sintassi delle istruzioni CREATE INDEX, CREATE TABLE e ALTER TABLE supporta le parole chiave ASC (ascending, crescente) e DESC (descending, decrescente) per singole colonne di indici e vincoli.The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

È utile specificare l'ordine di archiviazione dei valori chiave in un indice nel caso in cui le query che fanno riferimento alla tabella includono clausole ORDER BY che specificano direzioni diverse per la colonna o le colonne chiave nell'indice.Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. In questi casi, l'indice non richiede un operatore SORT nel piano della query e pertanto la query risulta più efficiente.In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. Gli acquirenti nel reparto acquisti di Adventure Works CyclesAdventure Works Cycles , ad esempio, devono valutare la qualità dei prodotti acquistati dai fornitori.For example, the buyers in the Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. Gli acquirenti sono più interessati a trovare i prodotti inviati dai fornitori con una percentuale di resi maggiore.The buyers are most interested in finding products sent by these vendors with a high rejection rate. Come illustrato nella query seguente, per recuperare i dati che soddisfano questo criterio è necessario che la colonna RejectedQty della tabella Purchasing.PurchaseOrderDetail sia disposta in ordine decrescente, ovvero dal valore più grande al più piccolo, e che la colonna ProductID sia disposta in ordine crescente, ovvero dal valore più piccolo al più grande.As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

Il piano di esecuzione seguente per questa query mostra che in Query Optimizer viene utilizzato un operatore SORT per restituire il set di risultati nell'ordine specificato dalla clausola ORDER BY.The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

IndexSort1

Se un indice viene creato con colonne chiave che corrispondono a quelle della clausola ORDER BY nella query, è possibile eliminare l'operatore SORT nel piano della query, migliorando l'efficienza della query.If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

Dopo che la query è stata eseguita di nuovo, il piano di esecuzione seguente mostra che l'operatore SORT è stato eliminato ed è stato utilizzato il nuovo indice non cluster creato.After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

InsertSort2

Il Motore di databaseDatabase Engine consente di spostarsi con la stessa efficienza in entrambe le direzioni.The Motore di databaseDatabase Engine can move equally efficiently in either direction. Un indice definito come (RejectedQty DESC, ProductID ASC) può comunque essere utilizzato per una query in cui l'ordinamento delle colonne nella clausola ORDER BY viene invertito.An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. L'indice può ad esempio essere utilizzato da una query con la clausola ORDER BY ORDER BY RejectedQty ASC, ProductID DESC .For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

È possibile specificare l'ordinamento solo per le colonne chiave.Sort order can be specified only for key columns. La vista del catalogo sys.index_columns e la funzione INDEXKEY_PROPERTY indicano se una colonna di un indice è archiviata in ordine crescente o decrescente.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

Linee guida per la progettazione di indici clusterClustered Index Design Guidelines

Gli indici cluster ordinano e archiviano le righe di dati della tabella in base ai valori di chiave.Clustered indexes sort and store the data rows in the table based on their key values. Per ogni tabella è disponibile un solo indice cluster poiché le righe di dati possono essere ordinate con un solo tipo di ordinamento.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. Con poche eccezioni, è opportuno definire un indice cluster sulla colonna o sulle colonne di tutte le tabelle che presentano le caratteristiche seguenti:With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • Possono essere utilizzate per query frequenti.Can be used for frequently used queries.

  • Garantiscono un elevato livello di univocità.Provide a high degree of uniqueness.

    Nota

    Quando si crea un vincolo PRIMARY KEY, viene automaticamente creato un indice univoco sulla colonna o sulle colonne.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. Per impostazione predefinita, tale indice è cluster. È tuttavia possibile specificare un indice non cluster durante la creazione del vincolo.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • Possono essere utilizzate in query di intervallo.Can be used in range queries.

    Se l'indice cluster non viene creato con la proprietà UNIQUE, tramite il Motore di databaseDatabase Engine viene aggiunta automaticamente una colonna uniqueifier a 4 byte alla tabella.If the clustered index is not created with the UNIQUE property, the Motore di databaseDatabase Engine automatically adds a 4-byte uniqueifier column to the table. Se necessario, tramite il Motore di databaseDatabase Engine viene aggiunto automaticamente un valore uniqueifier a una riga per rendere univoca ciascuna chiave.When it is required, the Motore di databaseDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. Questa colonna e i relativi valori sono per uso interno e non sono visualizzati o accessibili dagli utenti.This column and its values are used internally and cannot be seen or accessed by users.

Architettura dell'indice clusterClustered Index Architecture

In SQL ServerSQL Servergli indici sono organizzati in alberi B.In SQL ServerSQL Server, indexes are organized as B-trees. Ogni pagina dell'albero B di un indice viene definita nodo.Each page in an index B-tree is called an index node. Il nodo di livello superiore dell'albero B viene definito nodo radice.The top node of the B-tree is called the root node. I nodi inferiori dell'indice vengono definiti nodi foglia.The bottom nodes in the index are called the leaf nodes. I livelli dell'indice compresi tra il nodo radice e i nodi foglia sono noti come livelli intermedi.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In un indice cluster il livello foglia include le pagine di dati della tabella sottostante.In a clustered index, the leaf nodes contain the data pages of the underlying table. I nodi di livello radice e intermedio contengono pagine di indice che includono le righe dell'indice.The root and intermediate level nodes contain index pages holding index rows. Ogni riga di indice contiene un valore di chiave e un puntatore a una pagina di livello intermedio nell'albero B o a una riga di dati nel livello foglia dell'indice.Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. Le pagine di ogni livello dell'indice sono collegate in un elenco collegato doppiamente.The pages in each level of the index are linked in a doubly-linked list.

Gli indici cluster includono una riga in sys.partitions, con index_id = 1 per ogni partizione usata dall'indice.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. Per impostazione predefinita, un indice cluster include una singola partizione.By default, a clustered index has a single partition. Quando in un indice cluster sono incluse più partizioni, ogni partizione ha un albero B contenente i dati per la partizione specifica.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. Se, ad esempio, un indice cluster include quattro partizioni, vi sono quattro alberi B, una in ogni partizione.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

In base al tipo di dati nell'indice non cluster, ogni struttura dell'indice non cluster avrà una o più unità di allocazione in cui archiviare e gestire i dati per una partizione specifica.Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Ogni indice cluster conterrà almeno un'unità di allocazione IN_ROW_DATA per partizioneAt a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. e anche un'unità di allocazione LOB_DATA per partizione se contiene colonne LOB.The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Conterrà inoltre un'unità di allocazione ROW_OVERFLOW_DATA per partizione, se include colonne a lunghezza variabile che superano il limite della lunghezza di riga di 8.060.It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

Le pagine nella catena di dati e le righe incluse nelle pagine vengono ordinate in base al valore della chiave dell'indice cluster.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. Tutti gli inserimenti vengono eseguiti in corrispondenza del punto in cui il valore di chiave della riga inserita rientra nella sequenza di ordinamento tra righe esistenti.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

Nella figura seguente viene illustrata la struttura di un indice cluster in una singola partizione.This illustration shows the structure of a clustered index in a single partition.

bokind2

Considerazioni sulle queryQuery Considerations

Prima di creare indici cluster, è consigliabile conoscere la modalità di accesso ai dati.Before you create clustered indexes, understand how your data will be accessed. Utilizzare ad esempio un indice cluster per query che eseguono le operazioni seguenti:Consider using a clustered index for queries that do the following:

  • Restituiscono un intervallo di valori utilizzando operatori quali BETWEEN, >, >=, < e <=.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    Quando la riga con il primo valore viene trovata utilizzando l'indice cluster, le righe con i valori indicizzati successivi sono sempre fisicamente adiacenti.After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. Se, ad esempio, tramite una query vengono recuperati i record compresi tra un intervallo di numeri di ordini vendita, la presenza di un indice cluster nella colonna SalesOrderNumber consente di individuare rapidamente la riga contenente il numero iniziale dell'ordine di vendita e quindi di recuperare tutte le righe successive nella tabella fino al raggiungimento dell'ultimo numero dell'ordine vendita.For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • Restituiscono set di risultati di grandi dimensioni.Return large result sets.

  • Utilizzano clausole JOIN, come nel caso delle colonne chiave esterne.Use JOIN clauses; typically these are foreign key columns.

  • Utilizzano clausole ORDER BY o GROUP BY.Use ORDER BY, or GROUP BY clauses.

    L'utilizzo di un indice basato sulle colonne specificate nella clausola ORDER BY o GROUP BY consente di evitare operazioni di ordinamento dei dati in Motore di databaseDatabase Engine perché le righe sono già ordinateAn index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Motore di databaseDatabase Engine to sort the data, because the rows are already sorted. e pertanto di ottimizzare le prestazioni delle query.This improves query performance.

Considerazioni sulle colonneColumn Considerations

È in genere opportuno definire la chiave di indice cluster con il minor numero di colonne possibile.Generally, you should define the clustered index key with as few columns as possible. Utilizzare colonne che presentano o più degli attributi seguenti:Consider columns that have one or more of the following attributes:

  • Sono univoche o contengono molti valori distinti.Are unique or contain many distinct values

    Gli ID dipendente consentono ad esempio di identificare in modo univoco i dipendenti.For example, an employee ID uniquely identifies employees. Un indice cluster o un vincolo PRIMARY KEY nella colonna EmployeeID contribuisce a migliorare le prestazioni di query tramite cui viene eseguita la ricerca di informazioni sui dipendenti in base al numero ID del dipendente.A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. In alternativa, è possibile creare un indice cluster in LastName, FirstName, MiddleName perché i record relativi ai dipendenti sono in genere raggruppati e sottoposti a query in questo modo. Inoltre, la combinazione di queste colonne garantisce un elevato livello di differenziazione.Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

  • Sono caratterizzate dall'accesso in modalità sequenziale.Are accessed sequentially

    Tramite un ID prodotto, ad esempio, vengono identificati in modo univoco i prodotti inclusi nella tabella Production.Product del database AdventureWorks2012AdventureWorks2012 .For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. La definizione di un indice cluster in WHERE ProductID BETWEEN 980 and 999produce effetti positivi sulle query in cui è stata specificata una ricerca sequenziale, come nel caso di ProductID,Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. in quanto le righe vengono archiviate in base all'ordinamento definito per tale colonna chiave.This is because the rows would be stored in sorted order on that key column.

  • Definite come IDENTITY.Defined as IDENTITY.

  • Vengono utilizzate di frequente per ordinare i dati recuperati da una tabella.Used frequently to sort the data retrieved from a table.

    Può essere utile eseguire il clustering della tabella, ovvero ordinarla fisicamente, in base a tale colonna per evitare il costo di un'operazione di ordinamento ogni volta che si esegue la query sulla colonna.It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

    Gli indici cluster non sono consigliati per gli attributi seguenti:Clustered indexes are not a good choice for the following attributes:

  • Colonne che vengono modificate di frequenteColumns that undergo frequent changes

    Le modifiche frequenti determinano lo spostamento dell'intera riga in quanto Motore di databaseDatabase Engine deve mantenere i valori dei dati nell'ordine fisico.This causes in the whole row to move, because the Motore di databaseDatabase Engine must keep the data values of a row in physical order. Si tratta di una considerazione importante nel caso di sistemi che elaborano volumi elevati di transazioni in cui i dati sono in genere volatili.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • Chiavi esteseWide keys

    Le chiavi estese sono costituite da diverse colonne normali o di grandi dimensioni.Wide keys are a composite of several columns or several large-size columns. I valori di chiave dell'indice cluster vengono utilizzati come chiavi di ricerca da tutti gli indici non cluster.The key values from the clustered index are used by all nonclustered indexes as lookup keys. Gli indici non cluster definiti nella stessa tabella saranno significativamente più grandi perché le voci di indice non cluster includono la chiave di clustering, nonché le colonne chiave definite per l'indice non cluster.Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

Linee guida per la progettazione di un indice non clusterNonclustered Index Design Guidelines

Un indice non cluster contiene i valori della chiave di indice e gli indicatori di posizione delle righe che puntano al percorso di archiviazione dei dati della tabella.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. In una vista tabella o indicizzata è possibile creare più indici non cluster.You can create multiple nonclustered indexes on a table or indexed view. In genere, gli indici non cluster consentono di migliorare le prestazioni di query utilizzate di frequente non coperte da un indice cluster.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

Analogamente a quando si utilizza l'indice di un libro, Query Optimizer cerca un valore di dati eseguendo una ricerca nell'indice non cluster per trovare la posizione del valore di dati nella tabella e quindi recupera i dati direttamente da quella posizione.Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. Per questo motivo, gli indici non cluster sono la scelta ottimale per le query di corrispondenza esatta, in quanto l'indice contiene le voci che descrivono la posizione esatta nella tabella dei valori di dati cercati dalle query.This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. Per eseguire, ad esempio, una query sulla tabella HumanResources. Employee per cercare tutti i dipendenti che fanno riferimento a un responsabile specifico, tramite Query Optimizer si potrebbe utilizzare l'indice non cluster IX_Employee_ManagerID, la cui colonna chiave è ManagerID .For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. Query Optimizer consente di trovare in modo rapido tutte le voci di indice che corrispondono all'oggetto ManagerIDspecificato.The query optimizer can quickly find all entries in the index that match the specified ManagerID. Ogni voce di indice punta alla pagina e alla riga esatte nella tabella o all'indice cluster in cui è possibile trovare i dati corrispondenti.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. Dopo avere trovato tutte le voci nell'indice, Query Optimizer può passare direttamente alla pagina e alla riga esatte per recuperare i dati.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Architettura dell'indice non clusterNonclustered Index Architecture

Gli indici non cluster hanno lo stesso albero B degli indici cluster, con due differenze significative:Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • Le righe di dati della tabella sottostante vengono archiviate con ordinamento basato sulle relative chiavi non cluster.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • Il livello foglia di un indice non cluster è composto da pagine di indice invece che da pagine di dati.The leaf layer of a nonclustered index is made up of index pages instead of data pages.

    Gli indicatori di posizione delle righe nelle righe di indice non cluster sono rappresentati da un puntatore a una riga o da una chiave di indice cluster per una riga, come illustrato di seguito:The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • Se la tabella è un heap, ovvero non include un indice cluster, l'indicatore di posizione è un puntatore riferito alla rigaIf the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. e compilato in base all'ID del file, al numero della pagina e al numero della riga nella pagina.The pointer is built from the file identifier (ID), page number, and number of the row on the page. L'intero puntatore è noto come ID di riga.The whole pointer is known as a Row ID (RID).

  • Se una tabella include un indice cluster oppure l'indice è riferito a una vista indicizzata, l'indicatore di posizione delle righe corrisponde alla chiave di indice cluster per la riga.If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

    Negli indici non cluster è inclusa una riga in sys.partitions con index_id >1 per ogni partizione usata dall'indice.Nonclustered indexes have one row in sys.partitions with index_id >1 for each partition used by the index. Per impostazione predefinita, un indice non cluster include una singola partizione.By default, a nonclustered index has a single partition. Quando in un indice non cluster sono incluse più partizioni, ogni partizione ha un albero B contenente le righe di indice per la partizione specifica.When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. Se, ad esempio, un indice non cluster include quattro partizioni, vi sono quattro alberi B, una in ogni partizione.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

    In base ai tipi di dati nell'indice non cluster, ogni struttura dell'indice non cluster avrà una o più unità di allocazione in cui archiviare e gestire i dati per una partizione specifica.Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Ogni indice non cluster ha almeno un'unità di allocazione IN_ROW_DATA per partizione in cui sono archiviate le pagine relative all'albero B dell'indice.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. L'indice non cluster includerà inoltre un'unità di allocazione LOB_DATA per partizione se contiene colonne LOB.The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . L'indice include inoltre un'unità di allocazione ROW_OVERFLOW_DATA per partizione se contiene colonne a lunghezza variabile che superano le dimensioni massime di 8.060 byte.Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

    Nella figura seguente viene illustrata la struttura di un indice non cluster in una singola partizione.The following illustration shows the structure of a nonclustered index in a single partition.

bokind1a

Considerazioni sui databaseDatabase Considerations

Quando si progettano indici non cluster, considerare le caratteristiche del database.Consider the characteristics of the database when designing nonclustered indexes.

  • In caso di database o tabelle che richiedono pochi aggiornamenti ma contengono grandi volumi di dati, l'utilizzo di molti indici non cluster può consentire di migliorare le prestazioni di esecuzione delle query.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. È consigliabile creare indici filtrati per subset ben definiti di dati per ottimizzare le prestazioni relative alle query e ridurre i costi di archiviazione e di manutenzione dell'indice rispetto agli indici non cluster di tabella completa.Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    In caso di applicazioni DSS (Decision Support System) e database che contengono principalmente dati di sola lettura è consigliabile l'utilizzo di molti indici non cluster.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. In questo modo, saranno disponibili per Query Optimizer più indici tra cui scegliere per determinare il metodo di accesso più rapido e la bassa frequenza di aggiornamento del database significa che la manutenzione dell'indice non influirà negativamente sulle prestazioni.The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • In caso di applicazioni di elaborazione delle transazioni online (OLP) e database contenenti tabelle aggiornate di frequente, è consigliabile evitare di utilizzare un numero eccessivo di indici.Online Transaction Processing applications and databases that contain heavily updated tables should avoid over-indexing. È inoltre necessario che gli indici siano limitati, ovvero con il minor numero possibile di colonne.Additionally, indexes should be narrow, that is, with as few columns as possible.

    Un numero elevato di indici in una tabella ha ripercussioni sulle prestazioni delle istruzioni INSERT, UPDATE, DELETE e MERGE perché, quando vengono modificati i dati nella tabella, tutti gli indici devono essere modificati di conseguenza.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

Considerazioni sulle queryQuery Considerations

Prima di creare indici non cluster, è consigliabile analizzare la modalità di accesso ai dati.Before you create nonclustered indexes, you should understand how your data will be accessed. Utilizzare un indice non cluster per le query con gli attributi seguenti:Consider using a nonclustered index for queries that have the following attributes:

  • Query che utilizzano clausole JOIN o GROUP BY.Use JOIN or GROUP BY clauses.

    Creare più indici non cluster in colonne interessate da operazioni di join e raggruppamento e un indice cluster in ogni colonna chiave esterna.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • Query che non restituiscono set di risultati estesi.Queries that do not return large result sets.

    Creare indici filtrati per coprire query che restituiscono un subset ben definito di righe da una tabella di elevate dimensioni.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

  • Query che contengono colonne interessate di frequente da condizioni di ricerca di una query, ad esempio la clausola WHERE, che restituiscono corrispondenze esatte.Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

Considerazioni sulle colonneColumn Considerations

Considerare le colonne con uno o più degli attributi seguenti:Consider columns that have one or more of these attributes:

  • Colonne che coprono la query.Cover the query.

    È possibile ottenere un miglioramento delle prestazioni quando l'indice contiene tutte le colonne nella query.Performance gains are achieved when the index contains all columns in the query. Query Optimizer può individuare tutti i valori della colonna all'interno dell'indice. Poiché non viene effettuato l'accesso ai dati della tabella o dell'indice cluster, il numero di operazioni di I/O su disco risulta inferiore.The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Utilizzare un indice con colonne per aggiungere colonne di copertura anziché creare una chiave di indice esteso.Use index with included columns to add covering columns instead of creating a wide index key.

    Se la tabella include un indice cluster, la colonna o le colonne definite in tale indice vengono automaticamente accodate alla fine di ogni indice non cluster nella tabella.If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. In questo modo, è possibile produrre una query coperta senza specificare le colonne dell'indice cluster nella definizione dell'indice non cluster.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. Se, ad esempio, in una tabella sono inclusi un indice cluster nella colonna C, un indice non cluster nelle colonne B e A , le colonne B, Ae Cdella tabella in questione rappresenteranno i relativi valori chiave.For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • Colonne che includono un numero elevato di valori distinct, ad esempio una combinazione di cognome e nome, se per le altre colonne viene utilizzato un indice cluster.Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    Se sono presenti soltanto pochi valori distinct, ad esempio 1 e 0, la maggior parte delle query non utilizzerà l'indice in quanto una scansione della tabella risulta in genere più efficiente.If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. Per questo tipo di dati, creare un indice filtrato su un valore distinto che presente solo in un numero ridotto di righe.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. Se la maggior parte dei valori è impostata su 0, Query Optimizer potrebbe utilizzare un indice filtrato per le righe di dati che contengono il valore 1.For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

Utilizzare colonne incluse per estendere gli indici non clusterUse Included Columns to Extend Nonclustered Indexes

È possibile estendere le funzionalità degli indici non cluster aggiungendo colonne non chiave a livello foglia dell'indice non cluster.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. Con l'inclusione di colonne non chiave è possibile creare indici non cluster in grado di coprire più query.By including nonkey columns, you can create nonclustered indexes that cover more queries. Ciò è possibile perché le colonne non chiave presentano i vantaggi seguenti:This is because the nonkey columns have the following benefits:

  • Possono essere tipi di dati che non sono consentiti come colonne chiave indice.They can be data types not allowed as index key columns.

  • Non vengono prese in esame dal Motore di databaseDatabase Engine durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi di indice.They are not considered by the Motore di databaseDatabase Engine when calculating the number of index key columns or index key size.

    Un indice con colonne non chiave incluse può aumentare significativamente le prestazioni delle query quando tutte le colonne della query sono incluse nell'indice come colonne chiave o non chiave.An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. I vantaggi nelle prestazioni si ottengono poiché in Query Optimizer è possibile individuare tutti i valori delle colonne all'interno dell'indice. In questo modo, la quantità di operazioni di I/O su disco è inferiore dato che non viene eseguito alcun accesso ai dati delle tabelle o degli indici cluster.Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

Nota

Quando un indice contiene tutte le colonne a cui fa riferimento la query, viene generalmente indicato come indice di copertura.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

Mentre le colonne chiave sono archiviate a tutti i livelli dell'indice, le colonne non chiave sono archiviate solo al livello foglia.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

Utilizzo di colonne incluse per aggirare i limiti alle dimensioniUsing Included Columns to Avoid Size Limits

È possibile includere colonne non chiave in un indice non cluster per evitare il superamento delle limitazioni di dimensione correnti degli indici (numero massimo di colonne chiave pari a 16 e dimensione massima delle chiavi di indice pari a 900 byte).You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Il Motore di databaseDatabase Engine non prende in esame le colonne non chiave durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi di indice.The Motore di databaseDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

Si supponga, ad esempio, che si desideri indicizzare le colonne seguenti nella tabella Document :For example, assume that you want to index the following columns in the Document table:

Title nvarchar(50)

Revision nchar(5)

FileName nvarchar(400)

Dal momento che i tipi i dati nchar e nvarchar richiedono 2 byte per ogni carattere, un indice contenente queste tre colonne supererebbe di 10 byte il limite di 900 byte (455 x 2).Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). Utilizzando la clausola INCLUDE dell'istruzione CREATE INDEX , è possibile definire la chiave dell'indice come (Title, Revision) e FileName come colonna non chiave.By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. In questo modo, la dimensione della chiave dell'indice sarebbe pari a 110 byte (55 * 2) e l'indice conterrebbe ancora tutte le colonne necessarie.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. Nell'istruzione seguente viene creato un simile indice.The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Linee guida sull'utilizzo degli indici con colonne incluseIndex with Included Columns Guidelines

Quando si progettano indici non cluster con colonne incluse è opportuno considerare le indicazioni generali seguenti:When you design nonclustered indexes with included columns consider the following guidelines:

  • Le colonne non chiave vengono definite nella clausola INCLUDE dell'istruzione CREATE INDEX.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • Le colonne non chiave possono essere definite solo su indici non cluster su tabelle o viste indicizzate.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • È possibile usare qualsiasi tipo di dati, ad eccezione di text, ntexte image.All data types are allowed except text, ntext, and image.

  • Come colonne incluse è possibile utilizzare colonne calcolate che sono deterministiche, sia precise che imprecise.Computed columns that are deterministic and either precise or imprecise can be included columns. Per altre informazioni, vedere Indici per le colonne calcolate.For more information, see Indexes on Computed Columns.

  • In maniera simile alle colonne chiave, le colonne calcolate derivate dai tipi di dati image, ntexte text possono essere colonne non chiave (incluse) purché il tipo di dati della colonna calcolata sia consentito come colonna non chiave dell'indice.As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • Non è possibile specificare i nomi delle colonne sia nell'elenco INCLUDE che nell'elenco delle colonne chiave.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • Non è possibile ripetere i nomi delle colonne nell'elenco INCLUDE.Column names cannot be repeated in the INCLUDE list.

Linee guida sulle dimensioni delle colonneColumn Size Guidelines
  • È necessario definire almeno una colonna chiave.At least one key column must be defined. Il numero massimo di colonne non chiave è 1023.The maximum number of nonkey columns is 1023 columns. Questo limite è rappresentato dal numero massimo di colonne nelle tabelle meno 1.This is the maximum number of table columns minus 1.

  • Le colonne chiave non indice, escluse le colonne non chiave dell'indice, devono soddisfare le limitazioni di dimensione correnti degli indici (numero massimo di colonne chiave pari a 16 e dimensione totale delle chiavi di indice pari a 900 byte).Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • La dimensione totale delle colonne non chiave è limitata solo dalle dimensioni delle colonne specificate nella clausola INCLUDE; ad esempio, le colonne varchar(max) sono limitate a 2 GB.The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

Linee guida sulla modifica delle colonneColumn Modification Guidelines

Quando si modifica una colonna di tabella che è stata definita come colonna inclusa, vengono applicate le limitazioni seguenti:When you modify a table column that has been defined as an included column, the following restrictions apply:

  • Non è possibile eliminare dalla tabella le colonne non chiave se non si è prima eliminato l'indice.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • Non è possibile modificare le colonne non chiave se non per effettuare le operazioni seguenti:Nonkey columns cannot be changed, except to do the following:

    • Modifica del supporto di valori NULL della colonna da NOT NULL a NULL.Change the nullability of the column from NOT NULL to NULL.

    • Aumento della lunghezza di colonne varchar, nvarcharo varbinary .Increase the length of varchar, nvarchar, or varbinary columns.

      Nota

      Tali restrizioni sulla modifica delle colonne sono valide anche per le colonne chiave indice.These column modification restrictions also apply to index key columns.

Consigli sulla progettazioneDesign Recommendations

Progettare nuovamente gli indici non cluster con chiavi di indice dalle dimensioni elevate in modo da utilizzare come colonne chiave solo le colonne utilizzate per le ricerche.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Modificare in colonne non chiave incluse tutte le altre colonne che coprono la query.Make all other columns that cover the query included nonkey columns. In questo modo si avranno tutte le colonne necessarie per coprire la query, contenendo al tempo stesso le dimensioni della chiave dell'indice e mantenendone l'efficienza.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

Si supponga, ad esempio, che si desideri progettare un indice per coprire la query seguente.For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

Per coprire la query è necessario definire tutte le colonne nell'indice.To cover the query, each column must be defined in the index. Sebbene sia possibile definire tutte le colonne come colonne chiave, la dimensione delle chiavi sarebbe di 334 byte.Although you could define all columns as key columns, the key size would be 334 bytes. Dal momento che la sola colonna effettivamente utilizzata come criterio di ricerca è la colonna PostalCode , la quale ha una lunghezza pari a 30 byte, è possibile migliorare la progettazione degli indici definendo PostalCode come colonna chiave e includendo tutte le altre colonne come colonne non chiave.Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

Nell'istruzione seguente viene creato un indice con colonne incluse per coprire la query.The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
Considerazioni sulle prestazioniPerformance Considerations

Evitare di aggiungere colonne non necessarie.Avoid adding unnecessary columns. L'aggiunta di troppe colonne indice, chiave o non chiave, può avere le implicazioni sulle prestazioni seguenti:Adding too many index columns, key or nonkey, can have the following performance implications:

  • In una pagina rientreranno meno righe di indice.Fewer index rows will fit on a page. Ciò potrebbe causare più operazioni di I/O e ridurre l'efficienza della cache.This could create I/O increases and reduced cache efficiency.

  • Sarà necessario più spazio su disco per archiviare l'indice.More disk space will be required to store the index. In particolare, l'aggiunta di tipi di dati varchar(max), nvarchar(max), varbinary(max)o xml come colonne non chiave dell'indice potrebbe aumentare significativamente l'utilizzo di spazio su disco.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. Ciò accade perché i valori delle colonne vengono copiati nel livello foglia dell'indice,This is because the column values are copied into the index leaf level. risiedendo in tal modo sia nell'indice che nella tabella di base.Therefore, they reside in both the index and the base table.

  • La manutenzione degli indici può aumentare il tempo necessario per eseguire modifiche, inserimenti, aggiornamenti o eliminazioni nella tabella sottostante o nella vista indicizzata.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

    Sarà necessario determinare se i guadagni in termini di prestazioni delle query offrano maggiore vantaggio rispetto all'influenza sulle prestazioni durante la modifica dei dati e ai maggiori requisiti di spazio su disco.You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

Linee guida per la progettazione di indici univociUnique Index Design Guidelines

Un indice univoco consente di garantire che nella chiave dell'indice non siano contenuti valori duplicati e che pertanto ogni riga della tabella sia univoca.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. È consigliabile specificare un indice univoco solo se l'unicità è una caratteristica dei dati stessi.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. Per verificare, ad esempio, che i valori della colonna NationalIDNumber nella tabella HumanResources.Employee siano univoci quando la chiave primaria è EmployeeID, creare un vincolo UNIQUE nella colonna NationalIDNumber .For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. Se l'utente tenta di inserire nella colonna lo stesso valore per più dipendenti, verrà visualizzato un messaggio di errore e il valore duplicato non verrà inserito.If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

Per gli indici univoci a più colonne, l'indice garantisce che ogni combinazione di valori nella chiave dell'indice sia univoca.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. Ad esempio, se si crea un indice univoco basato su una combinazione delle colonne LastName, FirstNamee MiddleName , non è possibile che nella tabella siano incluse due righe in cui è presente la stessa combinazione di valori per queste colonne.For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

Sia gli indici cluster che non cluster possono essere univoci.Both clustered and nonclustered indexes can be unique. Se i dati nella colonna sono univoci, nella stessa tabella è possibile creare sia un indice cluster univoco che più indici non cluster univoci.Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

Gli indici univoci offrono i vantaggi seguenti:The benefits of unique indexes include the following:

  • Garantiscono l'integrità dei dati delle colonne definite.Data integrity of the defined columns is ensured.

  • Forniscono informazioni aggiuntive utili per Query Optimizer.Additional information helpful to the query optimizer is provided.

    Se si crea un vincolo PRIMARY KEY o UNIQUE, viene creato automaticamente un indice univoco basato sulle colonne specificate.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. Non esistono differenze significative tra la creazione determinata da un vincolo UNIQUE e la creazione di un indice univoco indipendente da un vincolo.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. La convalida dei dati viene eseguita nello stesso modo e Query Optimizer non differenzia un indice univoco creato da un vincolo da un indice creato in modo manuale.Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. Se l'obiettivo è l'integrità dei dati, è tuttavia consigliabile creare un vincolo UNIQUE o PRIMARY KEY sulla colonna,However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. in modo tale che l'obiettivo dell'indice risulti chiaro.By doing this the objective of the index will be clear.

ConsiderazioniConsiderations

  • Se nei dati sono presenti valori di chiave duplicati, non è possibile creare un indice univoco, un vincolo UNIQUE o un vincolo PRIMARY KEY.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • Se i dati sono univoci e si desidera imporre l'unicità, la creazione di un indice univoco anziché di un indice non univoco per la stessa combinazione di colonne fornirà a Query Optimizer una maggiore quantità di informazioni che consentiranno di creare piani di esecuzione più efficienti.If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. In questo caso è consigliabile creare un indice univoco, preferibilmente tramite un vincolo UNIQUE.Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • In un indice non cluster univoco possono essere contenute colonne non chiave.A unique nonclustered index can contain included nonkey columns. Per altre informazioni, vedere Indice con colonne incluse.For more information, see Index with Included Columns.

Linee guida per la progettazione di indici filtratiFiltered Index Design Guidelines

Un indice filtrato è un indice non cluster ottimizzato, particolarmente indicato per coprire query che selezionano dati 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 di esecuzione delle query e di ridurre i costi di manutenzione e di archiviazione dell'indice stesso.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

||
|-|
|Si applica a: SQL Server 2008SQL Server 2008 tramite SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.|

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 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.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 affected. È possibile disporre di un numero elevato di indici filtrati, soprattutto quando questi ultimi contengono dati interessati raramente.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. 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.Similarly, if a filtered index contains only the frequently affected 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.

    Gli indici filtrati risultano particolarmente utili quando le colonne contengono subset ben definiti di dati cui le query fanno riferimento nelle istruzioni SELECT.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Di seguito vengono indicati alcuni esempi:Examples are:

  • Colonne di tipo sparse che contengono solo un numero limitato di valori non NULL.Sparse columns that contain only a few non-NULL values.

  • Colonne eterogenee che contengono categorie di dati.Heterogeneous columns that contain categories of data.

  • Colonne che contengono intervalli di valori diversi, ad esempio quantità di denaro, ore e date.Columns that contain ranges of values such as dollar amounts, time, and dates.

  • Partizioni di tabelle definite da logica di confronto semplice per i valori di colonna.Table partitions that are defined by simple comparison logic for column values.

    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.Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. 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.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In questo caso è opportuno utilizzare un indice di tabella completa anziché un indice filtrato.In this case, you should use a full-table index instead of a filtered index.

    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.

Considerazioni sulla progettazioneDesign Considerations

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.In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. 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.Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. 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.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Indici filtrati per subset di datiFiltered Indexes for Subsets of Data

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.

Nel database AdventureWorks2012 , ad esempio, è disponibile una tabella Production.BillOfMaterials con 2679 righe.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. La colonna EndDate dispone solo di 199 righe che contengono un valore non NULL, mentre le altre 2480 righe contengono valori NULL.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain 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.The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

L'indice filtrato FIBillOfMaterialsWithEndDate è valido per la query seguente.The filtered index FIBillOfMaterialsWithEndDate 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.

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

Per altre informazioni sulla creazione di indici filtrati e sulla definizione dell'espressione del predicato dell'indice filtrato, vedere Creare indici filtrati.For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

Indici filtrati per dati eterogeneiFiltered Indexes for Heterogeneous Data

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.

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.For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. Queste categorie sono eterogenee poiché i valori di colonna relativi nella tabella Production.Product non sono strettamente correlati.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. Ad esempio, le colonne Color, ReorderPoint, ListPrice, Weight, Classe Style dispongono di caratteristiche univoche per ogni categoria di prodotti.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Se vengono eseguite query frequenti sugli accessori con sottocategorie tra 27 e 36 inclusi,Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. è possibile migliorarne le prestazioni creando un indice filtrato nella sottocategoria degli accessori come mostrato nell'esempio riportato di seguito.You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;  

L'indice filtrato FIProductAccessories si applica alla query seguente, in quanto i risultatiThe filtered index FIProductAccessories covers the following query because the query

della query sono contenuti nell'indice e il piano della query non include una ricerca nella tabella di base.results are contained in the index and the query plan does not include a base table lookup. 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.For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

SELECT Name, ProductSubcategoryID, ListPrice  
FROM Production.Product  
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;  

Colonne chiaveKey Columns

È 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.It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. Sebbene Query Optimizer possa scegliere un indice filtrato per la query indipendentemente dal fatto che la copra o meno,The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. tuttavia è più probabile che venga scelto un indice filtrato che copre la query.However, the query optimizer is more likely to choose a filtered index if it covers the query.

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.In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. 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.The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. Gli esempi si riferiscono all'indice filtrato FIBillOfMaterialsWithEndDate creato in precedenza.The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

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. 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.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate non ha bisogno di EndDate come colonna chiave o inclusa nella definizione dell'indice filtrato.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

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

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. L'indice FIBillOfMaterialsWithEndDate , ad esempio, è valido per la query seguente perché seleziona un subset di righe dall'indice filtrato.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. 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.However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. Poiché Query Processor non è in grado di eseguire questa query senza cercare i valori di EndDate,The query processor cannot execute this query without looking up the values of EndDate. Di conseguenza, EndDate deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate > '20040101';   

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. L'indice FIBillOfMaterialsWithEndDate , ad esempio, non copre la query seguente perché restituisce la colonna EndDate nei risultati della query.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Di conseguenza, EndDate deve essere una colonna chiave o inclusa nella definizione dell'indice filtrato.Therefore, EndDate should be a key or included column in the filtered index definition.

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

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.

Operatori di conversione dei dati nel predicato del filtroData Conversion Operators in the Filter Predicate

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.

Nell'esempio seguente viene creata una tabella con tipi di dati diversi.The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

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.In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. Verrà generato un messaggio di errore 10611 poiché la conversione viene eseguita sul lato sinistro dell'operatore nel predicato filtrato.This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

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:The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = CONVERT(Varbinary(4), 1);  

Lo spostamento della conversione dei dati dal lato sinistro a quello destro di un operatore di confronto potrebbe modificare il significato della conversione.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. 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 .In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

Ulteriori informazioniAdditional Reading

Miglioramento delle prestazioni con le viste indicizzate di SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views
Partitioned Tables and IndexesPartitioned Tables and Indexes