CREATE INDEX (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Crée un index relationnel sur une table ou une vue. Également appelé index rowstore, car il s’agit d’un index B-Tree cluster ou non-cluster. Vous pouvez créer un index rowstore avant que la table soit remplie de données. Utilisez un index rowstore pour améliorer les performances des requêtes, en particulier quand les requêtes effectuent une sélection dans des colonnes spécifiques ou qu’elles exigent que les valeurs soient triées dans un ordre particulier.

Remarque

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

Azure Synapse Analytics et Analytics Platform System (PDW) ne prennent actuellement pas en charge les contraintes Unique. Tous les exemples référençant des contraintes uniques s’appliquent uniquement à SQL Server, Azure SQL Database et Azure SQL Managed Instance.

Pour plus d’informations sur les instructions de conception d’index, reportez-vous au guide de conception d’index SQL Server.

Exemples :

1. Créer un index non-cluster sur une table ou une vue

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Créer un index cluster sur une table et utiliser un nom en 3 parties pour la table

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Créer un index non-cluster avec une contrainte unique et spécifier l’ordre de tri

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Scénario clé :

À compter de SQL Server 2016 (13.x), dans Azure SQL Database et dans Azure SQL Managed Instance, vous pouvez utiliser un index non cluster sur un index columnstore pour améliorer les performances des requêtes d’entreposage de données. Pour plus d’informations, consultez les index Columnstore - entrepôt de données.

Pour d’autres types d’index, consultez :

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server, Azure SQL Database, Azure SQL Managed Instance

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.table_or_view_name | schema_name.table_or_view_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 }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Index relationnel à compatibilité descendante

Important

La structure de la syntaxe de l’index relationnel à compatibilité descendante sera supprimée dans une future version de SQL Server. Évitez d'utiliser cette structure de syntaxe dans tout nouveau travail de développement et envisagez de modifier les applications qui l'utilisent actuellement. Utilisez la structure de syntaxe spécifiée dans <relational_index_option> à la place.

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
}

Syntaxe pour Azure Synapse Analytics et Parallel Data Warehouse

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

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.

Le moteur de base de données n’autorise pas la création d’un index unique sur des colonnes qui incluent déjà des valeurs dupliquées, qu’elle soit définie ONou non IGNORE_DUP_KEY sur . Si cette tentative est effectuée, 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.

Une UNIQUE contrainte traite NULL comme une valeur. Si une colonne est nullable et qu’une UNIQUE contrainte existe sur la colonne, au plus une ligne avec une NULL valeur est autorisée.

CLUSTERED

Crée un index dans lequel l’ordre de tri spécifié pour les colonnes clés d’index détermine l’ordre de page dans la structure d’index sur le disque. Les lignes des pages situées dans le bas ou la feuille, le niveau de l’index cluster contient toujours toutes les colonnes de la table. Les lignes des pages dans les niveaux supérieurs de l’index contiennent uniquement des colonnes clés.

Une table ne peut avoir qu’un seul index cluster. Si un index cluster existe sur une table, il contient toutes les données de la table. Une table sans index cluster est appelée segment de mémoire.

Une vue avec un index cluster unique est appelée une vue indexée. Une vue indexée ne peut avoir qu’un seul index cluster. 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 existants sur les tables sont reconstruits lorsqu’un index cluster est créé, qui est une opération gourmande en ressources si la table est volumineuse.

Si CLUSTERED n’est pas spécifié, un index non cluster est créé.

Remarque

Étant donné que l’index cluster contient toutes les données de la table, la création d’un index cluster et l’utilisation de la ou ON filegroup_name de la ON partition_scheme_name clause déplace efficacement la table du groupe de fichiers sur laquelle la table a été créée vers le nouveau schéma de partition ou le 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 dans lequel l’ordre de tri spécifié pour les colonnes clés d’index détermine l’ordre de page dans la structure d’index sur le disque. Contrairement à l’index cluster, les lignes des pages du niveau feuille d’un index non cluster contiennent uniquement les colonnes clés d’index. Si vous le souhaitez, un sous-ensemble de colonnes non clés peut être inclus à l’aide de la INCLUDE clause.

Chaque table peut avoir jusqu’à 999 index non cluster, quelle que soit la façon dont les index sont créés : implicitement avec les PRIMARY KEYUNIQUE contraintes 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.

Sauf indication contraire, le type d’index par défaut est non-cluster.

index_name

Nom de l’index. Les noms d’index doivent être uniques dans une table ou une vue, mais ils ne doivent pas nécessairement être uniques dans une base de données. Les noms d’index doivent se conformer aux règles régissant les identificateurs.

column

Colonne(s) 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. Répertoriez les colonnes à inclure dans l’index composite, suivant l’ordre de priorité de tri, dans les parenthèses après table_or_view_name.

Vous pouvez combiner jusqu’à 32 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 pour les valeurs d’index combinées est de 900 octets pour un index cluster, ou de 1 700 pour un index non-cluster. Les limites sont de 16 colonnes et de 900 octets pour les versions antérieures à SQL Database et à SQL Server 2016 (13.x).

Les colonnes ayant les types de données LOB (Large OBject) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml ou image ne peuvent pas être spécifiées comme colonnes clés pour un index. En outre, une définition de vue indexée ne peut pas inclure de colonnes ntext, texte ou 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 les 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 (colonne [ ,... n ] )

Spécifie les colonnes non clés à ajouter au niveau feuille d’un index non cluster. L'index non cluster peut être unique ou non.

Les noms de colonnes ne peuvent pas être répétés dans la INCLUDE liste et ne peuvent pas être utilisés simultanément comme colonnes clés et non clés. Les index non cluster contiennent toujours implicitement les colonnes d’index cluster si un index cluster est défini sur la table. Pour plus d’informations, consultez Créer des index avec des colonnes incluses.

