Articolo
03/31/2012

Dimensione massima delle chiavi di indice

Quando si progetta un indice contenente numerose colonne chiave o colonne di grandi dimensioni, calcolare la dimensione della chiave dell'indice in modo che non superi la dimensione massima consentita. In SQL Server viene mantenuto il limite di 900 byte per la dimensione massima complessiva di tutte le colonne chiave dell'indice, escluse le colonne non chiave incluse nella definizione degli indici non cluster.

Calcolo della dimensione di una chiave di indice

Per calcolare la dimensione di una chiave di indice, eseguire le operazioni seguenti.

Visualizzare le proprietà delle colonne della tabella su cui si basa l'indice. A tale scopo utilizzare la vista del catalogo sys.columns.
Fare la somma della lunghezza delle colonne definite nella chiave dell'indice.

L'istruzione seguente aggrega ad esempio la colonna max_length della vista del catalogo sys.columns per le colonne specificate nella tabella Person.Address.
```
USE AdventureWorks2008R2;
GO
SELECT SUM(max_length)AS TotalIndexKeySize
FROM sys.columns
WHERE name IN (N'AddressLine1', N'AddressLine2', N'City', N'StateProvinceID', N'PostalCode')
AND object_id = OBJECT_ID(N'Person.Address');
```
Nota

Se la colonna di una tabella ha un tipo di dati Unicode quale nchar o nvarchar, la lunghezza della colonna visualizzata corrisponde alla lunghezza di archiviazione della colonna, che prevede il doppio del numero di caratteri specificato nell'istruzione CREATE TABLE. Nell'esempio precedente City ha un tipo di dati nvarchar(30). La lunghezza di archiviazione della colonna è pertanto 60.
Se la lunghezza complessiva è inferiore a 900 byte, le colonne possono essere utilizzate come colonne chiave dell'indice, se invece supera i 900 byte, vedere le opzioni e considerazioni aggiuntive seguenti.

L'istruzione CREATE INDEX utilizza gli algoritmi seguenti per calcolare la dimensione della chiave di indice:
- Se la dimensione di tutte le colonne chiave fisse sommata alla dimensione massima di tutte le colonne chiave variabili specificate nell'istruzione CREATE INDEX è inferiore a 900 byte, l'istruzione CREATE INDEX viene completata con esito positivo senza messaggi di avviso o errori.
- Se la dimensione di tutte le colonne chiave fisse sommata alla dimensione massima di tutte le colonne chiave variabili supera 900, ma la dimensione di tutte le colonne chiave fisse sommata alla dimensione minima delle colonne chiave variabili è inferiore a 900, l'istruzione CREATE INDEX viene eseguita con esito positivo e restituisce un messaggio di avviso indicante che un'istruzione INSERT o UPDATE successiva potrebbe avere esito negativo se vengono specificati valori che generano un valore di chiave maggiore di 900 byte. L'istruzione CREATE INDEX ha esito negativo se le righe di dati esistenti nella tabella contengono valori che generano una chiave maggiore di 900 byte. L'istruzione INSERT o UPDATE successiva che specifica valori di dati che generano un valore di chiave maggiore di 900 byte ha esito negativo.
- L'istruzione CREATE INDEX ha esito negativo se la dimensione di tutte le colonne chiave fisse sommata alla dimensione minima di tutte le colonne variabili specificate nell'istruzione CREATE INDEX supera 900 byte.
Nella tabella seguente sono riportati i risultati della creazione di indici che soddisfano o meno i limiti alla dimensione massima delle chiavi di indice.

Dimensione minima delle colonne di lunghezza variabile sommate alla dimensione delle colonne di lunghezza fissa	Dimensione massima delle colonne di lunghezza variabile sommate alla dimensione delle colonne di lunghezza fissa	Valore massimo della somma tra le lunghezze delle colonne chiave dell'indice per le righe esistenti*	Indice creato	Tipo di messaggio	Errore di run-time delle istruzioni INSERT o UPDATE a causa di un valore di chiave di indice troppo alto
> 900 byte	Non rilevante	Non rilevante	No	Errore	Nessun indice disponibile, pertanto nessun errore.
<= 900 byte	<= 900 byte	Non rilevante	Sì	Nessuno	No
<= 900 byte	> 900 byte	<= 900 byte	Sì	Avviso	Solo se la somma delle lunghezze correnti di tutte le colonne dell'indice è maggiore di 900 byte.
<= 900 byte	> 900 byte	> 900 byte	No	Errore	Nessun indice disponibile, pertanto nessun errore.

* Al momento dell'esecuzione dell'istruzione CREATE INDEX, a nessuna delle righe della tabella possono essere associati valori di chiave di indice la cui somma complessiva delle lunghezze sia maggiore di 900 byte.

Utilizzo di colonne incluse per aggirare i limiti alle dimensioni

È possibile includere colonne non chiave in un indice non cluster per aggirare i limiti alle dimensioni dell'indice correnti che prevedono un massimo di 16 colonne chiave e una dimensione massima delle chiavi dell'indice pari a 900 byte. Nel Motore di database di SQL Server le colonne non chiave non vengono prese in considerazione durante il calcolo del numero di colonne chiave dell'indice o della loro dimensione complessiva. In un indice non cluster con colonne incluse la dimensione complessiva delle colonne chiave dell'indice è limitata a 900 byte. La dimensione complessiva di tutte le colonne non chiave è limitata esclusivamente dalla dimensione delle colonne specificate nella clausola INCLUDE. La dimensione massima delle colonne di tipo varchar(max) è ad esempio pari a 2 GB. Le colonne nella clausola INCLUDE possono avere qualsiasi tipo di dati, ad eccezione di text, ntext e image.

Nota

Quando le tabelle vengono partizionate, se non sono già presenti in un indice cluster non univoco, le colonne chiave di partizionamento vengono aggiunte all'indice tramite il Motore di database. Le dimensioni combinate delle colonne indicizzate (senza contare le colonne incluse) e delle colonne di partizionamento aggiunte non possono superare 1800 byte in un indice cluster non univoco.