Architettura e guida per la progettazione degli indici di SQL ServerSQL Server Index Architecture and Design Guide

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. Questa guida per la progettazione degli indici di SQL ServerSQL Server contiene informazioni sull'architettura degli indici e le procedure consigliate che consentono di progettare indici validi per soddisfare le esigenze dell'applicazione.This SQL ServerSQL Server index design guide contains information on index architecture, 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.

In questa guida vengono illustrati i tipi di indici seguenti:This guide covers the following types of indexes:

  • ClusterClustered
  • Non clusterNonclustered
  • UnivocoUnique
  • FiltratoFiltered
  • columnstoreColumnstore
  • HashHash
  • Non cluster ottimizzati per la memoriaMemory-Optimized Nonclustered

Per informazioni sugli indici XML, vedere Panoramica degli indici XML.For information about XML indexes, see XML Indexes Overview.

Per informazioni sugli indici spaziali, vedere Panoramica degli indici spaziali.For information about Spatial indexes, see Spatial Indexes Overview.

Per informazioni sugli indici full-text, vedere Popolamento degli indici full-text.For information about Full-text indexes, see Populate Full-Text Indexes.

Nozioni fondamentali sulla progettazione di indiciIndex Design Basics

Un indice è una struttura su disco o memoria 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 or in-memory 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. Per gli indici su disco, 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.For on-disk indexes, 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.

Un indice archivia dati organizzati logicamente, come una tabella con righe e colonne, e archiviati fisicamente in un formato di dati a livello di riga denominato rowstore 1, o archiviati in un formato di dati a livello di colonna denominato columnstore.An index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore 1, or stored in a column-wise data format called columnstore.

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.

1 Il formato rowstore è il metodo di archiviazione tradizionale per i dati relazionali di tabella.1 Rowstore has been the traditional way to store relational table data. In SQL ServerSQL Server, rowstore fa riferimento alla tabella in cui il formato di archiviazione dati sottostante è un heap, un albero B (indice cluster) o una tabella ottimizzata per la memoria.In SQL ServerSQL Server, rowstore refers to table where the underlying data storage format is a heap, a B-tree (clustered index), or a memory-optimized table.

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.

  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 usare 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 usata 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
  • Columnstore e rowstoreColumnstore versus rowstore
  • Hash e non cluster per tabelle ottimizzate per la memoriaHash versus nonclustered for Memory-Optimized tables

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

MetadatiMetadata

Usare queste viste dei metadati per visualizzare gli attributi degli indici.Use these metadata views to see attributes of indexes. In alcune di queste viste sono incorporate altre informazioni sull'architettura.More architectural information is embedded in some of these views.

Nota

Tutte le colonne di un indice columnstore vengono archiviate nei metadati come colonne incluse.For columnstore indexes, all columns are stored in the metadata as included columns. L'indice columnstore non contiene colonne chiave.The columnstore index does not have key columns.

sys.indexes (Transact-SQL)sys.indexes (Transact-SQL) sys.index_columns (Transact-SQL)sys.index_columns (Transact-SQL)
sys.partitions (Transact-SQL)sys.partitions (Transact-SQL) sys.internal_partitions (Transact-SQL)sys.internal_partitions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)
sys.column_store_segments (Transact-SQL)sys.column_store_segments (Transact-SQL) sys.column_store_dictionaries (Transact-SQL)sys.column_store_dictionaries (Transact-SQL)
sys.column_store_row_groups (Transact-SQL)sys.column_store_row_groups (Transact-SQL) sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)
sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) sys.dm_column_store_object_pool (Transact-SQL)sys.dm_column_store_object_pool (Transact-SQL)
sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)sys.dm_db_column_store_row_group_operational_stats (Transact-SQL) sys.dm_db_xtp_hash_index_stats (Transact-SQL)sys.dm_db_xtp_hash_index_stats (Transact-SQL)
sys.dm_db_xtp_index_stats (Transact-SQL)sys.dm_db_xtp_index_stats (Transact-SQL) sys.dm_db_xtp_object_stats (Transact-SQL)sys.dm_db_xtp_object_stats (Transact-SQL)
sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL) sys.dm_db_xtp_table_memory_stats (Transact-SQL)sys.dm_db_xtp_table_memory_stats (Transact-SQL)
sys.hash_indexes (Transact-SQL)sys.hash_indexes (Transact-SQL) sys.memory_optimized_tables_internal_attributes (Transact-SQL)sys.memory_optimized_tables_internal_attributes (Transact-SQL)

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à anche 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 usando 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.

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

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

    Suggerimento

    Se non specificato diversamente, durante la creazione di un vincolo PRIMARY KEY SQL ServerSQL Servercrea un indice cluster per supportare tale vincolo.If not specified differently, when creating a PRIMARY KEY constraint, SQL ServerSQL Server creates a clustered index to support that constraint. Sebbene un uniqueidentifier possa essere usato per applicare una PRIMARY KEY di univocità, non si tratta di una chiave di clustering efficiente.Although a uniqueidentifier can be used to enforce uniqueness as a PRIMARY KEY, it is not an efficient clustering key. Se si usa un valore uniqueidentifier come PRIMARY KEY, è consigliabile crearlo come un indice non cluster e usare un'altra colonna, ad esempio IDENTITY, per creare l'indice cluster.If using a uniqueidentifier as PRIMARY KEY, the recommendation is to create it as a nonclustered index, and use another column such as an IDENTITY to create the clustered index.

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

  • Definito 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 include anche 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 anche 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:

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

Suggerimento

La definizione degli indici columnstore non cluster supporta l'uso di una condizione filtrata.The nonclustered columnstore index definition supports using a filtered condition. Per ridurre al minimo l'impatto sulle prestazioni conseguente all'aggiunta di un indice columnstore in una tabella OLTP, usare una condizione filtrata per creare un indice columnstore non cluster solo sui dati usati meno di frequente del carico di lavoro operativo.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

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.

Linee guida per la progettazione di indici columnstoreColumnstore Index Design Guidelines