Tous les types de données sont autorisés, à l'exception de text, ntextet image. À compter de SQL Server 2012 (11.x), dans Azure SQL Database et dans Azure SQL Managed Instance, si l’une des colonnes non clés spécifiées est varchar(max), nvarchar(max)ou varbinary(max), l’index peut être généré ou reconstruit à l’aide de l’option ONLINE .

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 tant que le type de données de colonne calculée est autorisé en tant que colonne incluse. 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.

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 de type de données définie par l’utilisateur (UDT), une colonne de type de données spatiales ou une colonne de type de données hierarchyid . Les comparaisons avec NULL des littéraux utilisant les opérateurs de comparaison ne sont pas autorisées. L’utilisation des opérateurs IS NULL et IS NOT NULL est préférable.

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 UNIQUE les index, seules les lignes sélectionnées doivent avoir des valeurs d’index uniques. 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é sont 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 spécifie la colonne de partitionnement de l’index. 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 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, column_name doit être choisie parmi celles utilisées comme clé unique. 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 d’index, les tables partitionnés et les index.

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é dans le même groupe de fichiers ou schéma de partition que la table ou la vue.

Le terme default, dans ce contexte, n’est pas un mot clé. Il s’agit d’un identificateur pour le groupe de fichiers ou le schéma partitionné de la table ou de la vue et doit être délimité, comme dans ON "default" ou ON [default]. Si "default" elle est spécifiée, l’option QUOTED_IDENTIFIER doit être ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d’informations, voir SET QUOTED_IDENTIFIER.

Remarque

Dans le contexte de , "default" et [default] n’indiquez pas le groupe de CREATE INDEXfichiers par défaut de la base de données. Ils indiquent le groupe de fichiers ou le schéma de partition utilisé par la table ou la vue de base. Cela diffère de CREATE TABLE, où "default" et [default] placez la table sur le groupe de fichiers par défaut de la base de données.

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

Le 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 la même fonction de partition et les mêmes colonnes de partition que le schéma de partition de 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 de 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).

<objet>::=

Objet qualifié complet ou partiel à indexer.

database_name

Nom de la base de données.

schema_name

Nom du schéma auquel appartient la vue ou la table.

table_or_view_name

Nom de la table ou de la vue à indexer.

Pour créer un index sur une vue, la vue doit être définie avec SCHEMABINDING. 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 Remarques.

Depuis SQL Server 2016 (13.x), l’objet peut être une table stockée avec un index columnstore cluster.

Azure SQL Database prend en charge le format <database_name>.<schema_name>.<object_name> de nom en trois parties quand <database_name> il s’agit du nom de la base de données actuelle, ou <database_name> est tempdb et <object_name> commence par # ou ##. Si le nom du schéma est dbo, <schema_name> peut être omis.

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

• ACTIVÉ

  Le pourcentage d’espace libre spécifié par le facteur de remplissage est appliqué aux pages de niveau intermédiaire de l’index. Si FILLFACTOR aucune valeur n’est spécifiée en même temps PAD_INDEX , ONla valeur du facteur de remplissage dans sys.indexes est utilisée.

• OFF

  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. Cela se produit également si PAD_INDEX la valeur est définie ON , mais que le facteur de remplissage n’est pas spécifié.

L’option PAD_INDEX est utile uniquement quand FILLFACTOR elle est spécifiée, car PAD_INDEX utilise le pourcentage spécifié par FILLFACTOR. Si le pourcentage spécifié pour FILLFACTOR n’est pas suffisamment grand pour autoriser une ligne, le moteur de base de données remplace en interne le pourcentage pour autoriser le minimum. Le nombre de lignes d’une page d’index intermédiaire n’est jamais inférieur à deux, quelle que soit la faible valeur de FILLFACTOR.

Dans la syntaxe à compatibilité descendante, WITH PAD_INDEX équivaut à 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. La valeur de remplissage doit être une valeur entière comprise entre 1 et 100. Les taux de remplissage 0 et 100 sont identiques en tous points. Si fillfactor a la 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. Dans les pages, le moteur de base de données ne conserve pas dynamiquement le pourcentage d’espace libre défini.

Pour afficher le paramètre de facteur de remplissage, utilisez la fill_factor colonne de la vue catalogue sys.indexes .

Important

La création d’un index avec moins FILLFACTOR de 100 augmente la quantité d’espace de stockage occupée par le moteur de base de données, car le moteur de base de données redistribue les données en fonction du facteur de remplissage lors de la création ou de la reconstruction d’un index.

Pour plus d’informations, consultez Spécifier le facteur de remplissage d’un index.

SORT_IN_TEMPDB = { ON | OFF }

Spécifie s’il faut stocker les résultats de tri temporaire dans tempdb. La valeur par défaut est à l’exception OFF d’Azure SQL Database Hyperscale. Pour toutes les opérations de génération d’index dans Hyperscale, SORT_IN_TEMPDB elle est toujours sauf ON si une build d’index pouvant être reprise est utilisée. Pour les builds d’index pouvant être reprise, SORT_IN_TEMPDB est toujours OFF.

• ACTIVÉ

  Les résultats de tri intermédiaires utilisés pour générer l’index sont stockés dans tempdb. Cela peut réduire le temps nécessaire à la création d’un index. 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.

En plus de l’espace requis dans la base de données utilisateur pour créer l’index, tempdb doit avoir environ la même quantité d’espace supplémentaire pour contenir les résultats de tri intermédiaires. Pour plus d’informations, consultez SORT_IN_TEMPDB option pour les index.

Dans la syntaxe à compatibilité descendante, WITH SORT_IN_TEMPDB équivaut à 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, d’ALTER INDEX ou d’UPDATE. La valeur par défaut est OFF.

• ACTIVÉ

  Un message d’avertissement se produit lorsque des valeurs de clé dupliquées sont insérées dans un index unique. Seules les lignes qui ne respectent pas la contrainte d’unicité ne sont pas insérées.

• OFF

  Un message d’erreur se produit lorsque des valeurs de clé dupliquées sont insérées dans un index unique. L’instruction entière INSERT est restaurée.

IGNORE_DUP_KEY ne peut pas être défini ON pour les index créés sur une vue, des index non uniques, des index XML, des index spatiaux et des index filtrés.

