CREATE INDEX (Transact-SQL)

  • Article

Mis à jour : 14 avril 2006

Crée un index relationnel dans une table ou une vue spécifiée, ou un index XML dans une table spécifiée. Un index peut être créé avant que la table soit remplie de données. Les index 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é.

Icône Lien de rubriqueConventions de la syntaxe de Transact-SQL

Syntaxe

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<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
}

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   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 dans 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 SQL Server 2005 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 affecté ou non de la valeur 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 pouvoir créer un index unique 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 s'appelle 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.

    ms188783.note(fr-fr,SQL.90).gifRemarque :
    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 avoir 249 index non-cluster, quelle que soit la manière dont les index ont été créés : implicitement avec les 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 suivre les règles des identificateurs.

    Les noms d'index XML primaires ne peuvent pas commencer avec les caractères suivants : #, ##, @ ou @@.

  • column
    La ou les 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 de type LOB (Large Object) 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 unique.

    Le nombre maximal de colonnes non-clé incluses est 1 023, et le nombre minimal est 1.

    Les noms de colonnes 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 comme colonnes non-clé. Pour plus d'informations, consultez Index avec colonnes incluses.

    Tous les types de données sont autorisés, sauf text, ntext et image. L'index doit être créé ou régénéré hors ligne (ONLINE = OFF) si l'une des colonnes non-clé spécifiées est de type 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 comme colonnes non-clé dès lors que les types de colonnes calculées peuvent être utilisés comme colonnes incluses. Pour plus d'informations, consultez Création d'index sur des colonnes calculées.

  • ON partition_scheme_name**(column_name)**
    Spécifie le schéma de partition qui définit le groupe de fichiers auquel 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.

    ms188783.note(fr-fr,SQL.90).gifRemarque :
    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.

    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 les index de partitionnement, 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. Les index XML utilisent le même groupe de fichiers que la table.

  • ON "default"
    Crée l'index spécifié dans 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 actuelle. Il s'agit de l'option par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

  • [PRIMARY] XML
    Crée un index XML dans la colonne xml spécifiée. Lorsque PRIMARY est spécifié, un index cluster est créé avec la clé cluster créée à partir de la clé de cluster de la table utilisateur et d'un identificateur de nœud XML. Chaque table peut avoir 249 index XML. Tenez compte des points suivants lorsque vous créez un index XML :

    • Un index cluster doit exister dans la clé primaire de la table utilisateur.
    • La clé de cluster de la table utilisateur est limitée à 15 colonnes.
    • Chaque colonne xml d'une table peut avoir un index XML primaire et plusieurs index XML secondaires.
    • Un index XML primaire sur une colonne xml doit exister pour pouvoir créer un index XML secondaire sur la colonne.
    • Un index XML peut être créé seulement sur une colonne xml unique. Vous ne pouvez pas créer un index XML sur une colonne non-xml, ni créer un index relationnel sur une colonne xml.
    • Vous ne pouvez pas créer un index XML primaire ou secondaire sur une colonne xml d'une vue, sur une variable table avec des colonnes xml ou des variables de type xml.
    • Vous ne pouvez pas créer un index XML primaire sur une colonne xml calculée.
    • Les valeurs de l'option SET doivent être identiques à celles requises pour les vues indexées et les index de colonne calculée. En particulier, l'option ARITHABORT doit être définie à ON lorsqu'un index XML est créé et lors de l'insertion, de la suppression ou de la mise à jour des valeurs dans la colonne xml. Pour plus d'informations, consultez Options SET affectant les résultats.

    Pour plus d'informations, consultez Index portant sur des colonnes de type xml.

  • xml_column_name
    Colonne xml sur laquelle l'index est basé. Une seule colonne xml peut être spécifiée dans une même définition d'index XML. Toutefois, vous pouvez créer plusieurs index XML secondaires sur une colonne xml.
  • USING XML INDEX xml_index_name
    Spécifie l'index XML primaire à utiliser pour créer un index XML secondaire.

  • FOR { VALUE | PATH | PROPERTY }
    Spécifie le type d'index XML secondaire.

    • VALUE
      Crée un index XML secondaire sur les colonnes dans lesquelles les colonnes de clé (valeur et chemin de nœud) proviennent de l'index XML primaire.
    • PATH
      Crée un index XML secondaire sur les colonnes créées sur des valeurs de chemin et des valeurs de nœud dans l'index XML primaire. Dans l'index secondaire PATH, les valeurs de chemin et de nœud sont des colonnes clés qui permettent de rechercher efficacement des chemins.
    • PROPERTY
      Crée un index XML secondaire sur les colonnes (valeur PK, de chemin et de nœud) de l'index XML primaire où PK est la clé primaire de la table de base.
