Partager via

CREATE INDEX (Transact-SQL)

  • Article

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). Pour plus d'informations sur la création d'un index columnstore optimisé en mémoire xVelocity, consultez CREATE COLUMNSTORE INDEX (Transact-SQL).

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

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 <conjunct> ]

<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 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 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, qu'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.

    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 Créer 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.

    Dans certains cas, la création d'un index cluster peut activer les index précédemment désactivés. Pour plus d'informations, consultez Activer les index et contraintes et Désactiver les index et contraintes.

  • 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é.

    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.

    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éfini 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 Créer des 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 Index sur les 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 spatiales 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.

    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 soit CREATE PARTITION SCHEME, soit ALTER PARTITION SCHEME. column_name désigne la colonne selon 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 que partition_scheme_name utilise. column_name ne se limite pas aux colonnes dans la définition de l'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 le partitionnement des index, consultez Tables et 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 fonctions 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 plus d'informations, consultez FILESTREAM (SQL Server).

<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 indiquant le taux de remplissage appliqué par le Moteur de base de données au niveau feuille de chaque page d'index lors de la création ou de la reconstruction de l'index. fillfactor doit être une valeur entière comprise entre 1 et 100. Si fillfactor a pour valeur 100, le Moteur de base de données crée des index avec des pages de niveau feuille intégralement remplies.

    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 facteur de remplissage, utilisez l'affichage catalogue sys.indexes.

    Important

    La création d'un index cluster avec un facteur de remplissage FILLFACTOR 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 Spécifier un facteur de remplissage pour un index.

  • 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 Option SORT_IN_TEMPDB pour les 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 }
    Spécifie si les tables sous-jacentes et les index associés sont disponibles pour modifier des requêtes et des données pendant l'opération d'index. La valeur par défaut est OFF.

    [!REMARQUE]

    Les opérations d'index en ligne ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.

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

    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 et des types spatiaux.

    Pour plus d'informations, consultez Exécuter des opérations en ligne sur les index.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Indique 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 à quel moment 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 Configurer l'option de configuration du serveur Degré maximal de parallélisme pendant la durée de l'opération d'index. 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 Configurer des opérations d'index parallèles.

    [!REMARQUE]

    Les opérations d'index parallèles ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.

  • 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 Compression de données.

  • 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, 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 Configurer des 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.

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.

Index cluster

La création d'un index cluster sur une table (segment de mémoire) 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 sur les index cluster, consultez Créer des 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 restauré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.

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 l'index utilise le même schéma de partition et la même colonne de partitionnement que la table, l'index est aligné avec la table.

AttentionAttention

La création et la reconstruction des index non alignés sur une table contenant plus de 1 000 partitions sont possibles, mais ne sont pas prises en charge. Ces opérations peuvent entraîner une dégradation des performances ou une consommation de mémoire excessive. Nous vous recommandons d'utiliser uniquement des index alignés lorsque le nombre de partitions est supérieur à 1000.

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.

Dans SQL Server 2012, les statistiques ne sont pas créées en analysant toutes les lignes de la table lorsqu'un index partitionné est créé ou reconstruit. Au lieu de cela, l'optimiseur de requête utilise l'algorithme d'échantillonnage par défaut pour générer des statistiques. Pour obtenir des statistiques sur les index partitionnés en analysant toutes les lignes de la table, utilisez CREATE STATISTICS ou UPDATE STATISTICS avec la clause FULLSCAN.

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 Créer des index filtrés.

Index spatiaux

Pour plus d'informations sur les index spatiaux, consultez CREATE SPATIAL INDEX (Transact-SQL) et Vue d'ensemble des index spatiaux.

Index XML

Pour plus d'informations sur les index XML, consultez CREATE XML INDEX (Transact-SQL) et Index XML (SQL Server).

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. La clé d'un index cluster ne peut pas contenir de colonnes varchar qui possèdent 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 actions d'insertion ou de mise à jour réalisées ultérieurement sur la colonne et susceptibles d'envoyer les données hors ligne sont vouées à l'échec.

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 Créer des index avec colonnes incluses.

[!REMARQUE]

Lorsque des tables sont partitionnées, si les colonnes de clé de partitionnement ne sont pas déjà présentes dans un index cluster non unique, elles sont ajoutées à l'index par le Moteur de base de données. La taille combinée des colonnes indexées (sans compter les colonnes incluses), plus toutes les colonnes de partitionnement ajoutées, ne peut pas dépasser 1800 octets dans un index cluster non unique.

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 Index sur les 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 Créer des 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.

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 réelles et leurs verrous associés jusqu'à la validation de la transaction.

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.

  • 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écuter des 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 promouvoir un verrou de ligne ou de page en verrou de table.

Lorsque ALLOW_ROW_LOCKS = OFF et ALLOW_PAGE_LOCK = OFF, seul un verrou de table est autorisé lors de l'accès à l'index.

Affichage des informations sur les 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.

Compression de données

La compression de données est décrite dans la rubrique Compression de données. 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 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 AdventureWorks2012;
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 (BusinessEntityID); 
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 AdventureWorks2012
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 AdventureWorks2012;
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 AdventureWorks2012;
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é restauré.

Les mêmes instructions sont exécutées de nouveau, mais avec IGNORE_DUP_KEY défini sur OFF.

USE AdentureWorks2012;
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 seule ligne de la table 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 AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
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. Cet exemple suppose que l'exemple d'index partitionné a été installé.

USE AdentureWorks2012;
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 AdventureWorks2012;
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

Voir aussi

Référence

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE STATISTICS (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE XML INDEX (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)

Concepts

Index XML (SQL Server)