CREATE XML INDEX (Transact-SQL)

Crée un index XML sur une table spécifiée. Un index peut être créé avant que la table ne contienne des données. Vous pouvez créer des index XML sur des tables dans une autre base de données en spécifiant un nom de base de données qualifié.

[!REMARQUE]

Pour créer un index relationnel, consultez CREATE INDEX (Transact-SQL). Pour plus d'informations sur la création d'un index spatial, consultez CREATE SPATIAL INDEX (Transact-SQL).

Conventions de syntaxe de Transact-SQL

Syntaxe

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

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

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

Arguments

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

  • Un index cluster doit exister dans la clé primaire de la table utilisateur.

  • La clé de cluster de la table utilisateur est limitée à 15 colonnes.

  • Chaque colonne xml d'une table peut avoir un index XML primaire et plusieurs index XML secondaires.

  • Un index XML primaire sur une colonne xml doit exister pour pouvoir créer un index XML secondaire sur la colonne.

  • Un index XML peut être créé seulement sur une colonne xml unique. Vous ne pouvez pas créer un index XML sur une colonne non-xml, ni créer un index relationnel sur une colonne xml.

  • Vous ne pouvez pas créer un index XML primaire ou secondaire sur une colonne xml d'une vue, sur une variable table avec des colonnes xml ou des variables de type xml.

  • Vous ne pouvez pas créer un index XML primaire sur une colonne xml calculée.

  • Les valeurs de l'option SET doivent être identiques à celles requises pour les vues indexées et les index de colonne calculée. En particulier, l'option ARITHABORT doit être activée (ON) lorsqu'un index XML est créé et lors de l'insertion, de la suppression ou de la mise à jour des valeurs dans la colonne xml. Pour plus d'informations, consultez Options SET affectant les résultats.

  Pour plus d'informations, consultez Index sur les colonnes de type de données XML.

• index_name
  Nom de l'index. Les noms d'index doivent être uniques dans une table, mais ne doivent pas être nécessairement uniques dans une base de données. Les noms d'index doivent suivre les règles des identificateurs.

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

• xml_column_name
  Colonne xml sur laquelle l'index est basé. Une seule colonne xml peut être spécifiée dans une même définition d'index XML. Toutefois, vous pouvez créer plusieurs index XML secondaires sur une colonne xml.

• USING XML INDEX xml_index_name
  Spécifie l'index XML primaire à utiliser pour créer un index XML secondaire.

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

  • VALUE
    Crée un index XML secondaire sur les colonnes dans lesquelles les colonnes de clé (valeur et chemin de nœud) proviennent de l'index XML primaire.

  • PATH
    Crée un index XML secondaire sur les colonnes créées sur des valeurs de chemin et des valeurs de nœud dans l'index XML primaire. Dans l'index secondaire PATH, les valeurs de chemin et de nœud sont des colonnes clés qui permettent de rechercher efficacement des chemins.

  • PROPERTY
    Crée un index XML secondaire sur les colonnes (valeur PK, de chemin et de nœud) de l'index XML primaire où PK est la clé primaire de la table de base.

<object>::=

Objet qualifié complet ou partiel à indexer.

• database_name
  Nom de la base de données.

• schema_name
  Nom du schéma auquel appartient la table.

• table_name
  Nom de la table à indexer.

<xml_index_option> ::=

Spécifie les options à utiliser lors de la création de 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.  

• FILLFACTOR **=**fillfactor
  Spécifie un pourcentage qui indique l'espace occupé par le niveau feuille de chaque page d'index opéré par le moteur de base de données lors de la création ou de la reconstruction d'index. fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0. Si fillfactor a pour valeur 100 ou 0, le Moteur de base de données crée des index avec des pages de niveau feuille intégralement remplies.

  [!REMARQUE]

  Les taux de remplissage 0 et 100 sont identiques en tous points.

  La valeur FILLFACTOR s'applique uniquement lors de la création ou de la reconstruction de l'index. Le moteur de base de données ne conserve pas dynamiquement dans les pages le pourcentage d'espace libre défini. Pour afficher le taux de remplissage, utilisez l'affichage catalogue sys.indexes.

  Important

  La création d'un index cluster avec un taux de remplissage inférieur à 100 affecte la quantité d'espace de stockage qu'occupent les données, car le moteur de base de données redistribue les données lorsqu'il crée l'index cluster.

  Pour plus d'informations, consultez Facteur de remplissage.