L' columnstore index è una tecnologia per l'archiviazione, il recupero e la gestione dei dati utilizzando un formato di dati in colonna, detto columnstore.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Per altre informazioni, vedere Panoramica sugli indici columnstore.For more information, refer to Columnstore Indexes overview.

Per informazioni sulle versioni, vedere Indici columnstore - Novità.For version information, see Columnstore indexes - What's new.

Architettura degli indici columnstoreColumnstore Index Architecture

La conoscenza di questi concetti di base renderà più facile comprendere altri articoli dedicati agli indici columnstore che spiegano come usarli in modo efficace.Knowing these basics will make it easier to understand other columnstore articles that explain how to use them effectively.

Per l'archiviazione dei dati viene usata la compressione di columnstore e rowstoreData storage uses columnstore and rowstore compression

Quando si parla di indici columnstore, si usano i termini rowstore e columnstore per enfatizzare il formato per l'archiviazione dei dati.When discussing columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. Gli indici columnstore usano entrambi i tipi di archiviazione.Columnstore indexes use both types of storage.

Clustered Columnstore IndexClustered Columnstore Index

  • Un indice columnstore è costituito da dati organizzati logicamente in una tabella con righe e colonne e archiviati fisicamente in un formato di dati a colonne.A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

Un indice columnstore archivia fisicamente la maggior parte dei dati nel formato columnstore.A columnstore index physically stores most of the data in columnstore format. Nel formato columnstore i dati vengono compressi e decompressi come colonne.In columnstore format, the data is compressed and uncompressed as columns. Non è necessario decomprimere altri valori in ogni riga, se non sono richiesti dalla query.There is no need to uncompress other values in each row that are not requested by the query. Ciò consente di analizzare in modo veloce un'intera colonna di una tabella di grandi dimensioni.This makes it fast to scan an entire column of a large table.

  • Un indice rowstore è costituito da dati organizzati logicamente in una tabella con righe e colonne e archiviati fisicamente in un formato di dati a righe.A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. Questo è stato il metodo tradizionale per archiviare dati relazionali di tabella, come un indice heap o un indice albero B cluster.This has been the traditional way to store relational table data such as a heap or clustered B-tree index.

Un indice columnstore archivia inoltre fisicamente alcune righe in un formato rowstore, denominato archivio differenziale.A columnstore index also physically stores some rows in a rowstore format called a deltastore. L'archivio differenziale, detto anche rowgroup differenziale, è una posizione di archiviazione per le righe in numero troppo limitato per qualificarsi per la compressione nel columnstore.The deltastore,also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Ogni rowgroup differenziale viene implementato come indice albero B cluster.Each delta rowgroup is implemented as a clustered B-tree index.

  • Il deltastore è una posizione di archiviazione per le righe in numero troppo limitato per essere compresse nel columnstore.The deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. Il deltastore archivia le righe in formato rowstore.The deltastore stores the rows in rowstore format.

Le operazioni vengono eseguite sui rowgroup e sui segmenti di colonnaOperations are performed on rowgroups and column segments

L'indice columnstore raggruppa le righe in unità gestibili.The columnstore index groups rows into manageable units. Ognuna di queste unità viene chiamata rowgroup.Each of these units is called a rowgroup. Per ottenere prestazioni ottimali, il numero di righe nel rowgroup deve essere sufficientemente grande da migliorare il tasso di compressione e sufficientemente ridotto da poter trarre vantaggio dall'esecuzione delle operazioni in memoria.For best performance, the number of rows in a rowgroup is large enough to improve compression rates and small enough to benefit from in-memory operations.

  • Un rowgroup è un gruppo di righe su cui l'indice columnstore esegue operazioni di gestione e compressione.A rowgroup is a group of rows on which the columnstore index performs management and compression operations.

Ad esempio, l'indice columnstore esegue queste operazioni sui rowgroup:For example, the columnstore index performs these operations on rowgroups:

  • Compressione dei rowgroup nel columnstore.Compresses rowgroups into the columnstore. La compressione viene eseguita su ogni segmento di colonna all'interno di un rowgroup.Compression is performed on each column segment within a rowgroup.
  • Unione dei rowgroup durante un'operazione ALTER INDEX REORGANIZE.Merges rowgroups during an ALTER INDEX REORGANIZE operation.
  • Creazione di nuovi rowgroup durante un'operazione ALTER INDEX REBUILD.Creates new rowgroups during an ALTER INDEX REBUILD operation.
  • Restituzione di informazioni sull'integrità e la frammentazione dei rowgroup nelle viste a gestione dinamica (DMV).Reports on rowgroup health and fragmentation in the dynamic management views (DMVs).

L'archivio differenziale è costituito da uno o più rowgroup chiamati rowgroup differenziali.The deltastore is comprised of one or more rowgroups called delta rowgroups. Ogni rowgroup delta è un indice albero B cluster che archivia le righe quando sono troppo poche per la compressione nel columnstore.Each delta rowgroup is a clustered B-tree index that stores rows when they are too few in number for compression into the columnstore.

  • Un rowgroup delta è un indice albero B cluster che archivia caricamenti e inserimenti bulk di dimensioni contenute, fino a quando il rowgroup non contiene 1.048.576 righe o l'indice non viene ricompilato.A delta rowgroup is a clustered B-tree index that stores small bulk loads and inserts until the rowgroup contains 1,048,576 rows or until the index is rebuilt. Quando un rowgroup differenziale raggiunge 1.048.576 righe, viene contrassegnato come chiuso e attende che un processo denominato motore di tuple lo comprima nel columnstore.When a delta rowgroup contains 1,048,576 rows it is marked as closed and waits for a process called the tuple-mover to compress it into the columnstore.

