Indice con colonne incluse

Utilizzo di colonne incluse per evitare limiti di dimensioni

È 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). Il Motore di database non prende in esame le colonne non chiave durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi di indice.

Si supponga, ad esempio, che si desideri indicizzare le colonne seguenti nella tabella Document del database di esempio AdventureWorks:

     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 la limitazione di dimensione di 900 byte (455 x 2). Utilizzando la clausola INCLUDE dell'istruzione CREATE INDEX, è possibile definire la chiave dell'indice come (Title, Revision) e FileName come colonna non chiave. In questo modo, la dimensione della chiave dell'indice sarebbe pari a 110 byte (55 x 2) e l'indice potrebbe ancora contenere tutte le colonne necessarie. Nell'istruzione seguente viene creato un simile indice.

USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title       
ON Production.Document (Title, Revision)       
INCLUDE (FileName);       

Linee guida sull'utilizzo degli indici con colonne incluse

Quando si progettano indici non cluster con colonne incluse è opportuno considerare le indicazioni generali seguenti:

• Le colonne non chiave vengono definite nella clausola INCLUDE dell'istruzione CREATE INDEX.

• Le colonne non chiave possono essere definite solo su indici non cluster su tabelle o viste indicizzate.

• Sono consentiti tutti i tipi di dati, a eccezione di text, ntext e image.

• Le colonne calcolate che sono deterministiche e al tempo stesso precise o imprecise possono essere colonne incluse. Per ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

• In maniera simile alle colonne chiave, le colonne calcolate derivate dai tipi di dati image, ntext e text possono essere colonne non chiave (incluse) purché il tipo di dati della colonna calcolata sia consentito come colonna non chiave dell'indice.

• Non è possibile specificare i nomi delle colonne sia nell'elenco INCLUDE che nell'elenco delle colonne chiave.

• Non è possibile ripetere i nomi delle colonne nell'elenco INCLUDE.

Linee guida sulle dimensioni delle colonne

• È necessario definire almeno una colonna chiave. Il numero massimo di colonne non chiave è 1023. Questo limite è rappresentato dal numero massimo di colonne nelle tabelle meno 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).

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

Linee guida sulla modifica delle colonne

Quando si modifica una colonna di tabella che è stata definita come colonna inclusa, vengono applicate le limitazioni seguenti:

• Non è possibile eliminare dalla tabella le colonne non chiave se non si è prima eliminato l'indice.

• Non è possibile modificare le colonne non chiave se non per eseguire le operazioni seguenti:

  • Modifica del supporto di valori NULL della colonna da NOT NULL a NULL.

  • Aumento della lunghezza di colonne varchar, nvarchar o varbinary.

    [!NOTA]

    Tali restrizioni sulla modifica delle colonne sono valide anche per le colonne chiave indice.

Indicazioni sulla progettazione

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. Modificare in colonne non chiave incluse tutte le altre colonne che coprono la query. 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.

Si supponga, ad esempio, che si desideri progettare un indice per coprire la query seguente.

USE AdventureWorks;
GO
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. Sebbene sia possibile definire tutte le colonne come colonne chiave, la dimensione delle chiavi sarebbe di 334 byte. 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.

Nell'istruzione seguente viene creato un indice con colonne incluse per coprire la query.

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode       
ON Person.Address (PostalCode)       
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);       

Considerazioni sulle prestazioni

Evitare di aggiungere colonne non necessarie. L'aggiunta di troppe colonne indice, chiave o non chiave, può avere le implicazioni sulle prestazioni seguenti:

• In una pagina rientreranno meno righe di indice. Ciò potrebbe causare più operazioni di I/O e ridurre l'efficienza della cache.

• Sarà necessario più spazio su disco per archiviare l'indice. 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 lo spazio su disco richiesto. Ciò accade perché i valori delle colonne vengono copiati nel livello foglia dell'indice, risiedendo in tal modo sia nell'indice che nella tabella di base.

• La manutenzione degli indici può aumentare il tempo necessario per eseguire modifiche, inserimenti, aggiornamenti o eliminazioni nella tabella sottostante o nella vista indicizzata.

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. Per ulteriori informazioni sulla valutazione delle prestazioni delle query, vedere Ottimizzazione delle query.

Vedere anche

Concetti