• SORT_IN_TEMPDB = { ON | OFF }
  Spécifie si les résultats temporaires du tri doivent être stockés dans tempdb. La valeur par défaut est OFF.

  • ON
    Les résultats de tri intermédiaires utilisés pour créer l'index sont stockés dans tempdb. Ceci peut accélérer la création d'un index si tempdb ne se trouve pas sur le même groupe de disques que la base de données utilisateur. Toutefois, une plus grande quantité d'espace disque est alors utilisée lors de la création de l'index.

  • OFF
    Les résultats de tri intermédiaires sont stockés dans la même base de données que l'index.

  Outre l'espace nécessaire dans la base de données utilisateur pour créer l'index, tempdb doit disposer à peu près du même espace supplémentaire pour stocker les résultats de tri intermédiaires. Pour plus d'informations, consultez tempdb et création d'index.

• IGNORE_DUP_KEY **=**OFF
  N'a aucun effet pour les index XML, car le type d'index n'est jamais unique. N'activez pas cette option (ON), sinon une erreur est déclenchée.

• DROP_EXISTING = { ON | OFF }
  Spécifie que l'index XML nommé préexistant est supprimé et reconstruit. La valeur par défaut est OFF.

  • ON
    L'index existant est supprimé et reconstruit. 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é avec DROP_EXISTING. En outre, un index primaire XML ne peut pas être redéfini comme index XML secondaire, et inversement.

• ONLINE **=**OFF
  Indique que les tables sous-jacentes et les index associés ne sont pas disponibles pour les requêtes et la modification de données pendant l'opération d'indexation. Dans cette version de SQL Server, les constructions d'index en ligne ne sont pas prises en charge pour les index XML. Si cette option est activée (ON) pour un index XML, une erreur est déclenchée. Omettez l'option ONLINE ou attribuez la valeur OFF à ONLINE.

  Une opération d'index hors connexion qui crée, reconstruit ou supprime un index XML acquiert un verrou Sch-M (Modification du schéma) sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération.

  [!REMARQUE]

  Les opérations d'index en ligne ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

• 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 le moment où les verrous de page sont utilisés.

  • OFF
    Les verrous de page ne sont pas utilisés.

• MAXDOP **=**max_degree_of_parallelism
  Remplace l'option de configuration max degree of parallelism pendant la durée de l'indexation. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plans parallèles. Le maximum est de 64 processeurs.

  Important

  Bien que l'option MAXDOP soit prise en charge syntaxiquement pour tous les index XML, pour un index XML primaire, CREATE XML INDEX utilise uniquement un processeur unique.

  Valeurs possibles de max_degree_of_parallelism :

  • 1
    Supprime la génération de plans parallèles.

  • >1
    Limite le nombre maximal de processeurs utilisés dans l'opération d'index parallèle au nombre défini ou à un nombre inférieur en fonction de la charge de travail actuelle du système.

  • 0 (valeur par défaut)
    Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

  Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.

  [!REMARQUE]

  Les opérations d'index parallèles ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

Notes

Les colonnes calculées dérivées des types de données xml peuvent être indexées comme clés ou colonnes non-clés incluses dès lors que les types de colonnes calculées peuvent être utilisés comme colonnes clé d'index ou colonnes non-clés. Vous ne pouvez pas créer un index XML primaire sur une colonne xml calculée.

Pour afficher des informations sur les index XML, utilisez l'affichage catalogue sys.xml_indexes.

Pour plus d'informations sur les index XML, consultez Index sur les colonnes de type de données XML.

Remarques supplémentaires sur la création d'index

Pour plus d'informations sur la création d'index, consultez la section « Remarques » dans CREATE INDEX (Transact-SQL).

Exemples

A. Création d'un index XML primaire

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

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

B. Création d'un index XML secondaire

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

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

Voir aussi

Référence

ALTER INDEX (Transact-SQL)

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

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

Consignes générales pour la création d'index

Index sur les colonnes de type de données XML

Index sur les colonnes de type de données XML

Autres ressources

Détermination de l'espace disque requis par les index

Tables et architecture de structures de données d'index