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

1. Visualizzare le proprietà delle colonne della tabella su cui si basa l'indice. A tale scopo utilizzare la vista del catalogo sys.columns.

2. 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 AdventureWorks;
   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.

3. 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	Nessun valore con	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	Nessun valore con	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 evitare limiti di 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. In Motore di database di SQL Server 2005 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 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). Le colonne nella clausola INCLUDE possono avere qualsiasi tipo di dati, ad eccezione di text, ntext e image.

Vedere anche

Concetti

Linee guida generali per la progettazione di indici
Indice con colonne incluse
Nozioni fondamentali sulla progettazione di indici

Altre risorse

CREATE INDEX (Transact-SQL)
sys.columns (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)

Guida in linea e informazioni

Assistenza su SQL Server 2005