Ogni colonna dispone di alcuni dei relativi valori in ogni rowgroup.Each column has some of its values in each rowgroup. Questi valori sono denominati segmenti di colonna.These values are called column segments. Quando l'indice columnstore comprime un rowgroup, ogni segmento di colonna viene compresso separatamente.When the columnstore index compresses a rowgroup, it compresses each column segment separately. Per decomprimere un'intera colonna, l'indice columnstore deve semplicemente decomprimere un segmento di colonna da ogni rowgroup.To uncompress an entire column, the columnstore index only needs to uncompress one column segment from each rowgroup.

  • Un segmento di colonna è la parte dei valori di colonna in un rowgroup.A column segment is the portion of column values in a rowgroup. Ogni rowgroup contiene un segmento di colonna per ogni colonna della tabella.Each rowgroup contains one column segment for every column in the table. Ogni colonna ha un segmento di colonna in ogni rowgroup.Each column has one column segment in each rowgroup.|

    Column segmentColumn segment

I caricamenti e gli inserimenti di dimensioni contenute vengono indirizzati all'archivio differenzialeSmall loads and inserts go to the deltastore

Un indice columnstore migliora le prestazioni e la compressione del columnstore comprimendo almeno 102.400 righe alla volta nell'indice columnstore.A columnstore index improves columnstore compression and performance by compressing at least 102,400 rows at a time into the columnstore index. Per eseguire la compressione bulk delle righe, l'indice columnstore accumula caricamenti e inserimenti di dimensioni contenute nell'archivio differenziale.To compress rows in bulk, the columnstore index accumulates small loads and inserts in the deltastore. Le operazioni deltastore sono gestite in modo automatico.The deltastore operations are handled behind the scenes. Per tornare ai risultati della query corretti, l'indice columnstore cluster combina i risultati della query da columnstore e deltastore.To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

Le righe vengono indirizzate all'archivio differenziale nei casi seguenti:Rows go to the deltastore when they are:

  • Inserimento con l'istruzione INSERT INTO ... VALUES.Inserted with the INSERT INTO ... VALUES statement.
  • Alla fine di un caricamento bulk e quando sono meno di 102.400.At the end of a bulk load and they number less than 102,400.
  • Quando vengono aggiornate.Updated. Ogni aggiornamento viene implementato come un'eliminazione e un inserimento.Each update is implemented as a delete and an insert.

L'archivio differenziale archivia anche un elenco di ID per le righe eliminate contrassegnate come eliminate ma non ancora eliminate fisicamente dal columnstore.The deltastore also stores a list of IDs for deleted rows that have been marked as deleted but not yet physically deleted from the columnstore.

Quando i rowgroup differenziali sono pieni, vengono compressi nel columnstoreWhen delta rowgroups are full they get compressed into the columnstore

Gli indici columnstore cluster acquisiscono fino a 1.048.576 righe in ogni rowgroup differenziale prima di comprimere il rowgroup nel columnstore.Clustered columnstore indexes collect up to 1,048,576 rows in each delta rowgroup before compressing the rowgroup into the columnstore. migliorando così la compressione dell'indice columnstore.This improves the compression of the columnstore index. Quando un rowgroup differenziale contiene 1.048.576 righe, l'indice columnstore lo contrassegna come chiuso.When a delta rowgroup contains 1,048,576 rows, the columnstore index marks the rowgroup as closed. Un processo in background, denominato motore di tuple, trova ogni rowgroup chiuso e lo comprime nel columnstore.A background process, called the tuple-mover, finds each closed rowgroup and compresses it into the columnstore.

È possibile forzare l'inserimento dei rowgroup differenziali nel columnstore usando ALTER INDEX per ricostruire o riorganizzare l'indice.You can force delta rowgroups into the columnstore by using ALTER INDEX to rebuild or reorganize the index. Si noti che in caso di memoria insufficiente durante la compressione, l'indice columnstore potrebbe ridurre il numero di righe nel rowgroup compresso.Note that if there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup.

Ogni partizione di tabella ha i propri rowgroup e rowgroup differenzialiEach table partition has its own rowgroups and delta rowgroups

Il concetto di partizionamento è lo stesso per un indice cluster, un indice heap e un indice columnstore.The concept of partitioning is the same in both a clustered index, a heap, and a columnstore index. Il partizionamento di una tabella suddivide la tabella in piccoli gruppi di righe in base a un intervallo di valori di colonnaPartitioning a table divides the table into smaller groups of rows according to a range of column values. e viene spesso usato per la gestione dei dati.It is often used for managing the data. Ad esempio, è possibile creare una partizione per ogni anno dei dati e quindi usare il cambio della partizione per archiviare i dati in una soluzione di archiviazione meno costosa.For example, you could create a partition for each year of data, and then use partition switching to archive data to less expensive storage. Il cambio della partizione si applica agli indici columnstore e rende più semplice spostare una partizione di dati in un'altra posizione.Partition switching works on columnstore indexes and makes it easy to move a partition of data to another location.

I rowgroup sono sempre definiti all'interno di una partizione di tabella.Rowgroups are always defined within a table partition. Quando un indice columnstore viene partizionato, ogni partizione ha rowgroup compressi e rowgroup differenziali propri.When a columnstore index is partitioned, each partition has its own compressed rowgroups and delta rowgroups.

Ogni partizione può avere più rowgroup differenzialiEach partition can have multiple delta rowgroups

Ogni partizione può avere più di un rowgroup differenziale.Each partition can have more than one delta rowgroups. Quando l'indice columnstore deve aggiungere dati a un rowgroup differenziale e il rowgroup differenziale è bloccato, l'indice columnstore tenterà di ottenere un blocco su un rowgroup differenziale diverso.When the columnstore index needs to add data to a delta rowgroup and the delta rowgroup is locked, the columnstore index will try to obtain a lock on a different delta rowgroup. In assenza di rowgroup differenziali disponibili, l'indice columnstore creerà un nuovo rowgroup differenziale.If there are no delta rowgroups available, the columnstore index will create a new delta rowgroup. Ad esempio, una tabella con 10 partizioni potrebbe avere facilmente 20 o più rowgroup differenziali.For example, a table with 10 partitions could easily have 20 or more delta rowgroups.

È possibile combinare indici columnstore e rowstore nella stessa tabellaYou can combine columnstore and rowstore indexes on the same table

