Indice con colonne incluse
In SQL Server 2005 è possibile estendere le funzionalità degli indici non cluster aggiungendo colonne non chiave al livello foglia dell'indice non cluster. Con l'inclusione di colonne non chiave è possibile creare indici non cluster in grado di coprire più query. Ciò è possibile perché le colonne non chiave presentano i vantaggi seguenti:
- Possono essere tipi di dati che non sono consentiti come colonne chiave indice.
- Non vengono prese in esame dal Motore di database durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi di indice.
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. I vantaggi nelle prestazioni si ottengono poiché Query Optimizer può 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.
[!NOTA] Quando un indice contiene tutte le colonne a cui fa riferimento la query, viene generalmente indicato come indice di copertura.
Mentre le colonne chiave sono archiviate a tutti i livelli dell'indice, le colonne non chiave sono archiviate solo al livello foglia. Per ulteriori informazioni sui livelli degli indici, vedere Organizzazione di tabelle e indici.
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
Creazione di indici (Motore di database)
Creazione di indici con colonne incluse
Linee guida generali per la progettazione di indici
Nozioni fondamentali sulla progettazione di indici
Dimensione massima delle chiavi di indice
Visualizzazione delle informazioni relative agli indici