Pour afficher le IGNORE_DUP_KEY paramètre d’un index, utilisez la ignore_dup_key colonne dans la vue catalogue sys.indexes .

Dans la syntaxe à compatibilité descendante, WITH IGNORE_DUP_KEY équivaut à WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}

Spécifie si les statistiques sont recomputées. La valeur par défaut est OFF.

• ACTIVÉ

  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.

Avertissement

Si vous désactivez la recomputation automatique des statistiques en définissant STATISTICS_NORECOMPUTE = ON, vous pouvez empêcher l’optimiseur de requête de choisir des plans d’exécution optimaux pour les requêtes impliquant la table.

Le paramètre STATISTICS_NORECOMPUTE pour ON n’empêche pas la mise à jour des statistiques d’index qui se produisent pendant l’opération de reconstruction d’index.

Dans la syntaxe à compatibilité descendante, WITH STATISTICS_NORECOMPUTE équivaut à WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | OFF }

S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Quand ON, les statistiques créées sont par statistiques de partition. Quand OFF, l’arborescence des statistiques est supprimée et SQL Server calcule à nouveau les statistiques. La valeur par défaut est OFF.

Si les statistiques par partition ne sont pas prises en charge, l'option est ignorée et un avertissement est généré. Les statistiques incrémentielles ne sont pas prises en charge dans les cas suivants :

• statistiques créées avec des index qui ne sont pas alignés sur les partitions avec la table de base ;
• statistiques créées sur les bases de données secondaires lisibles Always On ;
• statistiques créées sur les bases de données en lecture seule ;
• statistiques créées sur les index filtrés ;
• statistiques créées sur les vues ;
• statistiques créées sur les tables internes ;
• Statistiques créées avec les index spatiaux ou les index XML.

DROP_EXISTING = { ON | OFF }

Option permettant de supprimer et de reconstruire l’index cluster ou non-cluster existant avec des spécifications de colonne modifiées, tout en conservant le même nom pour l’index. La valeur par défaut est OFF.

• ACTIVÉ

  Spécifie de supprimer et de reconstruire l’index existant, qui doit avoir le même nom que le paramètre index_name.

• OFF

  Spécifie de ne pas supprimer et recréer l’index existant. SQL Server affiche une erreur si le nom d’index spécifié existe déjà.

Avec DROP_EXISTING, vous pouvez changer :

• Un index rowstore non-cluster en index rowstore cluster.

Avec DROP_EXISTING, vous ne pouvez pas modifier :

• Un index rowstore cluster en index rowstore non-cluster.
• Un index columnstore cluster en n’importe quel type d’index rowstore.

Dans la syntaxe à compatibilité descendante, WITH DROP_EXISTING équivaut à 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 de données pendant l'opération d'index. La valeur par défaut est OFF.

Important

Les opérations d’index en ligne ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

• ACTIVÉ

  Les verrous de table à long terme ne sont pas maintenus pendant la durée de l'opération d'index. Pendant la phase principale de l’opération d’index, seul un verrou partagé d’intention (IS) est conservé 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) est conservé sur l’objet source pendant une courte période. À la fin de l’opération, pendant une courte période, un verrou partagé (S) est acquis sur l’objet si un index non cluster est créé. Un verrou de modification de schéma (Sch-M) est acquis lorsqu’un index cluster est créé ou supprimé en ligne et lorsqu’un index cluster ou non cluster est reconstruit. ONLINE ne peut pas être défini ON sur le moment où un index est créé sur une table temporaire locale.

  Remarque

  Vous pouvez utiliser l’option WAIT_AT_LOW_PRIORITY pour réduire ou éviter le blocage pendant les opérations d’index en ligne. Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne.

• OFF

  Des verrous de table sont appliqués pendant l'opération d'indexation. Une opération d’index hors connexion qui crée, reconstruit ou supprime un index cluster, spatial ou XML, ou reconstruit ou supprime un index non cluster, acquiert un verrou de modification de schéma (Sch-M) sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération. Une opération d’index hors connexion qui crée un index non cluster acquiert initialement un verrou partagé (S) sur la table. Cela empêche les modifications de la définition de table sous-jacente, mais permet de lire et de modifier les données de la table pendant que la build d’index est en cours.

Pour plus d’informations, consultez Effectuer des opérations d’index en ligne et des instructions pour les opérations d’index en ligne.

Les index, notamment les index portant sur des tables temporaires globales, ne peuvent pas être créés en ligne, à l’exception des cas suivants :

• Index XML
• Index de table temporaire locale
• Index cluster unique de départ sur une vue
• Index cluster désactivés
• Index columnstore en cluster dans SQL Server 2017 (14.x)) et versions antérieures
• Index columnstore non cluster dans SQL Server 2016 (13.x)) et versions antérieures
• Idex cluster si la table sous-jacente contient des types de données LOB (image, ntext, text) et des types de données spatiales
• Les colonnes varchar(max) et varbinary(max) ne peuvent pas faire partie d’un clé d’index. Dans SQL Server (à partir de SQL Server 2012 (11.x)), dans Azure SQL Database et dans Azure SQL Managed Instance, lorsqu’une table contient des colonnes varchar(max) ou varbinary(max), un index cluster contenant d’autres colonnes peut être généré ou reconstruit à l’aide de l’option ONLINE .
• Index non cluster sur une table avec un index columnstore cluster

Pour plus d’informations, consultez Fonctionnement des opérations d’index en ligne.

RESUMABLE = { ON | OFF }

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie si une opération d’index en ligne peut être reprise. Pour plus d’informations, consultez les opérations d’index pouvant être reprise et les considérations relatives aux index pouvant être repris.

• ACTIVÉ

  L’opération d’index peut être reprise.

• OFF

  L’opération d’index ne peut pas être reprise.

MAX_DURATION time [MINUTES] utilisé avec RESUMABLE = ON (nécessite ONLINE = ON)

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie la durée pendant laquelle, en minutes, une opération d’index pouvant être reprise est exécutée avant qu’elle ne soit suspendue.

ALLOW_ROW_LOCKS = { ON | OFF }