<object>::=

Objet qualifié complet ou partiellement qualifié à 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>::=

Définit 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
      Pourcentage d'espace libre indiqué par la valeur de fillfactor et qui 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 remplies pratiquement jusqu'à leur capacité maximale pour conserver un espace suffisant pour au moins une ligne de la taille maximale que peut avoir l'index, en tenant compte des groupes de clés des 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 corresponde au 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 le taux de remplissage de chaque niveau de feuille de chaque page d'index par le Moteur de base de données pendant la création ou la reconstruction de l'index. fillfactor doit être une valeur entière comprise entre 1 et 100, la valeur par défaut étant 0. Si fillfactor est égal à 100 ou 0, le Moteur de base de données crée des index avec des pages de feuilles entièrement remplies.

    ms188783.note(fr-fr,SQL.90).gifRemarque :
    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 régénération 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.

    ms188783.note(fr-fr,SQL.90).gifImportant :
    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 Taux 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 aux erreurs de valeurs de clés dupliquées dans une opération d'insertion de plusieurs lignes dans un index cluster unique ou un index non-cluster unique. La valeur par défaut est OFF.

    • ON
      Un message d'avertissement est émis, et seules les lignes qui violent la règle d'unicité de l'index échouent.
    • OFF
      Un message d'erreur est émis et l'ensemble de la transaction INSERT est annulé.

    Le paramètre IGNORE_DUP_KEY s'applique uniquement aux opérations d'insertion qui sont exécutées après la création ou la régénération de l'index. Le paramètre n'a pas d'effet lors de l'opération de création d'index.

    IGNORE_DUP_KEY ne peut pas avoir la valeur ON pour les index XML et les index créés sur une vue.

    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.

    ms188783.note(fr-fr,SQL.90).gifImportant :
    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, non-cluster ou XML 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, relationnel ou XML, ne peut pas être modifié avec DROP_EXISTING. En outre, un index primaire XML ne peut pas être redéfini comme index XML secondaire, et vice versa.

    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.

    ms188783.note(fr-fr,SQL.90).gifRemarque :
    Les opérations d'indexation en ligne sont disponibles seulement dans SQL Server 2005 Enterprise Edition.
    • ON
      Les verrous de table à long terme ne sont pas maintenus lors de l'indexation. 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 modification de schéma (SCH-M, Schema Modification) 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. Ceci 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.
      ms188783.note(fr-fr,SQL.90).gifRemarque :
      Un index non-cluster non unique peut être créé en ligne si la table contient des types de données LOB et qu'aucune de ces colonnes n'est utilisée dans la définition d'index comme colonne clé ou colonne (incluse) non-clé.

    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.

    ms188783.note(fr-fr,SQL.90).gifRemarque :
    Les opérations d'indexation parallèle sont disponibles seulement dans SQL Server 2005 Enterprise Edition.

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 les ordinateurs multiprocesseur avec SQL Server 2005 Enterprise Edition, 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é 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 partition.

Lorsque vous partitionnez un index cluster non unique, le moteur de base de données ajoute par défaut les colonnes de partition à 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 la performance 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 :

  1. Vérifiez que les options SET sont correctes pour toutes les tables existantes qui seront référencées dans la vue.
  2. Vérifiez que les options SET de la session sont définies correctement avant de créer des tables et la vue.
  3. Vérifiez que la définition de la vue est déterministe.
  4. Créez la vue avec l'option WITH SCHEMABINDING.
  5. 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 définie à ON, l'expression 'abc' + NULL renvoie la valeur NULL. Cependant, si CONCAT_NULL_YIEDS_NULL est définie à OFF, la même expression renvoie 'abc'.

