CREATE INDEX (Transact-SQL)
Crée un index relationnel sur une table ou une vue spécifiée sur une table donnée. Un index peut être créé avant que la table soit remplie de données. Les index relationnels peuvent être créés sur des tables ou des vues d'une autre base de données en spécifiant un nom de base de données qualifié.
[!REMARQUE]
Pour plus d'informations sur la création d'un index XML, consultez CREATE XML INDEX (Transact-SQL). Pour plus d'informations sur la création d'un index spatial, consultez CREATE SPATIAL INDEX (Transact-SQL).
Syntaxe
Create Relational Index
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> (column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE (column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name (column_name)
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}
<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR =fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP =max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE}
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<filter_predicate> ::=
{ <conjunct> [ AND <filter_predicate> ] }
<conjunct> ::=
{ <disjunct> | <comparison> }
<disjunct> ::=
{ column_name IN (constant ,...n ) }
<comparison> ::=
{ column_name <comparison_op> constant }
<comparison_op> ::=
{ IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }
<range> ::=
<partition_number_expression> TO <partition_number_expression>
Backward Compatible Relational IndexImportant The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> (column_name [ ASC | DESC ] [ ,...n ] )
[ WITH <backward_compatible_index_option> [ ,...n ] ]
[ ON { filegroup_name | "default" } ]
<object> ::=
{
[ database_name. [ owner_name ] . | owner_name. ]
table_or_view_name
}
<backward_compatible_index_option> ::=
{
PAD_INDEX
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB
| IGNORE_DUP_KEY
| STATISTICS_NORECOMPUTE
| DROP_EXISTING
}
Arguments
UNIQUE
Crée un index unique sur une table ou une vue. Un index unique est un index dans lequel deux lignes ne peuvent pas avoir la même valeur de clé d'index. Un index cluster d'une vue doit être unique.Le moteur de base de données ne permet pas de créer un index unique sur des colonnes qui contiennent déjà des valeurs dupliquées, que IGNORE_DUP_KEY soit ou non activé (ON). Si vous tentez de le faire, le moteur de base de données affiche un message d'erreur. Les valeurs dupliquées doivent être supprimées pour qu'un index unique puisse être créé sur la ou les colonnes. Les colonnes utilisées dans un index unique doivent être définies avec la valeur NOT NULL, car plusieurs valeurs NULL sont considérées comme des valeurs dupliquées lors de la création d'un index unique.
CLUSTERED
Crée un index dans lequel l'ordre logique des valeurs de clés détermine l'ordre physique des lignes correspondantes dans une table. Le niveau inférieur (ou feuille) de l'index cluster contient les lignes de données réelles de la table. Une table ou une vue ne peut avoir qu'un seul index cluster à la fois. Pour plus d'informations, consultez Structures des index cluster.Une vue avec un index cluster unique est appelée une vue indexée. La création d'un index cluster unique sur une vue matérialise physiquement la vue. Un index cluster unique doit être créé sur une vue avant la définition de tout autre index sur cette même vue. Pour plus d'informations, consultez Conception des vues indexées.
Créez l'index cluster avant les index non cluster. Les index non cluster déjà existants sur les tables sont reconstruits lors de la création d'un index cluster.
Si vous ne spécifiez pas CLUSTERED, le système crée un index non cluster.
[!REMARQUE]
Le niveau feuille d'un index cluster et les pages de données étant par définition identiques, la création d'un index cluster et l'utilisation de la clause ON partition_scheme_name ou ON filegroup_name déplace effectivement une table du groupe de fichiers dans lequel la table a été créée vers le nouveau schéma de partition ou le nouveau groupe de fichiers. Avant de créer des tables ou des index sur des groupes de fichiers spécifiques, vérifiez quels sont les groupes de fichiers disponibles et s'ils disposent de suffisamment d'espace vide pour l'index. Pour plus d'informations, consultez Détermination de l'espace disque requis par les index.
NONCLUSTERED
Crée un index qui spécifie l'ordre logique d'une table. Avec un index non cluster, l'ordre physique des lignes de données est indépendant de l'ordre indexé. Pour plus d'informations, consultez Structures d'index non-cluster.Chaque table peut comporter jusqu'à 999 index non cluster, indépendamment de la façon dont les index sont créés : implicitement avec des contraintes PRIMARY KEY et UNIQUE ou explicitement avec CREATE INDEX.
Pour les vues indexées, les index non cluster peuvent être créés uniquement dans une vue ayant un index cluster unique déjà défini.
La valeur par défaut est NONCLUSTERED.
index_name
Nom de l'index. Les noms d'index doivent être uniques dans une table ou une vue, mais ne doivent pas être nécessairement uniques dans une base de données. Les noms d'index doivent se conformer aux règles régissant les identificateurs.column
Colonnes sur lesquelles l'index est basé. Spécifiez deux ou plusieurs noms de colonnes pour créer un index composite sur les valeurs combinées des colonnes spécifiées. Indiquez la liste des colonnes à inclure dans l'index composite, en fonction de l'ordre de priorité de tri, dans les parenthèses après table_or_view_name.Vous pouvez combiner jusqu'à 16 colonnes dans une même clé d'index composite. Toutes les colonnes d'une clé d'index composite doivent se trouver dans la même table ou la même vue. La taille maximale autorisée des valeurs d'index combinées est de 900 octets. Pour plus d'informations sur les colonnes de type variable figurant dans les index composites, consultez la section Remarque.
Les colonnes comportant des données de type LOB ntext, text, varchar(max), nvarchar(max), varbinary(max), xml ou image ne peuvent pas être spécifiées comme colonnes de clé d'un index. De même, une définition de vue ne peut pas contenir des colonnes ntext, text ni image, même si elles ne sont pas référencées dans l'instruction CREATE INDEX.
Vous pouvez créer des index sur des colonnes de type CLR définies par l'utilisateur si le type prend en charge le tri binaire. Vous pouvez également créer des index sur des colonnes calculées définies comme appels de méthodes d'une colonne de type défini par l'utilisateur, dès lors que les méthodes sont déterministes et n'exécutent pas des opérations d'accès aux données. Pour plus d'informations sur l'indexation des colonnes de type CLR définies par l'utilisateur, consultez Types CLR définis par l'utilisateur.
[ ASC | DESC ]
Détermine le sens croissant ou décroissant du tri d'une colonne d'index particulière. La valeur par défaut est ASC.INCLUDE **(**column [ ,... n ] )
Spécifie les colonnes non clés à ajouter au niveau feuille de l'index non cluster. L'index non cluster peut être unique ou non.Les noms de colonne ne peuvent pas être répétés dans la liste INCLUDE et ne peuvent pas être utilisés simultanément comme colonnes clés et colonnes non clés. Les index non cluster contiennent toujours les colonnes de l'index cluster si un index cluster est défini sur la table. Pour plus d'informations, consultez Index avec colonnes incluses.
Tous les types de données sont autorisés, à l'exception de text, ntext et image. L'index doit être créé ou reconstruit hors connexion (ONLINE = OFF) si des colonnes non clés quelconques spécifiées sont des types de données varchar(max), nvarchar(max) ou varbinary(max).
Les colonnes calculées déterministes et précises ou imprécises peuvent être des colonnes incluses. Les colonnes calculées dérivées des types de données image, ntext, text, varchar(max), nvarchar(max), varbinary(max) et xml peuvent être incluses dans des colonnes non-clés dès lors que les types de données des colonnes calculées peuvent être utilisés pour des colonnes incluses. Pour plus d'informations, consultez Création d'index sur des colonnes calculées.
Pour plus d'informations sur la création d'un index XML, consultez CREATE XML INDEX (Transact-SQL).
WHERE <filter_predicate>
Crée un index filtré en spécifiant les lignes à inclure dans l'index. L'index filtré doit être un index non cluster sur une table. Crée des statistiques filtrées pour les lignes de données dans l'index filtré.Le prédicat de filtre utilise une logique de comparaison simple et ne peut pas référencer une colonne calculée, une colonne UDT, une colonne de type de données spatiale ou une colonne de type de données hierarchyID. Les comparaisons à l'aide de littéraux Null ne sont pas autorisées avec les opérateurs de comparaison. Utilisez les opérateurs IS NULL et IS NOT NULL à la place. Les comparaisons et les listes IN peuvent être combinées uniquement à l'aide de l'opérateur AND.
Voici quelques exemples de prédicats de filtre pour la table Production.BillOfMaterials :
WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL
Les index filtrés ne s'appliquent pas aux index XML ni aux index de recherche en texte intégral. Pour les index UNIQUES, seules les lignes sélectionnées doivent avoir des valeurs d'index unique. Les index filtrés ne permettent pas d'utiliser l'option IGNORE_DUP_KEY.
ON partition_scheme_name**(column_name)**
Spécifie le schéma de partition qui définit les groupes de fichiers auxquels les partitions d'un index partitionné seront mappées. Le schéma de partition doit exister dans la base de données en exécutant CREATE PARTITION SCHEME ou ALTER PARTITION SCHEME. column_name spécifie la colonne par rapport à laquelle un index partitionné sera partitionné. Cette colonne doit correspondre au type de données, à la longueur et à la précision de l'argument de la fonction de partition qu'utilise partition_scheme_name. column_name n'est pas limité aux colonnes de la définition d'index. Toute colonne de la table de base peut être spécifiée, sauf lors du partitionnement d'un index UNIQUE ; le nom de colonne column_name doit être choisi parmi les noms de colonnes utilisés comme clés uniques. Cette restriction permet au Moteur de base de données de vérifier l'unicité des valeurs de clés dans une seule partition uniquement.[!REMARQUE]
Lorsque vous partitionnez un index cluster non unique, le Moteur de base de données ajoute par défaut la colonne de partitionnement à la liste des clés d'index cluster, si elle n'est pas déjà spécifiée. Lorsque vous partitionnez un index non cluster non unique, le Moteur de base de données ajoute la colonne de partitionnement sous la forme d'une colonne (incluse) non clé de l'index, si elle n'est pas déjà spécifiée.
Si partition_scheme_name ou filegroup n'est pas spécifié et que la table est partitionnée, l'index est placé dans le même schéma de partition que la table sous-jacente, en utilisant la même colonne de partitionnement.
[!REMARQUE]
Vous ne pouvez pas spécifier un schéma de partitionnement dans un index XML. Si la table de base est partitionnée, l'index XML utilise le même schéma de partition que la table. Pour plus d'informations sur la création d'un index XML, consultez CREATE XML INDEX (Transact-SQL).
Pour plus d'informations sur le partitionnement des index, consultez Consignes spéciales pour les index partitionnés.
ON filegroup_name
Crée l'index spécifié dans le groupe de fichiers spécifié. Si aucun emplacement n'est défini et que la table ou la vue n'est pas partitionnée, l'index utilise le même groupe de fichiers que la table ou la vue sous-jacente. Le groupe de fichiers doit déjà exister.ON "default"
Crée l'index spécifié sur le groupe de fichiers par défaut.Le terme « default », dans ce contexte, n'est pas un mot clé. Il s'agit de l'identificateur du groupe de fichiers par défaut et il doit être délimité, comme dans ON "default" ou ON [default]. Si « default » est spécifié, l'option QUOTED_IDENTIFIER doit avoir la value ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
Spécifie le positionnement de données FILESTREAM pour la table lorsqu'un index cluster est créé. La clause FILESTREAM_ON permet le déplacement des données FILESTREAM vers un schéma de partition ou un groupe de fichiers FILESTREAM différent.filestream_filegroup_name est le nom d'un groupe de fichiers FILESTREAM. Le groupe de fichiers doit avoir un fichier défini pour le groupe de fichiers à l'aide d'une instruction CREATE DATABASE ou ALTER DATABASE ; dans le cas contraire, une erreur est générée.
Si la table est partitionnée, la clause FILESTREAM_ON doit être incluse et doit spécifier un schéma de partition de groupes de fichiers FILESTREAM qui utilise les mêmes fonction de partition et colonnes de partition que le schéma de partition pour la table. Dans le cas contraire, une erreur est générée.
Si la table n'est pas partitionnée, la colonne FILESTREAM ne peut pas être partitionnée. Les données FILESTREAM pour la table doivent être stockées dans un groupe de fichiers unique spécifié dans la clause FILESTREAM_ON.
FILESTREAM_ON NULL peut être spécifié dans une instruction CREATE INDEX si un index cluster est créé et si la table ne contient pas de colonne FILESTREAM.
Pour obtenir la liste des rubriques relatives à FILESTREAM, consultez Conception et implémentation du stockage FILESTREAM.
<object>::=
Objet qualifié complet ou partiel à indexer.
database_name
Nom de la base de données.schema_name
Nom du schéma auquel la table ou la vue appartient.table_or_view_name
Nom de la table ou de la vue à indexer.La vue doit être définie avec SCHEMABINDING pour pouvoir créer un index sur celle-ci. Un index cluster unique doit être créé sur une vue avant la création de tout index non cluster. Pour plus d'informations sur les vues indexées, consultez la section Remarques.
<relational_index_option>::=
Spécifie les options à utiliser lorsque vous créez l'index.
PAD_INDEX = { ON | OFF }
Spécifie le remplissage de l'index. La valeur par défaut est OFF.ON
Le pourcentage d'espace libre indiqué par fillfactor est appliqué aux pages du niveau intermédiaire de l'index.OFF ou fillfactor n'est pas spécifié
Les pages de niveau intermédiaire sont presque entièrement remplies, ce qui laisse suffisamment d'espace libre pour au moins une ligne de la taille maximale permise par l'index, en prenant en compte l'ensemble de clés sur les pages intermédiaires.
L'option PAD_INDEX est utile seulement si FILLFACTOR est spécifié, car PAD_INDEX utilise le pourcentage spécifié par FILLFACTOR. Si le pourcentage défini pour FILLFACTOR n'est pas suffisamment élevé pour autoriser une ligne, le moteur de base de données remplace en interne le pourcentage de façon à ce qu'il autorise le minimum. Le nombre de lignes dans une page d'index intermédiaire n'est jamais inférieur à deux, quelle que soit la faiblesse de la valeur de fillfactor.
Dans la syntaxe de compatibilité descendante, WITH PAD_INDEX est équivalent à WITH PAD_INDEX = ON.
FILLFACTOR **=**fillfactor
Spécifie un pourcentage qui indique l'espace occupé par le niveau feuille de chaque page d'index opéré par le moteur de base de données lors de la création ou de la reconstruction d'index. fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0. Si fillfactor a pour valeur 100 ou 0, le moteur de base de données crée des index avec des pages de niveau feuille intégralement remplies.[!REMARQUE]
Les taux de remplissage 0 et 100 sont identiques en tous points.
La valeur FILLFACTOR s'applique uniquement lors de la création ou de la reconstruction de l'index. Le moteur de base de données ne conserve pas dynamiquement dans les pages le pourcentage d'espace libre défini. Pour afficher le taux de remplissage, utilisez l'affichage catalogue sys.indexes.
Important
La création d'un index cluster avec un taux de remplissage inférieur à 100 affecte la quantité d'espace de stockage qu'occupent les données, car le moteur de base de données redistribue les données lorsqu'il crée l'index cluster.
Pour plus d'informations, consultez Facteur de remplissage.
SORT_IN_TEMPDB = { ON | OFF }
Spécifie si les résultats temporaires du tri doivent être stockés dans tempdb. La valeur par défaut est OFF.ON
Les résultats de tri intermédiaires utilisés pour créer l'index sont stockés dans tempdb. Ceci peut accélérer la création d'un index si tempdb ne se trouve pas sur le même groupe de disques que la base de données utilisateur. Toutefois, une plus grande quantité d'espace disque est alors utilisée lors de la création de l'index.OFF
Les résultats de tri intermédiaires sont stockés dans la même base de données que l'index.
Outre l'espace nécessaire dans la base de données utilisateur pour créer l'index, tempdb doit disposer à peu près du même espace supplémentaire pour stocker les résultats de tri intermédiaires. Pour plus d'informations, consultez tempdb et création d'index.
Dans la syntaxe de compatibilité descendante, WITH SORT_IN_TEMPDB est équivalent à WITH SORT_IN_TEMPDB = ON.
IGNORE_DUP_KEY = { ON | OFF }
Spécifie la réponse d'erreur lorsqu'une opération d'insertion essaie d'insérer des valeurs de clés en double dans un index unique. L'option IGNORE_DUP_KEY s'applique uniquement aux opérations d'insertion après la création ou la régénération de l'index. Cette option n'a aucun effet lors de l'exécution de CREATE INDEX, ALTER INDEX ou de UPDATE. La valeur par défaut est OFF.ON
Un message d'avertissement s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. Seules les lignes qui violent la contrainte d'unicité échouent.OFF
Un message d'erreur s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. L'intégralité de l'opération INSERT sera restaurée.
IGNORE_DUP_KEY ne peut pas être activé (ON) dans le cas d'index créés sur une vue, d'index non uniques, d'index XML, d'index spatiaux et d'index filtrés.
Pour afficher IGNORE_DUP_KEY, utilisez sys.indexes.
Dans la syntaxe de compatibilité descendante, WITH IGNORE_DUP_KEY est équivalent à WITH IGNORE_DUP_KEY = ON.
STATISTICS_NORECOMPUTE = { ON | OFF}
Spécifie si les statistiques de distribution sont recalculées. La valeur par défaut est OFF.ON
Les statistiques obsolètes ne sont pas recalculées automatiquement.OFF
La mise à jour automatique des statistiques est activée.
Pour restaurer la mise à jour automatique des statistiques, affectez la valeur OFF à STATISTICS_NORECOMPUTE, ou exécutez UPDATE STATISTICS sans la clause NORECOMPUTE.
Important
La désactivation du recalcul automatique des statistiques de distribution peut empêcher l'optimiseur de requête de sélectionner des plans d'exécution optimaux pour les requêtes qui impliquent la table.
Dans la syntaxe de compatibilité descendante, WITH STATISTICS_NORECOMPUTE est équivalent à WITH STATISTICS_NORECOMPUTE = ON.
DROP_EXISTING = { ON | OFF }
Spécifie que l'index cluster ou non cluster nommé préexistant est supprimé et recréé. La valeur par défaut est OFF.ON
L'index existant est supprimé et recréé. Le nom d'index défini doit être identique à celui de l'index existant. Toutefois, la définition de l'index peut être modifiée. Par exemple, vous pouvez définir des colonnes, un ordre de tri, un schéma de partition ou des options d'indexation différentes.OFF
Une erreur s'affiche si le nom d'index spécifié existe déjà.
Le type d'index ne peut pas être modifié à l'aide de DROP_EXISTING.
Dans la syntaxe de compatibilité descendante, WITH DROP_EXISTING est équivalent à WITH DROP_EXISTING = ON..
ONLINE = { ON | OFF }
Indique si les tables sous-jacentes et les index associés sont disponibles pour les requêtes et la modification des données au cours de l'indexation. La valeur par défaut est OFF.[!REMARQUE]
Les opérations d'index en ligne ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.
ON
Les verrous de table à long terme ne sont pas maintenus pendant la durée de l'opération d'index. Lors de la principale phase de l'indexation, seul le verrou de partage intentionnel (IS, Intent Share) est maintenu sur la table source. Ceci permet d'exécuter les requêtes ou les mises à jour dans la table sous-jacente et ses index. Au début de l'opération, un verrou partagé (S, Shared) est placé sur l'objet source pendant une période de temps très courte. À la fin de l'opération, pendant une période de temps très courte, un verrou partagé (S, Shared) est placé sur la source si un index non cluster est créé, ou bien un verrou de SCH-M (Modification du schéma) est placé lorsqu'un index cluster est créé ou supprimé en ligne et lorsqu'un index cluster ou non cluster est régénéré. ONLINE ne peut pas avoir la valeur ON lors de la création d'un index sur une table temporaire locale.OFF
Des verrous de table sont appliqués pendant l'opération d'indexation. Une opération d'indexation hors ligne qui crée, régénère ou supprime un index cluster, ou régénère ou supprime un index non-cluster, acquiert un verrou de modification de schéma (Sch-M) sur la table. Ceci empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération. Une opération d'indexation hors ligne qui crée un index non cluster acquiert un verrou partagé (S, Shared) sur la table. Cela empêche la mise à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que des instructions SELECT.
Pour plus d'informations, consultez Fonctionnement des opérations d'index en ligne. Pour plus d'informations sur les verrous, consultez Modes de verrouillage.
Les index, y compris les index des tables temporaires globales, peuvent être créés en ligne, à l'exception des index suivants :
index XML ;
index de table temporaire locale ;
index cluster unique initial sur une vue ;
index cluster désactivés ;
index cluster si la table sous-jacente contient des types de données LOB : image, ntext, text, varchar(max), nvarchar(max), varbinary(max) et xml ;
index non cluster défini avec des colonnes de type de données LOB.
[!REMARQUE]
Un index non cluster non unique peut être créé en ligne si la table contient des types de données LOB mais qu'aucune de ces colonnes n'est utilisée dans la définition d'index comme colonne (incluse) clé ou non-clés.
Pour plus d'informations, consultez Exécution d'opérations en ligne sur les index.
ALLOW_ROW_LOCKS = { ON | OFF }
Spécifie si les verrous de ligne sont autorisés. La valeur par défaut est ON.ON
Les verrous de ligne sont autorisés lors de l'accès à l'index. Le moteur de base de données détermine le moment où les verrous de ligne sont utilisés.OFF
Les verrous de ligne ne sont pas utilisés.
ALLOW_PAGE_LOCKS = { ON | OFF }
Spécifie si les verrous de page sont autorisés. La valeur par défaut est ON.ON
Les verrous de page sont autorisés lors de l'accès à l'index. Le moteur de base de données détermine le moment où les verrous de page sont utilisés.OFF
Les verrous de page ne sont pas utilisés.
MAXDOP = max_degree_of_parallelism
Remplace l'option de configuration max degree of parallelism pendant la durée de l'indexation. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plan parallèle. Le nombre maximal de processeurs est 64.Valeurs possibles de max_degree_of_parallelism :
1
Supprime la génération de plan parallèle.>1
Limite le nombre maximal de processeurs utilisés dans l'indexation parallèle au nombre défini ou à un nombre inférieur en fonction de la charge de travail actuelle du système.0 (valeur par défaut)
Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.
Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.
[!REMARQUE]
Les opérations d'index parallèles ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.
DATA_COMPRESSION
Spécifie l'option de compression de données pour l'index, le numéro de partition ou la plage de partitions spécifiés. Les options disponibles sont les suivantes :NONE
L'index ou les partitions spécifiées ne sont pas compressés.ROW
L'index ou les partitions spécifiées sont compressés au moyen de la compression de ligne.PAGE
L'index ou les partitions spécifiées sont compressés au moyen de la compression de page.
Pour plus d'informations sur la compression, consultez Création de tables et d'index compressés.
ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
Spécifie les partitions auxquelles le paramètre DATA_COMPRESSION s'applique. Si l'index n'est pas partitionné, l'argument ON PARTITIONS générera une erreur. Si la clause ON PARTITIONS n'est pas fournie, l'option DATA_COMPRESSION s'applique à toutes les partitions d'un index partitionné.<partition_number_expression> peut être spécifié des manières suivantes :
Spécifiez le numéro d'une partition, par exemple : ON PARTITIONS (2).
Spécifiez des numéros de partition pour plusieurs partitions individuelles séparées par des virgules, par exemple : ON PARTITIONS (1, 5).
Spécifiez à la fois des plages et des partitions individuelles, par exemple ON PARTITIONS (2, 4, 6 TO 8).
<range> peut être spécifié sous la forme de numéros de partitions séparés par le mot TO, par exemple ON PARTITIONS (6 TO 8).
Pour définir des types différents de compression de données pour des partitions différentes, spécifiez plusieurs fois l'option DATA_COMPRESSION, par exemple :
REBUILD WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )
Notes
L'instruction CREATE INDEX est optimisée comme toute autre requête. Pour consommer moins de ressources sur les opérations d'E/S, le processeur de requêtes peut choisir d'analyser un autre index au lieu d'effectuer une analyse de la table. L'opération de tri peut être éliminée dans certains cas. Sur des ordinateurs multiprocesseurs exécutant SQL Server 2005 Enterprise Edition ou SQL Server 2008, CREATE INDEX peut utiliser plusieurs processeurs pour exécuter les opérations d'analyse et de tri associées à la création de l'index, à l'instar des autres requêtes. Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.
L'opération de création d'index peut être consignée de manière minimale si le mode de récupération de base de données correspond au mode de journalisation en bloc ou au mode simple. Pour plus d'informations, consultez Choix d'un mode de récupération pour des opérations d'index.
Vous pouvez créer des index sur une table temporaire. Lorsque la table est supprimée ou que la session prend fin, les index sont supprimés.
Les index prennent en charge les propriétés étendues. Pour plus d'informations, consultez Utilisation de propriétés étendues sur les objets de base de données.
Index cluster
La création d'un index cluster sur une table (segment) ou la suppression et la recréation d'un index nécessite un espace de travail supplémentaire dans la base de données pour pouvoir y placer le tri des données et une copie temporaire de la table d'origine ou les données d'index cluster existantes. Pour plus d'informations, consultez Détermination de l'espace disque requis par les index. Pour plus d'informations sur les index cluster, consultez Création d'index cluster.
Index uniques
Lorsqu'un index unique existe, le moteur de base de données recherche les valeurs dupliquées chaque fois que des données sont ajoutées par des opérations d'insertion. Les opérations d'insertion qui génèrent des valeurs de clés dupliquées sont annulées, et le moteur de base de données affiche un message d'erreur. Ceci s'applique même si l'insertion modifie un grand nombre de lignes et ne génère qu'une seule valeur dupliquée. Si vous tentez d'entrer des données pour lesquelles il existe un index unique et que la clause IGNORE_DUP_KEY a la valeur ON, seules les lignes qui violent l'index UNIQUE échouent. Pour plus d'informations sur les index uniques, consultez Création d'index uniques.
Index partitionnés
Les index partitionnés sont créés et gérés pratiquement comme des tables partitionnées, mais comme les index ordinaires, ils sont gérés sous forme d'objets de base de données distincts. Un index partitionné peut être créé sur une table non partitionnée, et une table partitionnée peut avoir un index non partitionné.
Si vous créez un index sur une table partitionnée et que ne spécifiez pas un groupe de fichiers pour y placer l'index, l'index est partitionné de la même manière que la table sous-jacente. Ceci s'explique par le fait que les index sont placés par défaut dans les mêmes groupes de fichiers que leurs tables sous-jacentes, et pour une table partitionnée, dans le même schéma de partition qui utilise les mêmes colonnes de partitionnement.
Lorsque vous partitionnez un index cluster non unique, le Moteur de base de données ajoute par défaut des colonnes de partitionnement à la liste des clés d'index cluster, si elles ne sont pas déjà spécifiées.
Vous pouvez créer des vues indexées sur des tables partitionnées, en appliquant la même procédure que celle utilisée pour les index sur des tables. Pour plus d'informations sur les index partitionnés, consultez Tables et index partitionnés.
Vues indexées
La création d'un index cluster unique sur une vue améliore les performances des requêtes, car la vue est stockée dans la base de données au même titre qu'une table avec un index cluster. L'optimiseur de requête peut utiliser des vues indexées pour accélérer l'exécution des requêtes. Il n'est pas nécessaire de référencer la vue dans la requête pour que l'optimiseur envisage d'utiliser cette vue.
Vous devez procéder comme suit pour créer une vue indexée et l'implémenter correctement :
Vérifiez que les options SET sont correctes pour toutes les tables existantes qui seront référencées dans la vue.
Vérifiez que les options SET de la session sont définies correctement avant de créer des tables et la vue.
Vérifiez que la définition de la vue est déterministe.
Créez la vue avec l'option WITH SCHEMABINDING.
Créez l'index cluster unique sur la vue.
Options SET requises pour les vues indexées
L'évaluation de la même expression peut produire des résultats différents dans le Moteur de base de données si des options SET différentes sont actives lors de l'exécution de la requête. Par exemple, si l'option SET CONCAT_NULL_YIELDS_NULL est activée (ON), l'expression 'abc' + NULL retourne la valeur Null. Cependant, si l'option CONCAT_NULL_YIEDS_NULL est désactivée (OFF), la même expression retourne 'abc'.
Pour pouvoir gérer correctement les vues et retourner des résultats cohérents, les vues indexées nécessitent des valeurs fixes pour plusieurs options SET. Les options SET de la table suivante doivent être définies avec les valeurs indiquées dans les colonnes Valeurrequise chaque fois que les conditions suivantes sont réalisées :
La vue indexée est créée.
Une insertion, une mise à jour ou une suppression est exécutée sur une table qui participe à la vue indexée. Ceci inclut des opérations telles que la copie en bloc, la réplication et les requêtes distribuées.
L'optimiseur de requête utilise la vue indexée pour générer le plan de requête.
Options SET
Valeur requise
Valeur de serveur par défaut
Valeur OLE DB et ODBC
par défaut
Valeur Bibliothèque DB-Library
par défaut
ANSI_NULLS
ON
ON
ON
OFF
ANSI_PADDING
ON
ON
ON
OFF
ANSI_WARNINGS*
ON
ON
ON
OFF
ARITHABORT
ON
ON
OFF
OFF
CONCAT_NULL_YIELDS_NULL
ON
ON
ON
OFF
NUMERIC_ROUNDABORT
OFF
OFF
OFF
OFF
QUOTED_IDENTIFIER
ON
ON
ON
OFF
*L'affectation de la valeur ON à ANSI_WARNINGS affecte de manière implicite la valeur ON à ARITHABORT, lorsque le niveau de compatibilité de la base de données est d'au moins 90. Si le niveau de compatibilité de la base de données est au maximum de 80, la valeur ON doit être affectée de manière explicite à l'option ARITHABORT.
Si vous utilisez une connexion serveur OLE DB ou ODBC, la seule valeur qui doit être modifiée est ARITHABORT. Toutes les valeurs DB-Library doivent être définies correctement au niveau du serveur avec sp_configure, ou depuis l'application avec la commande SET. Pour plus d'informations sur les options SET, consultez Utilisation d'options dans SQL Server.
Important
Il est vivement conseillé d'affecter la valeur ON à l'option utilisateur ARITHABORT sur le serveur dès la création de la première vue indexée ou du premier index sur une colonne calculée dans une base de données du serveur.
Fonctions déterministes
La définition d'une vue indexée doit être déterministe. Une vue est déterministe si toutes les expressions de la liste de sélection ainsi que les clauses WHERE et GROUP BY sont déterministes. Les expressions déterministes retournent toujours le même résultat chaque fois qu'elles sont évaluées avec un groupe de valeurs d'entrée spécifiques. Seules les fonctions déterministes peuvent participer à des expressions déterministes. Par exemple, la fonction DATEADD est déterministe, car elle retourne toujours le même résultat pour un groupe donné de valeurs d'arguments pour ses trois paramètres. La fonction GETDATE n'est pas déterministe, car elle est toujours appelée avec le même argument, mais la valeur qu'elle retourne change chaque fois qu'elle est exécutée. Pour plus d'informations, consultez Fonctions déterministes et non déterministes.
Même si une expression est déterministe, si elle contient des expressions flottantes, le résultat exact dépend de l'architecture du processeur ou de la version du microcode. Pour garantir l'intégrité des données, ces expressions peuvent participer seulement sous forme de colonnes non clés de vues indexées. Les expressions déterministes qui ne contiennent pas d'expressions flottantes s'appellent des expressions précises. Seules les expressions déterministes précises peuvent participer à des colonnes clés et dans les clauses WHERE et GROUP BY des vues indexées.
Utilisez la propriété IsDeterministic de la fonction COLUMNPROPERTY pour déterminer si une colonne de vue est déterministe. Utilisez la propriété IsPrecise de la fonction COLUMNPROPERTY pour déterminer si une colonne déterministe d'une vue avec une liaison de schéma est précise. COLUMNPROPERTY retourne 1 pour TRUE, 0 pour FALSE et NULL pour une entrée non valide. Cela signifie que la colonne n'est pas déterministe ou pas précise.
Autres conditions requises
Outre les options SET et les conditions requises pour les fonctions déterministes, les conditions suivantes doivent être satisfaites :
L'utilisateur qui exécute CREATE INDEX doit être le propriétaire de la vue.
Si la définition de la vue contient une clause GROUP BY, la clé de l'index cluster unique peut référencer seulement les colonnes définies dans la clause GROUP BY.
Les options SET appropriées doivent être définies pour les tables de base lors de la création de la table : sans cela, elle ne peut pas être référencée par la vue avec la liaison de schéma.
Les tables doivent être référencées par des noms en deux parties, schema**.**tablename, dans la définition de la vue.
Les fonctions définies par l'utilisateur doivent être créées avec l'option WITH SCHEMABINDING.
Les fonctions définies par l'utilisateur doivent être référencées par des noms en deux parties (schema**.**function).
La vue doit être créée avec l'option WITH SCHEMABINDING.
La vue doit référencer seulement des tables de base de la même base de données, et non pas d'autres vues.
La définition de la vue ne doit pas contenir les éléments suivants :
COUNT(*)
fonction ROWSET
table dérivée
auto-jointure
DISTINCT
STDEV, VARIANCE, AVG
colonnes float*, text, ntext ou image
sous-requête
prédicats de texte intégral (CONTAIN, FREETEXT)
SUM ou expression acceptant les valeurs NULL
fonction d'agréation CLR définie par l'utilisateur
TOP
MIN, MAX
UNION
*La vue indexée peut contenir des colonnes float, mais ces colonnes ne peuvent pas être incluses dans la clé d'index cluster.
Si la clause GROUP BY est présente, la définition VIEW doit contenir COUNT_BIG(*), mais pas HAVING. Ces restrictions de GROUP BY sont applicables seulement à la définition de la vue indexée. Une requête peut utiliser une vue indexée dans son plan d'exécution, même si elle ne répond pas à ces restrictions de GROUP BY.
Des vues indexées peuvent être créées sur une table partitionnée, et elles peuvent elles-mêmes être partitionnées. Pour plus d'informations sur le partitionnement, consultez la section précédente « Index partitionnés ».
Pour empêcher le Moteur de base de données d'utiliser des vues indexées, utilisez l'indicateur OPTION (EXPAND VIEWS) dans la requête. En outre, si des options sont définies incorrectement, l'optimiseur ne peut pas utiliser les index des vues. Pour plus d'informations sur l'indicateur OPTION (EXPAND VIEWS), consultez SELECT (Transact-SQL).
Le niveau de compatibilité de la base de données ne peut pas être inférieur à 80. Il n'est pas possible de définir un niveau de compatibilité inférieur à 80 pour une base de données contenant une vue indexée.
Index filtrés
Un index filtré est un index non cluster optimisé, approprié pour les requêtes qui sélectionnent un faible pourcentage de lignes d'une table. Il utilise un prédicat de filtre pour indexer une partie des données de la table. Un index filtré bien conçu peut améliorer les performances des requêtes, réduire les coûts de stockage et réduire les coûts de maintenance.
Options SET requises pour les index filtrés
Les options SET figurant dans la colonne Required Value sont requises chaque fois qu'une des conditions suivantes est vérifiée :
Vous créez un index filtré.
L'opération INSERT, UPDATE, DELETE ou MERGE modifie les données dans un index filtré.
L'optimiseur de requête utilise l'index filtré dans le plan d'exécution de requête.
Options SET
Valeur requise
ANSI_NULLS
ON
ANSI_PADDING
ON
ANSI_WARNINGS*
ON
ARITHABORT
ON
CONCAT_NULL_YIELDS_NULL
ON
NUMERIC_ROUNDABORT
OFF
QUOTED_IDENTIFIER
ON
*L'affectation de la valeur ON à ANSI_WARNINGS affecte de manière implicite la valeur ON à ARITHABORT, lorsque le niveau de compatibilité de la base de données est d'au moins 90. Si le niveau de compatibilité de la base de données est au maximum de 80, la valeur ON doit être affectée de manière explicite à l'option ARITHABORT.
Si les options SET sont incorrectes, les conditions suivantes peuvent se vérifier :
L'index filtré n'est pas créé.
Le moteur de base de données génère une erreur et restaure les instructions INSERT, UPDATE DELETE ou MERGE qui modifient les données incluses dans l'index.
L'optimiseur de requête ne prend en compte l'index dans le plan d'exécution pour aucune instruction Transact-SQL.
Pour plus d'informations sur les index filtrés, consultez Règles de conception d'index filtrés.
Index spatiaux
Pour plus d'informations sur les index spatiaux, consultez CREATE SPATIAL INDEX (Transact-SQL) et Utilisation d'index spatiaux (moteur de base de données).
Index XML
Pour plus d'informations sur les index XML, consultez CREATE XML INDEX (Transact-SQL) et Index sur les colonnes de type de données XML.
Taille de clé d'index
La taille maximale d'une clé d'index est de 900 octets. Vous pouvez créer des index de plus de 900 octets sur des colonnes varchar si les données existantes des colonnes ne dépassent pas 900 octets lors de la création de l'index. Cependant, les insertions ou les mises à jour suivantes dans les colonnes aboutissant à une taille totale supérieure à 900 octets échouent. Pour plus d'informations, consultez Taille maximale des clés d'index. La clé d'un index cluster ne peut pas contenir des colonnes varchar ayant des données dans l'unité d'allocation ROW_OVERFLOW_DATA. Si un index cluster est créé sur une colonne varchar et que les données existantes se trouvent dans l'unité d'allocation IN_ROW_DATA, les insertions et les mises à jour suivantes dans la colonne qui envoient les données hors ligne échouent. Pour plus d'informations sur les unités d'allocation, consultez Organisation des tables et des index.
Les index non cluster peuvent contenir des colonnes non-clés au niveau feuille de l'index. Le moteur de base de données ne tient pas compte de ces colonnes lors du calcul de la taille de la clé d'index. Pour plus d'informations, consultez Index avec colonnes incluses.
Colonnes calculées
Des index peuvent être créés sur des colonnes calculées. En outre, les colonnes calculées peuvent avoir la propriété PERSISTED. Cela signifie que le moteur de base de données stocke les valeurs calculées dans la table et qu'il les met à jour lorsque les autres colonnes dont dépendent les colonnes calculées sont mises à jour. Le Moteur de base de données utilise ces valeurs persistantes pour créer un index sur la colonne et lorsqu'une requête fait référence à l'index.
Pour qu'il soit possible de créer un index d'une colonne calculée, celle-ci doit être déterministe et précise. Cependant, l'utilisation de la propriété PERSISTED permet d'étendre le type des colonnes calculées indexables pour inclure :
les colonnes calculées basées sur les fonctions Transact-SQL et CLR, et les méthodes de type CLR définies par l'utilisateur que l'utilisateur marque comme étant déterministes ;
les colonnes calculées basées sur des expressions déterministes, comme défini par le moteur de base de données, mais qui ne sont pas précises.
Les colonnes calculées persistantes nécessitent de définir les options SET ci-dessous comme indiqué dans la section précédente « Options SET requises pour les vues indexées ».
La contrainte UNIQUE ou PRIMARY KEY peut contenir une colonne calculée dès lors qu'elle satisfait à toutes les conditions d'indexation. En particulier, la colonne calculée doit être déterministe et précise, ou déterministe et permanente. Pour plus d'informations sur le déterminisme, consultez Fonctions déterministes et non déterministes.
Les colonnes calculées dérivées des types de données image, ntext, text, varchar(max), nvarchar(max), varbinary(max) et xml peuvent être indexées en tant que colonnes clés ou colonnes non clés incluses dès lors que les types de données des colonnes calculées peuvent être utilisés pour des colonnes clés ou des colonnes non clés d'index. Par exemple, vous ne pouvez pas créer un index XML primaire sur une colonne xml calculée. Si la taille de la clé d'index est supérieure à 900 octets, un message d'avertissement est affiché.
La création d'un index sur une colonne calculée peut provoquer l'échec d'une opération d'insertion ou de mise à jour qui fonctionnait auparavant. Ce type d'échec peut survenir lorsque la colonne calculée génère une erreur arithmétique. Par exemple, dans la table suivante, bien que la colonne calculée c retourne une erreur arithmétique, l'instruction INSERT fonctionne.
CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);
Par contre, si après avoir créé la table, vous créez un index sur la colonne calculée c, la même instruction INSERT va échouer.
CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);
Pour plus d'informations, consultez Création d'index sur des colonnes calculées.
Colonnes incluses dans les index
Des colonnes non clés, appelées colonnes incluses, peuvent être ajoutées au niveau feuille d'un index non cluster pour améliorer les performances d'une requête en couvrant la requête. En l'occurrence, toutes les colonnes référencées dans la requête sont incluses dans l'index sous forme de colonnes clés ou de colonnes non clés. Ainsi, l'optimiseur de requête peut rechercher toutes les informations nécessaires via une analyse de l'index ; il n'accède pas à la table, ni aux données de l'index cluster. Pour plus d'informations, consultez Index avec colonnes incluses.
Définition des options d'index
SQL Server 2005 a introduit de nouvelles options d'index et modifié également la façon dont les options sont spécifiées. Dans la syntaxe de compatibilité descendante, WITH option_name est équivalent à WITH ( <option_name> = ON ). Lorsque vous définissez les options d'index, les règles suivantes s'appliquent :
Les nouvelles options d'index peuvent être définies uniquement en utilisant WITH (option_name= ON | OFF**)**.
Vous ne pouvez pas définir les options en utilisant la syntaxe de compatibilité descendante et la nouvelle syntaxe dans une même instruction. Par exemple, si vous définissez WITH (DROP_EXISTING, ONLINE = ON**)**, l'instruction échoue.
Lorsque vous créez un index XML, les options doivent être définies en utilisant WITH (option_name= ON | OFF**)**.
Clause DROP_EXISTING
Vous pouvez utiliser la clause DROP_EXISTING pour régénérer l'index, ajouter ou supprimer des colonnes, modifier des options, modifier l'ordre de tri des colonnes ou modifier le schéma de partition ou le groupe de fichiers.
Si l'index applique une contrainte PRIMARY KEY ou UNIQUE et que sa définition n'est pas modifiée, l'index est supprimé et recréé en conservant la contrainte existante. Toutefois, si la définition de l'index est modifiée, l'instruction échoue. Pour changer la définition d'une contrainte PRIMARY KEY ou UNIQUE, supprimez la contrainte et ajoutez une contrainte avec la nouvelle définition.
DROP_EXISTING améliore les performances lorsque vous recréez un index cluster, avec le même groupe de clés ou un groupe de clés différent, sur une table qui contient également des index non cluster. DROP_EXISTING remplace l'exécution d'une instruction DROP INDEX dans l'ancien index cluster, suivie de l'exécution d'une instruction CREATE INDEX pour le nouvel index cluster. Les index non cluster sont régénérés une fois, et ensuite seulement si la définition d'index est modifiée. La clause DROP_EXISTING ne régénère pas les index non cluster lorsque la définition d'index porte le même nom d'index, a les mêmes colonnes clé et de partition, le même attribut d'unicité et le même ordre de tri que l'index d'origine.
Que les index non cluster soient régénérés ou non, ils restent toujours dans leur groupes de fichiers ou schémas de partition d'origine et utilisent les fonctions de partition d'origine. Si un index cluster est régénéré dans un groupe de fichiers ou un schéma de partition différent, les index non cluster ne sont pas déplacés pour coïncider avec le nouvel emplacement de l'index cluster. Par conséquent, même si les index non cluster ont été alignés sur l'index cluster, ils peuvent ne plus être alignés avec celui-ci. Pour plus d'informations sur l'alignement des index partitionnés, consultez Consignes spéciales pour les index partitionnés.
La clause DROP_EXISTING ne retrie pas les données si les mêmes colonnes de clé d'index sont utilisées dans le même ordre et avec le même ordre croissant ou descendant, sauf si l'instruction d'indexation spécifie un index non cluster et que l'option ONLINE est désactivée (OFF). Si l'index cluster est désactivé, l'opération CREATE INDEX WITH DROP_EXISTING doit être exécutée avec l'option ONLINE désactivée (OFF). Si un index non cluster est désactivé et n'est pas associé à un index cluster désactivé, l'opération CREATE INDEX WITH DROP_EXISTING peut être exécutée avec l'option ONLINE désactivée (OFF) ou activée (ON).
Lorsque des index avec 128 extensions ou plus sont supprimés ou régénérés, le moteur de base de données diffère les désallocations de page actuelles et leurs verrous associés jusqu'à la validation de la transaction. Pour plus d'informations, consultez Suppression et reconstruction d'objets volumineux.
Option ONLINE
Les instructions suivantes s'appliquent aux opérations d'indexation en ligne :
La table sous-jacente ne peut pas être modifiée, tronquée ou supprimée tant qu'une opération d'indexation en ligne est en cours.
Un espace disque temporaire supplémentaire est nécessaire au cours de l'opération d'indexation. Pour plus d'informations, consultez Détermination de l'espace disque requis par les index.
Des opérations en ligne peuvent être exécutées sur les index partitionnés et sur les index qui contiennent des colonnes calculées persistantes ou des colonnes incluses.
Pour plus d'informations, consultez Exécution d'opérations en ligne sur les index.
Options de verrous de ligne et de page
Lorsque ALLOW_ROW_LOCKS = ON et ALLOW_PAGE_LOCK = ON, les verrous de ligne, de page et de table sont autorisés lors de l'accès à l'index. Le Moteur de base de données choisit le verrou approprié et peut convertir un verrou de ligne ou de page en verrou de table. Pour plus d'informations, consultez Escalade de verrous (moteur de base de données).
Lorsque ALLOW_ROW_LOCKS = OFF et ALLOW_PAGE_LOCK = OFF, seul un verrou de table est autorisé lors de l'accès à l'index.
Pour plus d'informations sur la définition de la granularité des verrous d'un index, consultez Personnalisation du verrouillage pour un index.
Affichage des informations d'index
Pour retourner des informations sur les index, vous pouvez utiliser des affichages catalogue, des fonctions système et des procédures stockées système. Pour plus d'informations, consultez Affichage des informations sur les index.
Compression de données
La compression de données est décrite dans la rubrique Création de tables et d'index compressés. Voici les points clés à prendre en compte :
La compression permet de stocker un plus grand nombre de lignes dans une page, mais ne modifie pas la taille maximale des lignes.
Les pages non-feuille d'un index ne sont pas compressées par le biais de la compression de page, mais par le biais de la compression de ligne.
Chaque index non cluster dispose d'un paramètre de compression individuel et n'hérite pas du paramètre de compression de la table sous-jacente.
Lorsqu'un index cluster est créé sur un segment de mémoire, l'index cluster hérite de l'état de compression du segment, à moins qu'un autre état de compression soit spécifié.
Les restrictions suivantes s'appliquent aux index partitionnés :
Vous ne pouvez pas modifier le paramètre de compression d'une partition unique si la table possède des index non alignés.
La syntaxe ALTER INDEX <index> ... REBUILD PARTITION ... reconstruit la partition spécifiée de l'index.
La syntaxe ALTER INDEX <index> ... REBUILD WITH ... reconstruit toutes les partitions de l'index.
Pour évaluer la façon dont la modification de l'état de compression affectera une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings.
Autorisations
Nécessite l'autorisation ALTER sur la table ou la vue. L'utilisateur doit être membre du rôle de serveur fixe sysadmin ou des rôles de base de données fixes db_ddladmin et db_owner.
Exemples
A. Création d'un index non cluster simple
L'exemple suivant crée un index non cluster sur la colonne VendorID de la table Purchasing.ProductVendor .
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID);
GO
B. Création d'un index composite non cluster simple
L'exemple suivant crée un index composite non cluster sur les colonnes SalesQuota et SalesYTD de la table Sales.SalesPerson.
USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO
C. Création d'un index non cluster unique
L'exemple suivant crée un index non cluster unique sur la colonne Name de la table Production.UnitMeasure. L'index applique la contrainte d'unicité sur les données insérées dans la colonne Name.
USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'AK_UnitMeasure_Name')
DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name
ON Production.UnitMeasure(Name);
GO
La requête suivante teste la contrainte d'unicité en tentant d'insérer une ligne avec une valeur existant dans une autre ligne.
--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
VALUES ('OC', 'Ounces', GetDate());
Le message d'erreur retourné est :
Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.
D. Utilisation de l'option IGNORE_DUP_KEY
L'exemple suivant montre l'effet de l'option IGNORE_DUP_KEY en insérant plusieurs lignes dans une table temporaire avec cette option d'abord définie sur ON, puis sur OFF. Une ligne est insérée dans la table #Test pour créer intentionnellement une valeur dupliquée lorsque la deuxième instruction INSERT, qui va insérer plusieurs lignes, est exécutée. Un compteur de lignes de la table retourne le nombre de lignes insérées.
USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO
Voici les résultats de la deuxième instruction INSERT.
Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.
Number of rows
--------------
38
Notez que les lignes insérées depuis la table Production.UnitMeasure qui ne violent pas la contrainte d'unicité ont été correctement insérées. Un avertissement est émis, et la ligne dupliquée est ignorée ; l'ensemble de la transaction n'a pas été annulé.
Les mêmes instructions sont exécutées de nouveau, mais avec IGNORE_DUP_KEY défini par la valeur OFF.
USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO
Voici les résultats de la deuxième instruction INSERT.
Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.
Number of rows
--------------
1
Notez qu'aucune des lignes de la table Production.UnitMeasure n'a été insérée dans la table, alors qu'une ligne de la table seulement a violé la contrainte UNIQUE de l'index.
E. Utilisation de DROP_EXISTING pour supprimer et recréer un index
L'exemple suivant supprime et recrée un index existant sur la colonne ProductID de la table Production.WorkOrder avec l'option DROP_EXISTING. Les options FILLFACTOR et PAD_INDEX sont également définies.
USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON);
GO
F. Création d'un index sur une vue
L'exemple suivant crée une vue et un index sur cette vue. Deux requêtes sont incluses et utilisent la vue indexée.
USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND ProductID BETWEEN 700 and 800
AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO
G. Création d'un index avec des colonnes incluses (non clés)
L'exemple suivant crée un index non cluster avec une colonne clé (PostalCode) et quatre colonnes non clés (AddressLine1, AddressLine2, City, StateProvinceID). Une requête couverte par l'index suit. Pour afficher l'index sélectionné par l'optimiseur de requête, dans le menu Requête de SQL Server Management Studio, sélectionnez Inclure le plan d'exécution réel avant d'exécuter la requête.
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_Address_PostalCode')
DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO
H. Création d'un index partitionné
L'exemple suivant crée un index partitionné non cluster sur TransactionsPS1, un schéma de partition existant.
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO
I. Création d'un index filtré
L'exemple suivant crée un index filtré sur la table Production.BillOfMaterials. Le prédicat de filtre peut inclure des colonnes qui ne sont pas des colonnes clés dans l'index filtré. Dans cet exemple, le prédicat sélectionne uniquement les lignes où EndDate n'est pas NULL.
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithEndDate'
AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
GO
J. Création d'un index compressé
L'exemple ci-dessous illustre la création d'un index sur une table non partitionnée à l'aide de la compression de ligne.
CREATE NONCLUSTERED INDEX IX_INDEX_1
ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
L'exemple ci-dessous illustre la création d'un index sur une table partitionnée à l'aide de la compression de ligne sur toutes les partitions de l'index.
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
L'exemple ci-dessous illustre la création d'un index sur une table partitionnée à l'aide de la compression de page sur la partition 1 de l'index et à l'aide de la compression de ligne sur les partitions 2 à 4 de l'index.
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO
Historique des modifications
Mise à jour du contenu |
---|
Mise à jour de la définition IGNORE_DUP_KEY pour indiquer clairement à quel moment l'option est effective. |
Clarification des éléments syntaxiques pour filter_predicate, conjunct, disjunct et comparison. |
Voir aussi