Indique si les verrous de ligne sont autorisés ou non. La valeur par défaut est ON.

• ACTIVÉ

  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 }

Indique si les verrous de page sont autorisés. La valeur par défaut est ON.

• ACTIVÉ

  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.

OPTIMIZE_FOR_SEQUENTIAL_KEY = {ON | OFF }

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie s’il faut ou non optimiser pour éviter la contention d’insertion de dernière page. La valeur par défaut est OFF. Pour plus d’informations, consultez la section Clés séquentielles .

MAXDOP = max_degree_of_parallelism

Remplace l’option de configuration max degree of parallelism pour l’opération d’index. Pour plus d’informations, consultez Configurer l’option de configuration du serveur max degree of parallelism. Permet MAXDOP de limiter le degré de parallélisme et la consommation de ressources résultante pour une opération de génération d’index.

max_degree_of_parallelism peut avoir la valeur :

• 1

  Supprime la création de plans parallèles.

• >1

  Limite le degré maximal de parallélisme utilisé dans une opération d’index parallèle au nombre spécifié ou inférieur en fonction de la charge de travail système actuelle.

• 0 (valeur par défaut)

  Utilise le degré de parallélisme spécifié au niveau du serveur, de la base de données ou du groupe de charge de travail, sauf s’il est réduit en fonction de la charge de travail système actuelle.

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 la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

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 :

• Aucune

  Les partitions d’index ou spécifiées ne sont pas compressées. Cela ne s’applique pas aux index columnstore.

• ROW

  L'index ou les partitions spécifiées sont compressés au moyen de la compression de ligne. Cela ne s’applique pas aux index columnstore.

• PAGE

  L'index ou les partitions spécifiées sont compressés au moyen de la compression de page. Cela ne s’applique pas aux index columnstore.

• COLUMNSTORE

  S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

  S'applique uniquement aux index columnstore, y compris aux index columnstore non cluster et cluster.

• COLUMNSTORE_ARCHIVE

  S’applique à : SQL Server 2014 (12.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

  S'applique uniquement aux index columnstore, y compris aux index columnstore non cluster et cluster. COLUMNSTORE_ARCHIVE compresse davantage la partition spécifiée en une taille plus petite. Peut être utilisé pour l'archivage, ou d'autres situations qui nécessitent moins de stockage et supportent plus de temps pour le stockage et la récupération.

Pour plus d’informations sur la compression, consultez Compression des données.

XML_COMPRESSION

s’applique à: SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Spécifie l’option de compression XML pour l’index spécifié qui contient une ou plusieurs colonnes de type de données XML. Les options disponibles sont les suivantes :

• ACTIVÉ

  L'index ou les partitions spécifiées sont compressés au moyen de la compression XML.

• OFF

  Les partitions d’index ou spécifiées ne sont pas compressées à l’aide de la compression XML.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Spécifie les partitions auxquelles les paramètres DATA_COMPRESSION ou XML_COMPRESSION s’appliquent. Si l’index n’est pas partitionné, l’argument ON PARTITIONS génère une erreur. Si la clause ON PARTITIONS n’est pas fournie, l’option DATA_COMPRESSION ou XML_COMPRESSION s’applique à toutes les partitions d’un index partitionné.

<partition_number_expression> peut être spécifié des manières suivantes :

• Spécifie le numéro d’une partition, par exemple : ON PARTITIONS (2).
• Spécifie 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é en tant que nombres de partition séparés par le mot clé 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)
);

Vous pouvez également spécifier l’option XML_COMPRESSION plusieurs fois, par exemple :

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Remarques

Lors de la création du plan de requête pour l’instruction CREATE INDEX , l’optimiseur de requête peut choisir d’analyser un autre index au lieu d’effectuer une analyse de table. L’opération de tri peut être éliminée dans certaines situations. Sur les ordinateurs multiprocesseurs, CREATE INDEX vous pouvez utiliser le parallélisme pour les opérations d’analyse et de tri associées à la création de l’index, de la même façon que les autres requêtes. Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

L’opération CREATE INDEX peut être enregistrée minimalement si le modèle de récupération de base de données est défini sur journalisé en bloc ou simple.

Vous pouvez créer des index sur une table temporaire. Lorsque la table est supprimée ou n’est plus étendue, les index sont supprimés.

Un index cluster est basé sur une variable de table lorsqu’une contrainte de clé primaire est ajoutée. De même, un index non cluster est basé sur une variable de table lorsqu’une contrainte unique est ajoutée. Lorsque la variable de table est hors de portée, les index sont supprimés.

Les index prennent en charge les propriétés étendues.

CREATE INDEX n’est pas pris en charge dans Microsoft Fabric.

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 et l’architecture et le guide de conception de l’index SQL Server.

Index non cluster

À compter de SQL Server 2016 (13.x), dans Azure SQL Database et dans Azure SQL Managed Instance, vous pouvez créer un index non cluster sur une table stockée en tant qu’index columnstore cluster. Si vous créez d’abord un index non cluster sur une table stockée sous la forme d’un segment de mémoire ou d’un index cluster, l’index persiste si vous convertissez ultérieurement la table en index columnstore cluster. Il n’est pas non plus nécessaire de supprimer l’index non-cluster quand vous reconstruisez l’index columnstore cluster.

L’option FILESTREAM_ON n’est pas valide quand vous créez un index non-cluster sur une table stockée sous la forme d’un index columnstore cluster.

Index uniques

Lorsqu’un index unique existe, le moteur de base de données vérifie les valeurs dupliquées chaque fois que les données sont ajoutées ou modifiées. Les opérations qui généreraient des valeurs de clé dupliquées sont restaurées et le moteur de base de données retourne un message d’erreur. Cela est vrai même si l’opération d’ajout ou de modification de données modifie de nombreuses lignes, mais ne provoque qu’un seul doublon. Si une tentative d’insertion de lignes est effectuée lorsqu’il existe un index unique avec l’option IGNORE_DUP_KEY définie ONsur , les lignes qui ne respectent pas l’index unique sont ignorées.

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