Pour pouvoir gérer correctement les vues et renvoyer des résultats cohérents, les vues indexées nécessitent des valeurs fixes pour plusieurs options SET. Les options SET répertoriées dans le tableau suivant 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 des requêtes de copie en bloc, de réplication et 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

    *Dans SQL Server 2005, l'activation de l'option ANSI_WARNINGS active de manière implicite l'option ARITHABORT lorsque le niveau de compatibilité de la base de données est défini sur 90. Si le niveau de compatibilité est égal ou inférieur à 80, l'option ARITHABORT doit être activée de manière explicite (c'est-à-dire définie sur ON).

Si vous utilisez une connexion serveur OLE DB ou ODBC, la seule valeur qui doit être modifiée est ARITHABORT. Toutes les valeurs de bibliothèque 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.

ms188783.note(fr-fr,SQL.90).gifImportant :
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 toutes les clauses WHERE et GROUP BY sont déterministes. Les expressions déterministes renvoient 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 renvoie 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 renvoie 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é de vues indexées. Les expression 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 renvoie 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 à 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 à 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 ne peut pas contenir 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. Le niveau de compatibilité d'une base de données qui contient une vue indexée ne peut pas être remplacé par un niveau de compatibilité inférieur à 80.

Index XML

Pour plus d'informations, consultez Index portant sur des colonnes de type 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 dans 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éé dans 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.

Dans SQL Server 2005, les index non-cluster peuvent contenir des colonnes non-clé dans le 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. Dans SQL Server 2005, 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éterministe.
  • 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 comme clés ou colonnes non-clé incluses dès lors que les types de colonnes calculées peuvent être utilisés comme colonnes clé d'index ou colonnes non-clé. Par exemple, vous ne pouvez pas créer un index XML dans une colonne calculée xml. 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 renvoie 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é, 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é ou de colonnes non-clé. 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 introduit de nouvelles options d'index et modifie é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 uniquement 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éfinie à OFF. Si l'index cluster est désactivé, l'opération CREATE INDEX WITH DROP_EXISTING doit être exécutée avec ONLINE définie à OFF. Si un index non-cluster est désactivé et n'est pas associé à un index cluster activé, l'opération CREATE INDEX WITH DROP_EXISTING ne peut pas être exécutée avec ONLINE définie à OFF ou à 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 Promotion 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.

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 renvoyé 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 renvoie 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éfinie à 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é)

L'exemple suivant crée un index non-cluster avec une colonne clé (PostalCode) et quatre colonnes non-clé (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 XML primaire

L'exemple suivant crée un index XML primaire sur la colonne CatalogDescription de la table Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

I. Création d'un index XML secondaire

L'exemple suivant crée un index XML secondaire sur la colonne CatalogDescription de la table Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

J. Création d'un index partitionné

L'exemple suivant crée un index partitionné non-cluster sur TransactionsPS1, un schéma de partition existant. Cet exemple suppose que l'exemple d'index partitionné a été installé. Pour plus d'informations, consultez Readme_PartitioningScript.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_TransactionHistory_ReferenceOrderID')
    DROP INDEX IX_TransactionHistory_ReferenceOrderID
        ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO

Voir aussi

Référence

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL)
Types de données (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
EVENTDATA (Transact-SQL)

Autres ressources

Détermination de l'espace disque requis par les index
Consignes générales pour la création d'index
Index portant sur des colonnes de type xml
Architecture des tables et des index

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

14 avril 2006
Contenu mis à jour :
  • Suppression des index non-cluster uniques de la liste des index qui ne peuvent pas être créés en ligne. Cette modification s'applique à SQL Server 2005 Service Pack 1 et version ultérieure.
  • Ajout d'une note de bas de page à la table Options SET à propos de l'effet de l'activation de ANSI_WARNINGS sur le paramètre ARITHABORT.

5 décembre 2005
Nouveau contenu :
  • Les index non-cluster uniques ont été ajoutés à la liste des index qui ne peuvent pas être créés en ligne.