Un indice non cluster contiene una copia totale o parziale di tutte le righe e colonne della tabella sottostante.A nonclustered index contains a copy of part or all of the rows and columns in the underlying table. L'indice è definito sotto forma di una o più colonne della tabella e ha una condizione facoltativa che consente di filtrare le righe.The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

A partire da SQL Server 2016 (13.x)SQL Server 2016 (13.x), è possibile creare un indice columnstore non cluster aggiornabile in una tabella rowstore.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. L'indice columnstore archivia una copia dei dati, pertanto è necessario spazio di archiviazione aggiuntivo.The columnstore index stores a copy of the data so you do need extra storage. Tuttavia, i dati nell'indice columnstore verranno compressi fino a ottenere dimensioni minori rispetto a quanto richiesto per la tabella del rowstore.However, the data in the columnstore index will compress to a smaller size than the rowstore table requires. In questo modo è possibile eseguire allo stesso tempo analisi sull'indice columnstore e transazioni sull'indice rowstore.By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. Il columnstore viene aggiornato quando i dati nella tabella rowstore vengono modificati. In questo modo entrambi gli indici possono usare gli stessi dati.The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

A partire da SQL Server 2016 (13.x)SQL Server 2016 (13.x), è possibile avere uno o più indici rowstore non cluster per un indice columnstore.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index. Ciò consente di eseguire ricerche efficienti all'interno delle tabelle del columnstore sottostante.By doing this, you can perform efficient table seeks on the underlying columnstore. Sono disponibili anche altre opzioni.Other options become available too. È possibile, ad esempio, applicare un vincolo di chiave primaria tramite un vincolo UNIQUE nella tabella rowstore.For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. Di conseguenza, poiché non è possibile inserire un valore non univoco nella tabella rowstore, SQL Server non può inserire il valore nel columnstore.Since an non-unique value will fail to insert into the rowstore table, SQL Server cannot insert the value into the columnstore.

Considerazioni sulle prestazioniPerformance considerations

  • La definizione degli indici columnstore non cluster supporta l'uso di una condizione filtrata.The nonclustered columnstore index definition supports using a filtered condition. Per ridurre al minimo l'impatto sulle prestazioni conseguente all'aggiunta di un indice columnstore in una tabella OLTP, usare una condizione filtrata per creare un indice columnstore non cluster solo sui dati usati meno di frequente del carico di lavoro operativo.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

  • Una tabella in memoria può avere un solo indice columnstore.An in-memory table can have one columnstore index. È possibile crearlo durante la creazione della tabella o aggiungerlo in un secondo momento con ALTER TABLE (Transact-SQL).You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). Prima di SQL Server 2016 (13.x)SQL Server 2016 (13.x), solamente una tabella basata su disco poteva avere un indice columnstore.Before SQL Server 2016 (13.x)SQL Server 2016 (13.x), only a disk-based table could have a columnstore index.

Per altre informazioni, vedere Indici columnstore - Prestazioni delle query .For more information, refer to Columnstore indexes - Query performance.

Linee guida per la progettazioneDesign Guidance

  • Una tabella rowstore può avere un solo indice columnstore non cluster aggiornabile.A rowstore table can have one updateable nonclustered columnstore index. Prima di SQL Server 2014 (12.x)SQL Server 2014 (12.x), l'indice columnstore non cluster era un indice di sola lettura.Before SQL Server 2014 (12.x)SQL Server 2014 (12.x), the nonclustered columnstore index was read-only.

Per altre informazioni, fare riferimento a Linee guida per la progettazione di un indice columnstore.For more information, refer to Columnstore indexes - Design Guidance.

Linee guida per la progettazione di indici hashHash Index Design Guidelines

Tutte le tabelle ottimizzate per la memoria devono contenere almeno un indice in quanto gli indici consentono l'interconnessione delle righe.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. In una tabella con ottimizzazione per la memoria, ogni indice è anche ottimizzato per la memoria.On a memory-optimized table, every index is also memory-optimized. Gli indici hash sono uno dei tipi di indice possibili in una tabella ottimizzata per la memoria.Hash indexes are one of the possible index types in a memory-optimized table. Per altre informazioni, vedere Indici per le tabelle ottimizzate per la memoria.For more information, see Indexes for Memory-Optimized Tables.

Si applica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) tramite SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

Architettura dell'indice hashHash Index Architecture

Un indice hash è costituito da una matrice di puntatori e ogni elemento della matrice viene definito bucket di hash.A hash index consists of an array of pointers, and each element of the array is called a hash bucket.

  • Ogni bucket è costituito da 8 byte, che vengono usati per archiviare l'indirizzo di memoria di un elenco di collegamenti delle voci della chiave.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • Ogni voce rappresenta un valore per una chiave di indice, oltre all'indirizzo della riga corrispondente nella tabella ottimizzata per la memoria sottostante.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
  • Ogni voce punta alla voce successiva in un elenco di collegamenti di voci, tutte concatenate per il bucket corrente.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

Il numero di bucket deve essere specificato in fase di definizione dell'indice:The number of buckets must be specified at index definition time:

  • Minore è il rapporto tra bucket e righe di tabella o valori distinct, maggiore sarà la lunghezza dell'elenco di collegamenti bucket medio.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • Gli elenchi di collegamenti brevi risultano più veloci rispetto agli elenchi di collegamenti lunghi.Short link lists perform faster than long link lists.
  • Il numero massimo di bucket negli indici hash è 1.073.741.824.The maximum number of buckets in hash indexes is 1,073,741,824.

Suggerimento

Per determinare il corretto BUCKET_COUNT per i propri dati, vedere Configurazione del numero di bucket dell'indice hash.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

La funzione hash si applica alle colonne chiave dell'indice e il risultato della funzione determina il bucket in cui rientra la chiave.The hash function is applied to the index key columns and the result of the function determines what bucket that key falls into. Ogni bucket ha un puntatore per righe con valori di chiave hash. Per questi valori è eseguito il mapping al bucket.Each bucket has a pointer to rows whose hashed key values are mapped to that bucket.

