Taille maximale des clés d'index

Lorsque vous concevez un index contenant de nombreuses colonnes clés ou des colonnes volumineuses, calculez la taille de la clé d'index pour vérifier que vous ne dépassez pas la taille de clé d'index maximale. SQL Server 2005 conserve la limite de 900 octets pour la taille maximale des colonnes de clés d'index. Cela exclut les colonnes non-clés comprises dans la définition des index non-cluster.

Calcul de la taille d'une clé d'index

Pour calculer la taille d'une clé d'index, procédez comme suit.

1. Affichez les propriétés des colonnes de la table sur lesquelles se base l'index. Pour cela, vous pouvez utiliser la vue de catalogue sys.columns.

2. Totalisez la longueur de chaque colonne qui sera définie dans la clé d'index.
   Par exemple, l'instruction suivante agrège la colonne max_length de la vue de catalogue sys.columns pour les colonnes spécifiées dans la table 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');
   

   Remarque :

   Si une colonne de la table possède un type de données Unicode, tel que nchar ou nvarchar, la longueur de la colonne affichée est la longueur de stockage de la colonne. Il s'agit du double du nombre de caractères spécifié dans l'instruction CREATE TABLE. Dans l'exemple précédent, City est défini en tant que type de données nvarchar(30) ; par conséquent, la longueur de stockage de la colonne est de 60.

3. Si la longueur totale est inférieure à 900 octets, les colonnes peuvent participer en tant que colonnes de clés d'index. Si la longueur totale dépasse 900 octets, vérifiez les informations suivantes relatives aux options et observations supplémentaires qui s'imposent éventuellement.
   L'instruction CREATE INDEX utilise les algorithmes suivants pour calculer la taille de la clé d'index :

   • Si l'addition de la taille des toutes les colonnes de clés fixes et de la taille maximale de toutes les colonnes de clés variables spécifiées dans l'instruction CREATE INDEX est inférieure à 900 octets, l'instruction CREATE INDEX aboutit sans message d'avertissement ou d'erreur.
   • Si l'addition de la taille de toutes les colonnes de clés fixes et de la taille maximale de toutes les colonnes de clés variables dépasse 900, mais si l'addition de la taille de toutes les colonnes de clés fixes et des tailles minimales des colonnes de clés variables est inférieure à 900, l'instruction CREATE INDEX aboutit avec un message d'avertissement indiquant qu'une instruction INSERT ou UPDATE ultérieure pourra échouer si elle spécifie des valeurs générant une valeur de clé supérieure à 900 octets. L'instruction CREATE INDEX échoue si les lignes de données existantes de la table ont des valeurs qui génèrent une clé supérieure à 900 octets. Toute instruction INSERT ou UPDATE ultérieure qui spécifie des valeurs de données générant une valeur de clé supérieure à 900 octets échouera.
   • L'instruction CREATE INDEX échoue si l'addition de la taille de toutes les colonnes de clés fixes et de la taille minimale de toutes les colonnes variables spécifiées dans l'instruction CREATE INDEX dépasse 900 octets.

   Le tableau suivant récapitule les résultats de la création d'index qui respectent ou dépassent les restrictions de taille maximale des clés d'index.

Taille minimale de la/des colonnes de longueur variable + taille de la/des colonnes de données fixes	Taille maximale de la/des colonnes de longueur variable + taille de la/des colonnes de données fixes	MAX de la SUM des longueurs de colonnes de clés d'index pour les lignes existantes*	L'index est créé	Type du message	Erreur d'exécution d'opération INSERT ou UPDATE en raison d'une valeur de clé d'index surdimensionnée
> 900 octets	Non applicable	Non applicable	Non	Erreur	Aucun index présent pour générer une erreur.
<= 900 octets	<= 900 octets	Non applicable	Oui	Aucun	Non
<= 900 octets	> 900 octets	<= 900 octets	Oui	Avertissement	Uniquement si la somme des longueurs actuelles de toutes les colonnes d'index est supérieure à 900 octets.
<= 900 octets	> 900 octets	> 900 octets	Non	Erreur	Aucun index présent pour générer une erreur.

* Au moment où l'instruction CREATE INDEX est exécutée, aucune des lignes de la table ne peut avoir de valeur de clé d'index dont la longueur totale dépasse 900 octets.

Utilisation de colonnes incluses pour éviter les limites de taille

Vous pouvez inclure des colonnes non-clés dans un index non-cluster afin d'éviter les limitations actuelles en matière de taille d'index, à savoir des colonnes contenant 16 clés maximum et une taille maximale de clé d'index de 900 octets. Le Moteur de base de données SQL Server 2005 ne prend pas en compte les colonnes non-clés lors du calcul du nombre de colonnes de clés d'index ou de la taille totale des colonnes de clés d'index. Dans un index non-cluster possédant des colonnes incluses, la taille totale des colonnes de clés d'index est limitée à 900 octets. La taille totale de toutes les colonnes non-clés est limitée uniquement à la taille des colonnes spécifiées dans la clause INCLUDE ; par exemple, les colonnes varchar(max) sont limitées à 2 Go. Les colonnes indiquées dans la clause INCLUDE peuvent être de tous les types de données, à l'exception de text, ntext et image.

Voir aussi

Concepts

Consignes générales pour la création d'index
Index avec colonnes incluses
Notions de base de la conception d'index

Autres ressources

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

Aide et Informations

Assistance sur SQL Server 2005