Avertissement

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 dépasse 1 000.

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 les tables et index partitionnés et l’architecture d’index SQL Server et le guide de conception.

Lorsqu’un index est créé ou reconstruit, la requête optimise les statistiques sur l’index. Pour un index partitionné, l’optimiseur de requête utilise l’algorithme d’échantillonnage par défaut au lieu d’analyser toutes les lignes de la table pour un index nonpartitionné. 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 SET options de la colonne Valeur obligatoire sont requises chaque fois que l’une des conditions suivantes se produit :

• Vous créez un index filtré.

• Une INSERTinstruction , ou DELETEMERGE , UPDATEmodifie les données dans un index filtré.

• L’index filtré est utilisé par l’optimiseur de requête pour générer le plan de requête.

  SET option	Valeur requise	Valeur de serveur par défaut	Valeur OLE DB et ODBC par défaut	Valeur DB-Library par défaut
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	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

  ¹ Paramètre ANSI_WARNINGSON sur lequel définir ARITHABORTON implicitement lorsque le niveau de compatibilité de la base de données est défini sur 90 ou version ultérieure. Si le niveau de compatibilité de la base de données est défini sur 80 ou version antérieure, l’option ARITHABORT doit être définie ONexplicitement sur .

Si les SET options sont incorrectes, les conditions suivantes peuvent se produire :

• La création de l’index filtré échoue.
• Le moteur de base de données génère une erreur et restaure l’instruction INSERT, UPDATEou MERGEDELETEqui modifie les données 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 et l’architecture et le guide de conception de l’index SQL Server.

Index spatiaux

Pour plus d’informations sur les index spatiaux, consultez la vue d’ensemble des indexspatiaux CREATE SPATIAL INDEX et spatial.

Index XML

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

Taille de clé d’index

La taille maximale d’une clé d’index est de 900 octets pour un index cluster et de 1 700 octets pour un index non-cluster. (Avant SQL Database et SQL Server 2016 (13.x) la limite était toujours de 900 octets.) Les index sur les colonnes varchar qui dépassent la limite d’octets peuvent être créés si les données existantes dans les colonnes ne dépassent pas la limite au moment de la création de l’index ; toutefois, les opérations d’insertion ou de mise à jour suivantes sur les colonnes qui entraînent l’échec de la taille totale sont supérieures à la limite. La clé d’index d’un index cluster ne peut pas contenir de colonnes varchar qui ont des données existantes dans l’unité ROW_OVERFLOW_DATA d’allocation. Si un index cluster est créé sur une colonne varchar et que les données existantes se trouvent dans l’unité IN_ROW_DATA d’allocation, les opérations d’insertion ou de mise à jour suivantes sur la colonne qui envoie (push) les données hors ligne échouent.

Les index non cluster peuvent inclure des colonnes non clés (incluses) dans le niveau feuille de l’index. Ces colonnes ne sont pas prises en compte par le moteur de base de données lors du calcul de la taille de clé d’index. Pour plus d’informations, consultez Créer des index avec des colonnes incluses et le guide de conception et d’architecture d’index SQL Server.

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. Toutefois, l’utilisation de la PERSISTED propriété développe le type de colonnes calculées indexables à 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 que les options suivantes SET soient définies comme indiqué dans la section précédente Options SET requises pour les index filtrés.

La UNIQUE ou PRIMARY KEY la contrainte peut contenir une colonne calculée tant 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 colonnes clés ou comme colonnes non-clés incluses dès lors que le type de données de la colonne calculée est autorisé comme colonne clé d’index ou comme colonne non-clé incluse. 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 entraîner l’échec d’une opération d’insertion ou de mise à jour qui fonctionnait précédemment. Une telle défaillance peut se produire lorsque la colonne calculée génère une erreur arithmétique.

Par exemple, dans le tableau suivant, même si l’expression de la colonne c calculée semble entraîner une erreur arithmétique lorsque la ligne est insérée, l’instruction INSERT fonctionne.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Toutefois, si vous créez un index sur une colonne ccalculée, la même INSERT instruction échoue.

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. Cela permet à l’optimiseur de requête d’obtenir toutes les informations requises à partir d’une analyse ou d’une recherche d’index non cluster ; les données d’index en cluster ou de table ne sont pas accessibles. Pour plus d’informations, consultez Créer des index avec des colonnes incluses et le guide de conception et d’architecture d’index SQL Server.

Définition des options d’index

SQL Server 2005 (9.x) a introduit de nouvelles options d’index et a également modifié la façon dont les options sont spécifiées. Dans la syntaxe à compatibilité descendante, WITH option_name équivaut à 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 spécifiez WITH (DROP_EXISTING, ONLINE = ON), l’instruction échoue.
• Quand vous créez un index XML, les options doivent être définies en utilisant WITH (<option_name> = <ON | OFF>).

DROP_EXISTING, clause

Vous pouvez utiliser la clause DROP_EXISTING pour regénérer l’index, ajouter ou supprimer des colonnes, modifier des options, modifier l’ordre de tri des colonnes ou changer le schéma de partition ou le groupe de fichiers.

Si l’index applique une PRIMARY KEY ou UNIQUE une contrainte et que la définition d’index n’est pas modifiée d’une manière quelconque, 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 modifier la définition d’une ou UNIQUE d’une PRIMARY KEY contrainte, supprimez la contrainte et ajoutez une contrainte avec la nouvelle définition.

DROP_EXISTING améliore les performances quand 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 regénère pas les index non-cluster quand 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 sont précédemment alignés avec l’index cluster, ils peuvent ne plus être alignés dessus. Pour plus d’informations sur l’alignement des index partitionnés, consultez tables et index partitionnés.

La DROP_EXISTING clause ne trie pas à nouveau 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 décroissant, sauf si l’instruction d’index spécifie un index non cluster et que l’option ONLINE est définie OFFsur . Si l’index cluster est désactivé, l’opération CREATE INDEX WITH DROP_EXISTING doit être effectuée avec ONLINE la valeur définie OFFsur . 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 effectuée avec ONLINE la valeur définie OFF sur ou ON.