La funzione di hashing utilizzata per gli indici hash presenta le caratteristiche seguenti:The hashing function used for hash indexes has the following characteristics:

  • In SQL ServerSQL Server è disponibile una funzione hash utilizzata per tutti gli indici hash. SQL ServerSQL Server has one hash function that is used for all hash indexes.
  • La funzione hash è deterministica.The hash function is deterministic. Nell'indice hash viene sempre eseguito il mapping del valore di chiave di input allo stesso bucket.The same input key value is always mapped to the same bucket in the hash index.
  • È possibile che venga eseguito il mapping di più chiavi di indice allo stesso bucket di hash.Multiple index keys may be mapped to the same hash bucket.
  • La funzione hash viene bilanciata, pertanto la distribuzione dei valori di chiave di indice in bucket di hash segue in genere una distribuzione di probabilità di Poisson o a campana e non una distribuzione lineare piana.The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson or bell curve distribution, not a flat linear distribution.
  • La distribuzione di probabilità di Poisson non è uniforme.Poisson distribution is not an even distribution. I valori delle chiavi di indice non vengono distribuiti in modo uniforme nei bucket di hash.Index key values are not evenly distributed in the hash buckets.
  • Se viene eseguito il mapping di due chiavi dell'indice allo stesso bucket di hash, si verifica una collisione hash.If two index keys are mapped to the same hash bucket, there is a hash collision. Un numero elevato di collisioni hash può influire negativamente sulle prestazioni nelle operazioni di lettura.A large number of hash collisions can have a performance impact on read operations. Un obiettivo realistico è che il 30% dei bucket contengano due valori di chiave diversi.A realistic goal is for 30% of the buckets contain two different key values.

Nell'immagine seguente è riepilogata l'interazione tra l'indice hash e i bucket.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

Configurazione del numero di bucket dell'indice hashConfiguring the hash index bucket count

Il numero di bucket dell'indice hash viene specificato al momento della creazione dell'indice e può essere modificato tramite la sintassi ALTER TABLE...ALTER INDEX REBUILD.The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

Nella maggior parte dei casi, il numero di bucket dovrebbe essere in teoria impostato su un valore compreso tra una e due volte il numero di valori distinct della chiave di indice.In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.
Non è sempre possibile stimare quanti valori ha o potrebbe avere una particolare chiave di indice.You may not always be able to predict how many values a particular index key may have, or will have. Le prestazioni sono di norma ancora soddisfacenti se il valore BUCKET_COUNT è al massimo 10 volte superiore al numero effettivo di valori chiave e un valore superiore è in genere migliore di un valore inferiore.Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

Un numero di bucket troppo ridotto presenta i seguenti svantaggi:Too few buckets has the following drawbacks:

  • Più collisioni hash di valori di chiave distinct.More hash collisions of distinct key values.
  • Ogni valore distinct è costretto a condividere lo stesso bucket con un valore distinct diverso.Each distinct value is forced to share the same bucket with a different distinct value.
  • La lunghezza media di catena per bucket aumenta.The average chain length per bucket grows.
  • Più è lunga la catena di bucket, più lente saranno le ricerche di uguaglianza nell'indice.The longer the bucket chain, the slower the speed of equality lookups in the index.

Un numero di bucket troppo elevato presenta i seguenti svantaggi:Too many buckets has the following drawbacks:

  • Un numero di bucket troppo elevato può comportare la presenza di più bucket vuoti.Too high a bucket count might result in more empty buckets.
  • I bucket vuoti influiscono sulle prestazioni delle analisi complete degli indici.Empty buckets impact the performance of full index scans. Se le analisi vengono eseguite regolarmente, è consigliabile scegliere un numero di bucket simile al numero dei valori di chiave di indice distinct.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
  • I bucket vuoti utilizzano memoria, anche se ogni bucket utilizza solo 8 byte.Empty buckets use memory, though each bucket uses only 8 bytes.

Nota

L'aggiunta di ulteriori bucket non determina in alcun modo la riduzione del concatenamento di voci che condividono un valore duplicato.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. La frequenza di duplicazione dei valori viene usata per stabilire se un hash è del tipo di indice appropriato, non per calcolare il numero di bucket.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

Considerazioni sulle prestazioniPerformance considerations

Le prestazioni di un indice hash sono:The performance of a hash index is:

  • Eccellenti quando il predicato nella clausola WHERE specifica un valore esatto per ogni colonna della chiave di indice hash.Excellent when the predicate in the WHERE clause specifies an exact value for each column in the hash index key. Se viene fornito un predicato di disuguaglianza, l'indice hash verrà ripristinato in un'analisi.A hash index will revert to a scan given an inequality predicate.
  • Scarse quando il predicato nella clausola WHERE cerca un intervallo di valori nella chiave di indice.Poor when the predicate in the WHERE clause looks for a range of values in the index key.
  • Scarse quando il predicato nella clausola WHERE specifica un determinato valore per la prima colonna di una chiave di indice hash a due colonne, ma non specifica un valore per altre colonne della chiave.Poor when the predicate in the WHERE clause stipulates one specific value for the first column of a two column hash index key, but does not specify a value for other columns of the key.

Suggerimento

Il predicato deve includere tutte le colonne nella chiave di indice hash.The predicate must include all columns in the hash index key. L'indice hash richiede una chiave per eseguire l'hashing ovvero eseguire ricerche nell'indice.The hash index requires a key (to hash) to seek into the index. Se una chiave di indice è costituita da due colonne e la clausola WHERE specifica solo la prima colonna, in SQL ServerSQL Server non sarà disponibile una chiave completa di cui eseguire l'hashing.If an index key consists of two columns and the WHERE clause only provides the first column, SQL ServerSQL Server does not have a complete key to hash. Verrà pertanto generato un piano di query per l'analisi di indice.This will result in an index scan query plan.

Se si usa un indice hash e il numero di chiavi di indice univoco supera di 100 volte (o più) il numero di righe, è necessario aumentare il numero di bucket per evitare catene di righe troppo grandi oppure usare un indice non cluster.If a hash index is used and the number of unique index keys is 100 times (or more) than the row count, consider either increasing to a larger bucket count to avoid large row chains, or use a nonclustered index instead.

Considerazioni sulla dichiarazioneDeclaration considerations

Un indice hash può essere presente solo in una tabella ottimizzata per la memoria.A hash index can exist only on a memory-optimized table. Non può esistere in una tabella basata su disco.It cannot exist on a disk-based table.

Un indice hash può essere dichiarato:A hash index can be declared as:

  • UNIQUE, oppure per impostazione predefinita Nonunique.UNIQUE, or can default to Non-Unique.
  • NONCLUSTERED, che è l'impostazione predefinita.NONCLUSTERED, which is the default.

Di seguito è riportato un esempio della sintassi per creare un indice hash al di fuori dell'istruzione CREATE TABLE:The following is an example of the syntax to create a hash index, outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
ADD INDEX ix_hash_Column2 UNIQUE  
HASH (Column2) WITH (BUCKET_COUNT = 64);

Versioni delle righe e Garbage CollectionRow versions and garbage collection

In una tabella ottimizzata per la memoria, quando una riga è interessata da un'istruzione UPDATE, la tabella crea una versione aggiornata della riga.In a memory-optimized table, when a row is affected by an UPDATE, the table creates an updated version of the row. Durante la transazione di aggiornamento, altre sessioni potrebbero riuscire a leggere la versione precedente della riga e quindi evitare il rallentamento delle prestazioni associato a un blocco di riga.During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

L'indice hash potrebbe anche avere versioni diverse delle relative voci per includere l'aggiornamento.The hash index might also have different versions of its entries to accommodate the update.

In seguito, quando le versioni precedenti non sono più necessarie, un thread di Garbage Collection (GC) attraversa i bucket e i relativi elenchi di collegamenti per eliminare le voci obsolete.Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. Il thread GC offre prestazioni migliori se le catene degli elenchi di collegamenti sono brevi.The GC thread performs better if the link list chain lengths are short. Per altre informazioni, vedere Garbage Collection per OLTP in memoria.For more information, refer to In-Memory OLTP Garbage Collection.

Linee guida per la progettazione di indici non cluster ottimizzati per la memoriaMemory-Optimized Nonclustered Index Design Guidelines

Gli indici non cluster sono uno dei tipi di indice possibili in una tabella ottimizzata per la memoria.Nonclustered indexes are one of the possible index types in a memory-optimized table. Per altre informazioni, vedere Indici per le tabelle ottimizzate per la memoria.For more information, see Indexes for Memory-Optimized Tables.

Si applica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) tramite SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

Architettura dell'indice non cluster in memoriaIn-memory Nonclustered Index Architecture

Gli indici non cluster in memoria sono implementati mediante una struttura di dati albero Bw, inizialmente concepita e descritta da Microsoft Research nel 2011.In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree, originally envisioned and described by Microsoft Research in 2011. Un albero Bw è una variazione priva di latch e blocco di un albero B.A Bw-Tree is a lock and latch-free variation of a B-Tree. Per altri dettagli, vedere Albero Bw: un albero B per nuove piattaforme Hardware.For more details please see The Bw-Tree: A B-tree for New Hardware Platforms.

A un livello elevato l'albero Bw può essere interpretato come una mappa di pagine organizzate in base all'ID della pagina (PidMap), una struttura per allocare e riusare gli ID di pagine (PidAlloc) e un set di pagine collegate nella mappa di pagine e tra di loro.At a very high level the Bw-Tree can be understood as a map of pages organized by page ID (PidMap), a facility to allocate and reuse page IDs (PidAlloc) and a set of pages linked in the page map and to each other. Questi tre sottocomponenti di alto livello costituiscono la struttura interna di base di un albero Bw.These three high level sub-components make up the basic internal structure of a Bw-Tree.

La struttura è simile a quella di un normale albero B in quanto ogni pagina ha una raccolta di valori di chiave ordinati e sono presenti livelli di indice che puntano a un livello inferiore e livelli foglia che puntano a una riga di dati.The structure is similar to a normal B-Tree in the sense that each page has a set of key values that are ordered and there are levels in the index each pointing to a lower level and the leaf levels point to a data row. Tuttavia sono presenti delle differenze.However there are several differences.

Proprio come negli indici hash, possono essere collegate tra loro (versioni) più righe di dati.Just like hash indexes, multiple data rows can be linked together (versions). I puntatori di pagina tra i livelli sono l'ID di pagine logiche, ovvero gli offset in una tabella di mapping di pagina, che a sua volta contiene l'indirizzo fisico per ogni pagina.The page pointers between the levels are logical page IDs, which are offsets into a page mapping table, that in turn has the physical address for each page.

Non sono disponibili aggiornamenti sul posto di pagine di indice.There are no in-place updates of index pages. A questo scopo, vengono introdotte nuove pagine delta.New delta pages are introduced for this purpose.

  • Per gli aggiornamenti della pagina, non sono necessari blocchi o latch.No latching or locking is required for page updates.
  • Le pagine di indice non hanno dimensioni fisse.Index pages are not a fixed size.

Il valore chiave in ogni pagina a livello non foglia descritta è il valore più alto che l'elemento figlio a cui punta contiene e ogni riga contiene anche l'ID pagina della pagina logica.The key value in each non-leaf level page depicted is the highest value that the child that it points to contains and each row also contains that page logical page ID. Nelle pagine a livello di foglia, oltre al valore chiave, è contenuto l'indirizzo fisico della riga di dati.On the leaf-level pages, along with the key value, it contains the physical address of the data row.

Le ricerche di punti frequenti sono simili a quelle dell'albero B tranne per il fatto che, poiché le pagine sono collegate solo in una direzione, Motore di database di SQL ServerSQL Server Database Engine segue i puntatori di pagina a destra, in cui ogni pagina che non è una pagina foglia corrisponde al valore massimo del relativo elemento figlio, anziché al valore più basso come accade in un albero B.Point lookups are similar to B-Trees except that because pages are linked in only one direction, the Motore di database di SQL ServerSQL Server Database Engine follows right page pointers, where each non-leaf pages has the highest value of its child, rather than lowest value as in a B-Tree.