Remarque

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. Pour plus d’informations, consultez La désallocation différée.

ONLINE (option)

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.
• L’option WAIT_AT_LOW_PRIORITY d’argument vous permet de déterminer la façon dont l’opération d’index se poursuit lorsqu’elle attend un Sch-M verrou. Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY

Pour plus d’informations, consultez Effectuer des opérations d’index en ligne.

Opérations d’index pouvant être reprises

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Vous pouvez rendre une opération de création d’index en ligne pouvant être reprise. Cela signifie que la build d’index peut être arrêtée et redémarrée ultérieurement à partir du point où elle s’est arrêtée. Pour exécuter une build d’index comme pouvant être reprise, spécifiez l’option RESUMABLE = ON .

Les instructions suivantes s’appliquent aux opérations d’index pouvant être reprise :

• Pour utiliser l’option RESUMABLE , vous devez également utiliser l’option ONLINE .
• L’option RESUMABLE n’est pas conservée dans les métadonnées d’un index donné et s’applique uniquement à la durée de l’instruction DDL actuelle. C’est pourquoi la clause RESUMABLE = ON doit être spécifiée explicitement pour permettre la reprise.
• L’option MAX_DURATION peut être spécifiée dans deux contextes :
  • MAX_DURATION pour l’option RESUMABLE spécifie l’intervalle de temps d’une reconstruction d’un index. Une fois ce temps écoulé, et si la reconstruction de l’index est toujours en cours d’exécution, elle est suspendue. Vous décidez quand la reconstruction d’un index suspendu peut être reprise. La durée en minutes MAX_DURATION doit être supérieure à 0 minutes et inférieure ou égale à une semaine (7 * 24 * 60 = 10080 minutes). Une longue pause dans une opération d’index peut avoir un impact notable sur les performances DML sur une table spécifique ainsi que sur la capacité du disque de base de données, car l’index d’origine et l’index nouvellement créé nécessitent un espace disque et doivent être mis à jour par les opérations DML. Si MAX_DURATION l’option est omise, l’opération d’index se poursuit jusqu’à l’achèvement ou jusqu’à ce qu’un échec se produise.
  • MAX_DURATION pour l’option WAIT_AT_LOW_PRIORITY spécifie le temps d’attente à l’aide de verrous de faible priorité si l’opération d’index est bloquée, avant d’agir. Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne.
• Pour suspendre immédiatement l’opération d’index, vous pouvez exécuter la ALTER INDEX PAUSE commande ou exécuter la KILL <session_id> commande.
• La réexécutation de l’instruction d’origine CREATE INDEX avec les mêmes paramètres reprend une opération de génération d’index suspendue. Vous pouvez également reprendre une opération de génération d’index suspendue en exécutant l’instruction ALTER INDEX RESUME .
• La ABORT commande tue la session qui exécute une build d’index et annule l’opération d’index. Vous ne pouvez pas reprendre une opération d’index qui a été abandonnée.

Une opération d’index pouvant être reprise s’exécute jusqu’à ce qu’elle se termine, suspend ou échoue. Si l’opération s’interrompt, une erreur est émise indiquant que l’opération a été suspendue et que la création de l’index n’a pas terminé. En cas d’échec de l’opération, une erreur est également émise.

Pour voir si une opération d’index est exécutée en tant qu’opération pouvant être reprise et pour vérifier son état d’exécution actuel, utilisez l’affichage catalogue sys.index_resumable_operations .

Ressources

Les ressources suivantes sont requises pour les opérations d’index pouvant être reprise :

• Espace supplémentaire nécessaire pour maintenir la génération de l’index, y compris l’heure à laquelle la génération est suspendue.
• Du débit de journal supplémentaire pendant la phase de tri. L’utilisation globale d’espace journal d’un index pouvant être repris est inférieure à celle de la création d’un index en ligne classique ; la troncature du journal est permise pendant cette opération.
• Les instructions DDL qui tentent de modifier la table associée à l’index en cours de création pendant que l’opération d’index est suspendue ne sont pas autorisées.
• Le nettoyage des éléments fantômes est bloqué sur l’index en génération pendant toute la durée de l’opération, qu’elle soit en pause ou en cours d’exécution.
• Si la table contient des colonnes métier, une build d’index cluster pouvant être reprise nécessite un verrou de modification de schéma (Sch-M) au début de l’opération.

Limitations fonctionnelles actuelles

Les opérations de création d’index pouvant être reprise ont les limitations suivantes :

• Une fois qu’une opération de création d’index en ligne pouvant être reprise est suspendue, la valeur initiale de MAXDOP ne peut pas être modifiée.
• L’option SORT_IN_TEMPDB = ON n’est pas prise en charge pour les opérations d’index pouvant être reprise.
• Impossible d’exécuter la commande RESUMABLE = ON DDL à l’intérieur d’une transaction explicite.
• Vous ne pouvez pas créer un index pouvant être repris qui contient :
  • Colonnes calculées ou timestamp (rowversion) en tant que colonnes clés.
  • Colonne métier en tant que colonne incluse.
• Les opérations d’index pouvant être reprise ne sont pas prises en charge pour :
  • La commande ALTER INDEX REBUILD ALL
  • La commande ALTER TABLE REBUILD
  • Index columnstore
  • Index filtrés
  • Index désactivés

WAIT_AT_LOW_PRIORITY avec les opérations d’index en ligne

s’applique à: SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

Lorsque vous n’utilisez pas l’option WAIT_AT_LOW_PRIORITY , toutes les transactions bloquantes actives contenant des verrous sur la table ou l’index doivent être terminées pour que l’opération de création d’index démarre et se termine. Lorsque l’opération d’index en ligne démarre et avant qu’elle ne se termine, elle doit acquérir un verrou partagé (S) ou une modification de schéma (Sch-M) sur la table et la conserver pendant une courte période. Même si le verrou est conservé pendant une courte durée uniquement, il peut affecter considérablement le débit de la charge de travail, augmenter la latence des requêtes ou provoquer des délais d’exécution.

Pour éviter ces problèmes, l’option WAIT_AT_LOW_PRIORITY vous permet de gérer le comportement ou Sch-MS les verrous requis pour qu’une opération d’index en ligne démarre et se termine, en sélectionnant parmi trois options. Dans tous les cas, si pendant le temps d’attente spécifié par MAX_DURATION = n [minutes] aucun blocage n’implique l’opération d’index, l’opération d’index se poursuit immédiatement.

WAIT_AT_LOW_PRIORITY fait attendre l’opération d’index en ligne à l’aide de verrous de faible priorité, ce qui permet à d’autres opérations utilisant des verrous de priorité normaux de continuer en attendant. Omettre l’option WAIT_AT_LOW_PRIORITY équivaut à WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]

Temps d’attente (valeur entière spécifiée en minutes) que l’opération d’index en ligne attend à l’aide de verrous de faible priorité. Si l’opération est bloquée pendant le MAX_DURATION temps, l’action spécifiée ABORT_AFTER_WAIT est exécutée. MAX_DURATION l’heure est toujours en minutes, et le mot MINUTES peut être omis.

ABORT_AFTER_WAIT= [NONE | | SELFBLOCKERS ]

• NONE: Continuez à attendre le verrou avec une priorité normale.
• SELF: quittez l’opération d’index en ligne en cours d’exécution, sans effectuer d’action. L’option SELF ne peut pas être utilisée lorsque MAX_DURATION la valeur est 0.
• BLOCKERS: supprimez toutes les transactions utilisateur qui bloquent l’opération d’index en ligne afin que l’opération puisse continuer. L’option BLOCKERS nécessite que le principal exécutant l’instruction CREATE INDEX ou ALTER INDEX l’instruction dispose de l’autorisation ALTER ANY CONNECTION .

Vous pouvez utiliser les événements étendus suivants pour surveiller les opérations d’index qui attendent des verrous à faible priorité :

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Options de verrous de ligne et de page

Lorsque ALLOW_ROW_LOCKS = ON et ALLOW_PAGE_LOCK = ON, les verrous au niveau de la ligne, de la page et de la table sont autorisés au moment où vous accédez à 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 au niveau de la table est autorisé au moment où vous accédez à l’index.

Avertissement

Il n’est pas recommandé de désactiver les verrous de ligne ou de page sur un index. Des problèmes liés à l’accès concurrentiel peuvent se produire et certaines fonctionnalités peuvent être indisponibles. Par exemple, un index ne peut pas être réorganisé lorsqu’il ALLOW_PAGE_LOCKS est défini OFFsur .

Clés séquentielles

S’applique à : SQL Server 2019 (15.x) et versions ultérieures, dans Azure SQL Database et dans Azure SQL Managed Instance.

La contention de l’insertion de la dernière page est un problème de performances courant qui se produit lorsqu’un grand nombre de threads simultanés tentent d’insérer des lignes dans un index avec une clé séquentielle. Un index est considéré comme séquentiel lorsque la colonne clé principale contient des valeurs qui augmentent (ou diminuent) toujours, par exemple une colonne d’identité ou une date correspondant par défaut à la date/heure actuelle. Étant donné que les clés insérées sont séquentielles, toutes les nouvelles lignes sont insérées à la fin de la structure d’index, en d’autres termes, sur la même page. Cela conduit à la contention de la page en mémoire qui peut être observée en tant que plusieurs threads en attente d’acquérir un verrou pour la page en question. Le type d’attente correspondant est PAGELATCH_EX.

L’activation de l’option d’index OPTIMIZE_FOR_SEQUENTIAL_KEY active une optimisation dans le moteur de base de données qui vous aide à améliorer le débit pour les insertions de haute concurrence dans l’index. Elle est conçue pour les index qui ont une clé séquentielle et sont susceptibles d’engendrer des contentions d’insertion de la dernière page, mais elle peut être également utile avec les index qui ont des zones réactives dans d’autres zones de la structure d’index B-Tree.

Remarque

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

Compression des données

Pour plus d’informations sur la compression des données, consultez Compression des données.

Voici les points clés à prendre en compte dans le contexte des opérations de génération d’index lorsque la compression des données est utilisée :

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

Pour évaluer comment la modification de l’état de compression affecte l’utilisation de l’espace par une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings .

Compression XML

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

La plupart des considérations relatives à la compression des données s’appliquent à la compression XML. Vous devez également tenir compte des points suivants :

• Quand une liste de partitions est spécifiée, la compression XML peut être activée sur des partitions individuelles. Si la liste des partitions n’est pas spécifiée, toutes les partitions sont configurées pour utiliser la compression XML. Quand une table ou un index est créé, la compression des données XML est désactivée, sauf indication contraire. Lorsqu'une table est modifiée, la compression existante est conservée, sauf indication contraire.
• Si vous spécifiez une liste de partitions ou une partition hors limites, une erreur est générée.
• Quand un index cluster est créé sur un segment de mémoire, cet index hérite de l’état de compression XML du segment, sauf si une autre option de compression est spécifiée.
• La modification du paramètre de compression XML d’un segment de mémoire nécessite la reconstruction de tous les index non cluster sur la table afin qu’ils aient des pointeurs vers les nouveaux emplacements de ligne dans le segment.
• Vous pouvez activer ou désactiver la compression XML en ligne ou hors connexion. L'activation de la compression sur un segment de mémoire est mono-thread pour une opération en ligne.
• Pour déterminer l’état de compression XML des partitions dans une table partitionnée, utilisez la xml_compression colonne de l’affichage sys.partitions catalogue.

Statistiques d’index

Lorsqu’un index rowstore est créé, le moteur de base de données crée également des statistiques sur les colonnes clés de l’index. Le nom de l’objet de statistiques dans la vue catalogue sys.stats correspond au nom de l’index. Pour un index non partitionné, les statistiques sont générées à l’aide d’une analyse complète des données. Pour un index partitionné, les statistiques sont générées à l’aide de l’algorithme d’échantillonnage par défaut.