Se si deve modificare una pagina foglia, Motore di database di SQL ServerSQL Server Database Engine non modifica la pagina stessa.If a Leaf-level page has to change, the Motore di database di SQL ServerSQL Server Database Engine does not modify the page itself. Motore di database di SQL ServerSQL Server Database Engine crea un record delta che descrive la modifica e lo aggiunge alla pagina precedente.Rather, the Motore di database di SQL ServerSQL Server Database Engine creates a delta record that describes the change, and appends it to the previous page. Aggiorna l'indirizzo di tabella della mappa per la pagina precedente all'indirizzo del record delta che diventa l'indirizzo fisico per questa pagina.Then it also updates the page map table address for that previous page, to the address of the delta record which now becomes the physical address for this page.

Per la gestione della struttura di un albero Bw sono possibili tre opzioni diverse: consolidamento, divisione e unione.There are three different operations that can be required for managing the structure of a Bw-Tree: consolidation, split and merge.

Consolidamento del deltaDelta Consolidation

Le prestazioni della ricerca potrebbero risultare ridotte in presenza di una lunga catena di record delta, in quanto potrebbe significare l'attraversamento di lunghe catene durante una ricerca in un indice.A long chain of delta records can eventually degrade search performance as it could mean we are traversing long chains when searching through an index. Se un nuovo record delta è aggiunto a un catena che ha già 16 elementi, le modifiche apportate nel record delta saranno consolidate nella pagina di indice di riferimento e la pagina sarà ricompilata, includendo le modificate indicate dal nuovo record delta che ha avviato il consolidamento.If a new delta record is added to a chain that already has 16 elements, the changes in the delta records will be consolidated into the referenced index page, and the page will then be rebuilt, including the changes indicated by the new delta record that triggered the consolidation. La nuova pagina avrà lo stesso ID ma un nuovo indirizzo di memoria.The newly rebuilt page will have the same page ID but a new memory address.

hekaton_tables_23ehekaton_tables_23e

Dividere la paginaSplit page

Una pagina di indice nell'albero Bw aumenta in base alle esigenze a partire dall'archiviazione di una singola riga fino all'archiviazione di un massimo di 8 KB.An index page in Bw-Tree grows on as-needed basis starting from storing a single row to storing a maximum of 8 KB. Una volta che la pagina di indice ha raggiunto 8 KB, un nuovo inserimento di una riga singola causa la divisione della pagina di indice.Once the index page grows to 8 KB, a new insert of a single row will cause the index page to split. Per una pagina interna, ciò significa che non è disponibile più spazio per aggiungere altri valori chiave e puntatori. Per una pagina foglia, ciò significa che, una volta incorporati tutti i record delta, la riga risulta troppo grande per la pagina.For an internal page, this means when there is no more room to add another key value and pointer, and for a leaf page, it means that the row would be too big to fit on the page once all the delta records are incorporated. Le informazioni statistiche nell'intestazione della pagina foglia tengono traccia della quantità di spazio necessaria per consolidare i record delta. Tali informazioni vengono regolate quando un nuovo record delta viene aggiunto.The statistics information in the page header for a leaf page keeps track of how much space would be required to consolidate the delta records, and that information is adjusted as each new delta record is added.

Un'operazione di divisione è effettuata in due passaggi atomici.A Split operation is done in two atomic steps. Nell'immagine seguente, si supponga che una pagina foglia forzi una divisione in quanto una chiave con valore 5 viene inserita e che sia presente una pagina non foglia che punta alla fine della pagina a livello foglia attuale (valore chave 4).In the picture below, assume a Leaf-page forces a split because a key with value 5 is being inserted, and a non-leaf page exists pointing to the end of the current Leaf-level page (key value 4).

hekaton_tables_23fhekaton_tables_23f

Passaggio 1: Assegnare due nuove pagine P1 e P2 e suddividere le righe dalla pagina P1 precedente in queste nuove pagine, inclusa la riga appena inserita.Step 1: Allocate two new pages P1 and P2, and split the rows from old P1 page onto these new pages, including the newly inserted row. Un nuovo slot nella tabella di mapping della pagina viene usato per archiviare l'indirizzo fisico della pagina P2.A new slot in Page Mapping Table is used to store the physical address of page P2. Le due pagine, P1 e P2, non sono ancora accessibili ad alcuna operazione simultanea.These pages, P1 and P2 are not accessible to any concurrent operations yet. Il puntatore logico da P1 a P2 è impostato.In addition, the logical pointer from P1 to P2 is set. Poi l'aggiornamento della tabella di mapping della pagina per modificare il puntatore dal vecchio P1 al nuovo P1, avviene in un unico passaggio atomico.Then, in one atomic step update the Page Mapping Table to change the pointer from old P1 to new P1.

Passaggio 2: La pagina non foglia punta a P1 ma non è presente alcun puntatore diretto da una pagina non foglia per P2.Step 2: The non-leaf page points to P1 but there is no direct pointer from a non-leaf page to P2. P2 è raggiungibile solo tramite P1.P2 is only reachable via P1. Per creare un puntatore da una pagina non foglia a P2, allocare una nuova pagina non foglia (pagina di indice interno), copiare tutte le righe dalla pagina foglia precedente e aggiungere una nuova riga in modo che punti a P2.To create a pointer from a non-leaf page to P2, allocate a new non-leaf page (internal index page), copy all the rows from old non-leaf page, and add a new row to point to P2. Una volta terminata questa operazione, eseguita in un unico passaggio atomico, aggiornare la tabella di mapping della pagina per modificare il puntatore dalla pagina non foglia precedente alla nuova pagina non foglia.Once this is done, in one atomic step, update the Page Mapping Table to change the pointer from old non-leaf page to new non-leaf page.

Unire le pagineMerge page

Quando un'operazione DELETE restituisce una pagina con meno del 10% della dimensione massima della pagina (attualmente 8 KB) o con una sola riga, la pagina verrà unita a una pagina contigua.When a DELETE operation results in a page having less than 10% of the maximum page size (currently 8 KB), or with a single row on it, that page will be merged with a contiguous page.

Quando viene eliminata una riga da una pagina, viene aggiunto un record delta per l'eliminazione.When a row is deleted from a page, a delta record for the delete is added. Viene anche eseguito un controllo per determinare se la pagina di indice (pagina non foglia) soddisfa i criteri per l'unione.Additionally, a check is made to determine if the index page (non-leaf page) qualifies for Merge. Questo controllo verifica se lo spazio rimanente dopo l'eliminazione della riga sarà inferiore al 10% delle dimensioni massime della pagina.This check verifies if the remaining space after deleting the row will be less than 10% of maximum page size. Se i requisiti sono soddisfatti, l'unione viene eseguita in tre passaggi atomici.If it does qualify, the Merge is performed in three atomic steps.

Nell'immagine seguente, si supponga che un'operazione DELETE eliminerà il valore chiave 10.In the picture below, assume a DELETE operation will delete the key value 10.

hekaton_tables_23ghekaton_tables_23g

Passaggio 1: Viene creata una pagina delta che rappresenta il valore di chiave 10 (triangolo blu) e il suo puntatore nella pagina non foglia Pp1 è impostato per la nuova pagina delta.Step 1: A delta page representing key value 10 (blue triangle) is created and its pointer in the non-leaf page Pp1 is set to the new delta page. Viene anche creata una pagina speciale delta di tipo unione (triangolo verde) e viene collegata in modo da puntare alla pagina delta.Additionally a special merge-delta page (green triangle) is created, and it is linked to point to the delta page. In questa fase le due pagine (pagina delta e pagina delta di tipo unione) non sono visibili per le transazioni simultanee.At this stage, both pages (delta page and merge-delta page) are not visible to any concurrent transaction. In un unico passaggio atomico, il puntatore alla pagina di livello foglia P1 nella tabella di mapping della pagina viene aggiornato per puntare alla pagina delta di tipo unione.In one atomic step, the pointer to the Leaf-level page P1 in the Page Mapping Table is updated to point to the merge-delta page. Dopo questo passaggio, la voce relativa al valore chiave 10 in Pp1 punta alla pagina delta dell'unione.After this step, the entry for key value 10 in Pp1 now points to the merge-delta page.

Passaggio 2: La riga che rappresenta il valore chiave 7 nella pagina non foglia Pp1 deve essere rimosso e la voce per il valore chiave 10 aggiornato per puntare a P1.Step 2: The row representing key value 7 in the non-leaf page Pp1 needs to be removed, and the entry for key value 10 updated to point to P1. A tale scopo, viene allocata una nuova pagina non foglia Pp2 e vengono copiate tutte le righe a partire da Pp1, tranne la riga che rappresenta il valore chiave 7. La riga per il valore chiave 10 viene quindi aggiornata per puntare alla pagina P1.To do this, a new non-leaf page Pp2 is allocated and all the rows from Pp1 are copied except for the row representing key value 7; then the row for key value 10 is updated to point to page P1. Dopo questa operazione, eseguita in un unico passaggio atomico, la voce della tabella di mapping di pagina che punta a Pp1 viene aggiornata per puntare a Pp2.Once this is done, in one atomic step, the Page Mapping Table entry pointing to Pp1 is updated to point to Pp2. Pp1 non è più raggiungibile.Pp1 is no longer reachable.

Passaggio 3: Le pagine a livello foglia P2 e P1 vengono unite e le pagine delta rimosse.Step 3: The Leaf-level pages P2 and P1 are merged and the delta pages removed. A tale scopo, viene allocata una nuova pagina P3 e le righe da P2 a P1 sono unite. Le modifiche apportate alla pagina delta sono incluse nella nuova pagina P3.To do this, a new page P3 is allocated and the rows from P2 and P1 are merged, and the delta page changes are included in the new P3. In un unico passaggio atomico, la voce della tabella di mapping di pagina che punta alla pagina P1 viene aggiornata per puntare alla pagina P3.Then, in one atomic step, the Page Mapping Table entry pointing to page P1 is updated to point to page P3.

Considerazioni sulle prestazioniPerformance considerations

Quando si esegue una query su una tabella ottimizzata per la memoria con predicati di disuguaglianza, gli indici non cluster offriranno prestazioni migliori rispetto agli indici hash non cluster.The performance of a nonclustered index is better than nonclustered hash indexes when querying a memory-optimized table with inequality predicates.

Nota

Una colonna di una tabella ottimizzata per la memoria può far parte sia di un indice hash che di un indice non cluster.A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

Suggerimento

Quando in un indice non cluster le colonne chiave contengono molti valori duplicati, le prestazioni per le operazioni di aggiornamento, inserimento ed eliminazione potrebbero risultare ridotte.When a column in a nonclustered index key columns have many duplicate values, performance can degrade for updates, inserts, and deletes. Un modo per migliorare le prestazioni in questa situazione è aggiungere un'altra colonna nell'indice non cluster.One way to improve performance in this situation is to add another column to the nonclustered index.

Ulteriori informazioniAdditional Reading

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)CREATE XML INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
Riorganizzare e ricompilare gli indici Reorganize and Rebuild Indexes
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
Creare una chiave primaria Create a Primary Key
Indici per tabelle con ottimizzazione per la memoriaIndexes for Memory-Optimized Tables
Indici columnstore - PanoramicaColumnstore Indexes overview
Risoluzione dei problemi per gli indici hash per tabelle ottimizzate per la memoria Troubleshooting Hash Indexes for Memory-Optimized Tables
Viste a gestione dinamica correlate alle tabelle con ottimizzazione per la memoria (Transact-SQL) Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Funzioni a gestione dinamica e DMV correlate all'indice (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
Indici per le colonne calcolate Indexes on Computed Columns
Indici e ALTER TABLE Indexes and ALTER TABLE
Adaptive Index Defrag (Deframmentazione dell'indice adattativo)Adaptive Index Defrag