Lorsqu’un index columnstore est créé, le moteur de base de données crée également un objet de statistiques dans sys.stats . Cet objet de statistiques ne contient pas de données de statistiques telles que l’histogramme et le vecteur de densité. Il est utilisé lors de la création d’un clone de base de données en scriptant la base de données. À ce stade, les commandes et UPDATE STATISTICS ... WITH STATS_STREAM les commandes sont utilisées pour obtenir des métadonnées columnstore telles que le segment, le DBCC SHOW_STATISTICS dictionnaire et la taille du magasin delta et l’ajouter aux statistiques sur l’index columnstore. Ces métadonnées sont obtenues dynamiquement au moment de la compilation des requêtes pour une base de données régulière, mais elles sont fournies par l’objet de statistiques d’un clone de base de données. La commande UPDATE STATISTICS n’est pas prise en charge pour l’objet de statistiques sur un index columnstore dans un autre scénario.

Autorisations

Nécessite l’autorisation ALTER sur la table ou l’affichage ou l’appartenance au db_ddladmin rôle de base de données fixe.

Limitations et restrictions

Dans Azure Synapse Analytics et Analytics Platform System (PDW), vous ne pouvez pas créer :

• Un index rowstore cluster ou non-cluster sur une table d’entrepôt de données quand il existe déjà un index columnstore. Ce comportement est différent de SQL Server SMP qui permet à des index rowstore et columnstore de coexister sur la même table.
• Vous ne pouvez pas créer un index sur une vue.

Métadonnées

Pour afficher des informations sur les index existants, vous pouvez interroger la vue de catalogue sys.indexes.

Notes de version

• Azure SQL Database ne prend pas en charge les groupes de fichiers autres que PRIMARY.
• Azure SQL Database et Azure SQL Managed Instance ne prennent pas en charge FILESTREAM les options.
• Les index Columnstore ne sont pas disponibles avant SQL Server 2012 (11.x).
• Les opérations d’index pouvant être reprise sont disponibles à partir de SQL Server 2017 (14.x), dans Azure SQL Database et dans Azure SQL Managed Instance.

Exemples : Toutes les versions. Utilise la base de données AdventureWorks

R. Créer un index rowstore non-cluster simple

Les exemples suivants créent un index non-cluster sur la colonne VendorID de la table Purchasing.ProductVendor.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Créer un index composite rowstore non-cluster simple

L’exemple suivant crée un index composite non-cluster sur les colonnes SalesQuota et SalesYTD de la table Sales.SalesPerson.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Créer un index sur une table dans une autre base de données

L’exemple suivant crée un index cluster sur la colonne VendorID de la table ProductVendor dans la base de données Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Ajouter une colonne à un index

L’exemple suivant crée un index IX_FF avec deux colonnes à partir de la table dbo.FactFinance. L’instruction suivante reconstruit l’index avec une colonne supplémentaire et conserve le nom existant.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Exemples : SQL Server, Azure SQL Database

E. Créer un index non-cluster unique

L'exemple suivant crée un index non cluster unique sur la colonne Name de la table Production.UnitMeasure dans la base de données AdventureWorks2022. L'index applique la contrainte d'unicité sur les données insérées dans la colonne Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

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.

F. Utiliser 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.

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.

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.

G. 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 dans la base de données AdventureWorks2022 avec l'option DROP_EXISTING. Les options FILLFACTOR et PAD_INDEX sont également définies.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Créer un index sur une vue

L'exemple suivant crée une vue et un index sur cette vue. Deux requêtes sont incluses, lesquelles utilisent la vue indexée.

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

I. Créer un index avec des colonnes (non-clés) incluses

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.

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

J. Créer un index partitionné

L'exemple suivant crée un index partitionné non cluster sur TransactionsPS1, un schéma de partition existant dans la base de données AdventureWorks2022. Cet exemple suppose que l'exemple d'index partitionné a été installé.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Création d'un index filtré

L'exemple suivant crée un index filtré sur la table Production.BillOfMaterials dans la base de données AdventureWorks2022. 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.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

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

M. Créer un index avec compression XML

S'applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL, Azure SQL Managed Instance

L’exemple ci-dessous illustre la création d’un index sur une table non partitionnée avec la compression XML. Au moins une colonne de l’index doit être de type de données xml.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

L’exemple ci-dessous illustre la création d’un index sur une table partitionnée avec la compression XML sur toutes les partitions de l’index.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. Créer, reprendre, suspendre et abandonner des opérations d’index pouvant être reprises

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. CREATE INDEX avec différentes options de verrouillage de basse priorité

Les exemples suivants utilisent l’option WAIT_AT_LOW_PRIORITY pour spécifier différentes stratégies de traitement des blocages.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

L’exemple suivant utilise l’option RESUMABLE et spécifie deux valeurs MAX_DURATION ; la première s’applique à l’option ABORT_AFTER_WAIT, la seconde à l’option RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

P. Syntaxe de base

Créer, reprendre, suspendre et abandonner des opérations d’index pouvant être reprises

s’applique à: SQL Server 2019 (15.x) et versions ultérieures, Azure SQL Database et Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. Créer un index non-cluster sur une table de la base de données active

L’exemple suivant crée un index non-cluster sur la colonne VendorID de la table ProductVendor.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Créer un index cluster sur une table dans une autre base de données

L’exemple suivant crée un index non-cluster sur la colonne VendorID de la table ProductVendor dans la base de données Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Créer un index cluster ordonné sur une table

L’exemple suivant crée un index composite cluster ordonné sur les colonnes c1 et c2 de la table T1 dans la base de données MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Convertir un index columnstore cluster en index cluster ordonné sur une table

L’exemple suivant convertit l’index columnstore cluster existant en index columnstore cluster ordonné appelé MyOrderedCCI sur les colonnes c1 et c2 de la table T2 dans la base de données MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Contenu connexe

• Guide de conception et d’architecture d’index SQL Server
• Exécuter des opérations en ligne sur les index
• Index et ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• Data types
• DBCC SHOW_STATISTICS
• DROP INDEX
• Index XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA