Index portant sur des colonnes de type xml

  • Article

Les instances XML sont stockées dans les colonnes de type xml sous forme de BLOB (Binary Large Objects, objets volumineux binaires). Ces instances XML peuvent donc être volumineuses et la représentation binaire stockée d'instances de type xml peut atteindre jusqu'à 2 Go. Sans index, ces objets sont fragmentés au moment de l'exécution du programme afin d'évaluer une requête, ce qui peut prendre du temps. Examinons, par exemple, la requête suivante :

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

Pour pouvoir sélectionner les instances XML satisfaisant la condition stipulée dans la clause WHERE, le BLOB XML se trouvant dans chaque ligne de la table Production.ProductModel est fragmenté au moment de l'exécution. L'expression (/PD:ProductDescription/@ProductModelID[.="19"]) tirée de la méthode exist() est ensuite évaluée. Une telle fragmentation à l'exécution peut être coûteuse selon la taille et le nombre d'instances stockées dans la colonne.

Si l'exécution de requêtes sur des BLOB est courante dans l'environnement de votre application, cette méthodologie permet d'indexer les colonnes de type xml. En contrepartie, le coût associé à la gestion de l'index lors de la modification des données est également à prendre en compte.

Les index XML sont classés en plusieurs catégories :

  • Index XML primaires
  • Index XML secondaires

Le premier index portant sur la colonne de type xml est obligatoirement l'index XML primaire. Par le biais de l'index XML primaire, les trois types d'index secondaires suivants sont pris en charge : PATH, VALUE et PROPERTY. Selon le type de requêtes, ces index secondaires peuvent contribuer à améliorer les performances liées à l'exécution de requêtes.

Index XML primaire

L'index XML primaire correspond à une représentation fragmentée et persistante des BLOB XML inclus dans la colonne des données de type xml. Pour chacun de ces BLOB de la colonne, l'index crée plusieurs lignes de données. Le nombre de lignes dans l'index est presque égal au nombre de nœuds se trouvant dans le BLOB XML.

Chaque ligne stocke les informations suivantes relatives aux nœuds :

  • le nom de la balise tel que le nom d'un élément ou d'un attribut ;
  • la valeur du nœud ;
  • le type de nœud, par exemple un nœud élément, un nœud attribut ou un nœud texte ;
  • les informations sur l'ordre du document, représentées par un identifiant de nœud interne ;
  • le chemin de chacun des nœuds vers la racine de l'arborescence XML (cette colonne fait l'objet de recherches pour y trouver la présence d'expressions de chemin d'accès mentionnées dans la requête) ;
  • la clé primaire de la table de base ; cette clé est copiée dans l'index XML primaire afin de pouvoir effectuer une jointure en retour avec la table de base et le nombre maximal de colonnes dans la clé primaire de la table de base est limité à 15.

Les informations de ce nœud sont utilisées afin d'évaluer et d'élaborer les résultats sous forme de données XML découlant d'une requête donnée. Pour des raisons d'optimisation, les informations relatives au nom de la balise et au type de nœud sont codées sous forme de valeurs entières et la colonne Path s'appuie sur ce même codage. En outre, les chemins d'accès sont stockés dans l'ordre inverse afin de pouvoir faire correspondre les chemins d'accès où seul le suffixe est connu. Par exemple :

  • //ContactRecord/PhoneNumber, où seuls les deux derniers niveaux sont connus ;

- ou -

  • /Book/*/Title, où le caractère générique (*) est mentionné au milieu de l'expression.

Le processeur de requêtes utilise l'index XML primaire dans le cas de requêtes mettant en œuvre des méthodes de données de type xml et renvoie les valeurs scalaires ou les sous-arborescences XML tirées de l'index primaire lui-même (cet index stocke toutes les informations nécessaires afin de reconstruire l'instance XML).

Par exemple, la requête suivante renvoie les informations sommaires stockées dans la colonne CatalogDescription de type xml et provenant de la table ProductModel. Elle ne renvoie les informations dans la balise <Summary> que pour les modèles de produits dont la description de catalogue stocke également la description située dans la balise <Features>.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

Concernant l'index XML primaire, au lieu de fragmenter chaque instance de BLOB XML se trouvant dans la table de base, les lignes de l'index correspondant à chaque BLOB font l'objet de recherches séquentielles pour retrouver l'expression indiquée dans la méthode exist(). Si le chemin est retrouvé dans la colonne Path de l'index, l'élément <Summary> ainsi que ses sous-arborescences sont récupérés de l'index XML primaire, puis convertis en un BLOB XML suite à l'exécution de la méthode query().

Notez que l'index XML primaire n'est pas sollicité lors de la récupération d'une instance XML complète. Par exemple, la requête suivante extrait de la table l'instance XML tout entière décrivant les instructions de fabrication d'un modèle de produit donné.

USE AdventureWorks;

SELECT Instructions
FROM Production.ProductModel 
WHERE ProductModelID=7;

Index XML secondaire

Afin d'améliorer les performances lors des recherches, vous pouvez créer des index XML secondaires. Un index XML primaire doit être défini au préalable avant de pouvoir créer des index secondaires. Il en existe trois types différents :

  • les index XML secondaires de type PATH (s'appuyant sur le chemin d'accès) ;
  • les index XML secondaires de type VALUE (utilisant la valeur comme critère de recherche) ;
  • et les index XML secondaires de type PROPERTY (pour rechercher des données d'après leurs propriétés).

Index XML secondaire de type PATH

Si vos requêtes précisent habituellement les expressions de chemin d'accès sur les colonnes de type xml, un index secondaire de type PATH peut s'avérer à même d'accélérer la recherche. Comme nous l'avons vu précédemment, l'index primaire est des plus utiles pour les requêtes indiquant la méthode exist() dans leur clause WHERE. Si vous ajoutez à présent un index secondaire de type PATH, il se peut que vous amélioriez encore la rapidité de la recherche lancée par de telles requêtes.

Bien qu'un index XML primaire évite de fragmenter les BLOB XML à l'exécution, il peut ne pas offrir les meilleurs temps de réponse pour les requêtes s'appuyant sur des expressions de chemin d'accès. Une recherche, lancée sur toutes les lignes constituant l'index XML primaire correspondant à un BLOB XML et s'opérant de façon séquentielle pour des instances XML volumineuses, peut s'avérer des plus lentes. Dans ce cas, un index secondaire construit sur les valeurs de chemin d'accès et sur celles des nœuds de l'index primaire peut accélérer de façon significative la recherche sur l'index. Dans le cas d'un index XML secondaire de type PATH, les valeurs de chemin d'accès et des nœuds correspondent à des colonnes clés permettant donc des recherches plus efficaces si ces dernières portent sur le chemin d'accès. Il se peut que l'optimiseur de requête utilise l'index de type PATH dans des expressions telles que celles mentionnées ci-dessous :

  • /root/Location, n'indiquant que son chemin d'accès ;

- ou -

  • /root/Location/@LocationID[.="10"], où le chemin et la valeur du nœud sont précisés.

La requête suivante illustre un cas de figure où l'index de type PATH s'avère particulièrement utile :

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

Dans la requête, l'expression de chemin d'accès /PD:ProductDescription/@ProductModelID et sa valeur "19" se trouvant dans la méthode exist() correspondent aux champs de clé définis pour l'index de type PATH. Des recherches directes portant sur l'index PATH peuvent s'effectuer et offrir ainsi de meilleures performances que dans le cas d'une recherche séquentielle portant sur les valeurs de chemin d'accès de l'index primaire.

Index XML secondaire de type VALUE

Si des requêtes s'appuient sur les valeurs pour ses résultats, par exemple /Root/ProductDescription/@*[. = "Mountain Bike"] ou //ProductDescription[@Name = "Mountain Bike"], et que le chemin n'est pas entièrement précisé ou qu'il contient un caractère générique, vous pourriez obtenir des résultats plus rapidement en construisant un index XML secondaire sur les valeurs des nœuds de l'index XML primaire.

Les colonnes clés (correspondant aux valeurs de nœud et aux chemins d'accès) de l'index de type VALUE sont tirées de l'index XML primaire. Si vous devez lancer des requêtes sur des valeurs provenant d'instances XML sans connaître le nom des éléments ou des attributs contenant les valeurs, il se peut que l'index de type VALUE vous soit particulièrement utile. Par exemple, l'expression suivante tire parti de l'utilisation d'un index de type VALUE :

  • //author[LastName="someName"], où vous connaissez la valeur de l'élément <LastName>, mais où le parent <author> peut se placer n'importe où.
  • /book[@* = "someValue"], où la requête recherche l'élément <book> dont un attribut possède la valeur "someValue".

La requête suivante renvoie ContactID à partir de la table Contact. La clause WHERE indique un filtre permettant de rechercher des valeurs dans la colonne AdditionalContactInfo de type xml. L'ID des contacts n'est renvoyé que si le BLOB XML relatif aux informations complémentaires sur les contacts correspondants contient un numéro de téléphone spécifique. Puisque l'élément <telephoneNumber> peut apparaître n'importe où dans le document XML, l'expression du chemin d'accès indique l'axe descendant-or-self.

WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT)

SELECT ContactID 
FROM   Person.Contact
WHERE  AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1

Dans ce cas, nous connaissons donc la valeur de recherche correspondant à <number> mais elle peut se trouver n'importe où dans l'instance XML en tant qu'enfant de l'élément <telephoneNumber>. Ce type de requête pourrait être plus efficace grâce à la recherche d'une valeur précise dans les index.

Index secondaire de type PROPERTY

Les requêtes chargées d'extraire plusieurs valeurs d'instances XML distinctes peuvent bénéficier de l'utilisation d'un index de type PROPERTY. Un scénario type de cette utilisation s'illustre lorsque vous récupérez les propriétés d'objets par le biais de la méthode value() de type xml et que la valeur de la clé primaire de l'objet en question est connue.

L'index utilisant le paramètre PROPERTY se construit d'après des colonnes (PK pour la clé primaire, Path pour le chemin d'accès, ainsi que la valeur du nœud) issues de l'index XML primaire, où PK correspond à la clé primaire de la table de base.

Par exemple, concernant le modèle de produit 19, la requête suivante extrait les valeurs des attributs ProductModelID et ProductModelName grâce à la méthode value(). Contrairement aux requêtes utilisant les index XML primaires ou tout autre index XML secondaire, l'index PROPERTY peut permettre une exécution plus rapide.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') as ModelID,
       CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') as ModelName        
FROM Production.ProductModel   
WHERE ProductModelID = 19

À part les différences décrites plus loin dans cette rubrique, la création d'un index XML portant sur une colonne de type xml est similaire à celle d'un index portant sur une colonne de type non xml. Les instructions DDL Transact-SQL suivantes permettent de créer et de gérer les index XML :

Création d'un index XML primaire

Pour créer un index XML primaire, utilisez l'instruction DDL Transact-SQL CREATE PRIMARY XML INDEX. Les options disponibles aux index non XML ne sont pas toutes prises en charge pour les index XML.

Lorsque vous créez un index XML, prenez les points suivants en considération :

  • Pour créer un index XML primaire, la table contenant la colonne XML en cours d'indexation, appelée table de base, doit posséder un index cluster portant sur la clé primaire. Ceci permet de s'assurer que, si la table de base est partitionnée, l'index XML primaire peut être partitionné grâce au même schéma de partitionnement et à la même fonction de partitionnement.
  • Si un index XML existe, la clé primaire mise en cluster de la table ne peut alors pas être modifiée. Vous devez dans ce cas supprimer tous les index XML de la table avant de pouvoir modifier la clé primaire.
  • Un index XML primaire portant sur une seule colonne de type xml peut être créé. Aucun autre type d'index relatif à la colonne de type XML ne peut être créé en tant que colonne clé. Vous pouvez cependant inclure la colonne de type xml dans un index non XML. Chaque colonne de type xml d'une table peut présenter son propre index XML primaire. Ceci dit, un seul index XML primaire est autorisé par colonne de type xml.
  • Les index XML existent dans le même espace de noms que les index non XML. Vous ne pouvez par conséquent pas avoir un index XML et un index non XML portant tous deux le même nom pour une même table.
  • Les options IGNORE_DUP_KEY et ONLINE sont toujours définies sur OFF (c'est-à-dire désactivées) pour les index XML. Vous pouvez indiquer la valeur OFF pour ces options.
  • Les informations de groupes de fichiers ou de partitionnement de la table utilisateur s'appliquent à l'index XML. Les utilisateurs peuvent indiquer ces informations séparément pour un index XML.
  • L'option d'index DROP_EXISTING peut supprimer un index XML primaire et en recréer un, ou supprimer un index XML secondaire et en recréer un. Elle ne peut cependant pas entraîner la suppression d'index XML secondaire pour créer un index XML primaire ou vice versa.
  • Les noms s'appliquant aux index XML primaires sont soumis aux mêmes restrictions que les noms de vues.

Vous ne pouvez donc pas créer d'index XML portant sur une colonne de type xml dans une vue, sur une variable affectée d'une valeur de type table et possédant des colonnes de type xml ou encore des variables de type xml.

  • Pour modifier une colonne de type xml non typé en XML typé ou vice versa par le biais de l'option ALTER TABLE ALTER COLUMN, assurez-vous qu'aucun index XML portant sur la colonne n'existe. Si c'est le cas, il doit être supprimé avant de pouvoir tenter de modifier le type de colonne.
  • L'option ARITHABORT doit être activée (c'est-à-dire définie sur ON) lors de la création d'un index XML. Pour pouvoir interroger, insérer, supprimer ou mettre à jour des valeurs de la colonne XML par le biais des méthodes portant sur des données de type XML, cette même option doit être définie sur la connexion aux données. Dans le cas contraire, les méthodes portant sur les données de type XML échouent.
    ms191497.note(fr-fr,SQL.90).gifRemarque :
    Des informations à propos d'un index XML donné sont répertoriées dans les affichages catalogue. La procédure sp_helpindex n'est cependant pas prise en charge. Des exemples fournis plus loin dans cette rubrique illustrent comment lancer une requête sur les affichages catalogue afin de retrouver les informations propres aux index XML.

Création d'un index XML secondaire

Utilisez l'instruction DDL Transact-SQL CREATE XML INDEX pour créer des index XML secondaires et préciser leur type voulu.

Lorsque vous créez un index XML secondaire, prenez les points suivants en considération :

  • Toutes les options d'indexation qui s'appliquent à un index non-cluster, hors les options IGNORE_DUP_KEY et ONLINE, sont autorisées sur les index XML secondaires. Ces deux options doivent toujours être définies sur OFF dans le cas d'index XML secondaires.
  • Les index secondaires sont partitionnés de la même manière que l'index XML primaire.
  • DROP_EXISTING permet de supprimer un index secondaire sur la table utilisateur pour en recréer un.

Vous pouvez passer par une requête sur l'affichage catalogue sys.xml_indexes afin d'extraire les informations relatives aux index XML. Il reste à noter que la colonne secondary_type_desc se trouvant dans l'affichage catalogue sys.xml_indexes fournit le type d'index secondaire.

SELECT  * 
FROM    sys.xml_indexes

Les valeurs renvoyées dans la colonne secondary_type_desc peuvent être NULL, PATH, VALUE ou PROPERTY. Pour l'index XML primaire, la valeur renvoyée correspond à NULL.

Modification d'un index XML

L'insctruction DLL Transact-SQL ALTER INDEX permet de modifier des index XML et non XML. Les options ALTER INDEX ne sont cependant pas toutes disponibles aux index XML. Les options suivantes ne sont pas valides si vous modifiez des index XML :

  • L'option de reconstruction et de définition de valeur IGNORE_DUP_KEY n'est pas valide dans le cas d'utilisation d'index XML. L'option de reconstruction ONLINE doit toujours être définie sur OFF dans le cas d'index XML secondaires. L'option DROP_EXISTING n'est pas autorisée dans l'instruction ALTER INDEX. Lors de la reconstruction de l'index, les options de connexion doivent être définies d'après ce qui est décrit dans Définition des options (index XML).
  • Les modifications apportées à la contrainte de clé primaire de la table utilisateur ne se transmettent pas automatiquement aux index XML. L'utilisateur doit dans ce cas supprimer les index XML en premier puis les recréer.
  • Si l'option ALTER INDEX ALL est précisée, elle s'applique aussi bien aux index non XML qu'aux index XML. Il se peut que les options d'indexation soient indiquées comme n'étant pas valides pour ces deux types d'index. Dans ce cas, l'instruction entière est ignorée.

Suppression d'un index XML

L'instruction Transact-SQL DROP INDEX permet de supprimer des index XML et non XML, qu'ils soient primaires ou secondaires. Les options DROP INDEX ne s'appliquent cependant pas aux index XML. Si vous supprimez l'index XML primaire, tous les index secondaires présents sont alors également supprimés.

La syntaxe DROP avec TableName**.**IndexName est cependant en cours de retrait des fonctionnalités et n'est pas prise en charge pour les index XML.

Exemples

Les exemples suivants illustrent la création, la modification et la suppression d'index XML.

A. Création puis suppression d'un index XML primaire

L'exemple suivant propose la création d'un index XML portant sur une colonne de type xml.

DROP TABLE T
GO
CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create Primary XML index 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Verify the index creation. 
-- Note index type is 3 for xml indexes.
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol' 
-- Drop the index.
DROP INDEX PIdx_T_XmlCol ON T

Lorsqu'une table est supprimée, tous les index XML qui lui sont associés sont également automatiquement supprimés. Ceci dit, une colonne XML ne peut pas être supprimée d'une table si un index XML existe sur cette colonne.

L'exemple suivant propose la création d'un index XML portant sur une colonne de type xml. Pour plus d'informations, consultez XML typé et non typé.

CREATE TABLE TestTable(
 Col1 int primary key, 
 Col2 xml (Production.ProductDescriptionSchemaCollection)) 
GO

Vous pouvez à présent créer un index XML primaire relatif à Co12.

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

B. Création d'index XML secondaires

L'exemple suivant illustre le mode de création d'index XML secondaires. Il montre également les informations relatives aux index XML que vous avez créés.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR VALUE
GO
CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PROPERTY
GO

Vous pouvez interroger le sys.xml_indexes afin d'extraire des informations relatives aux index XML. La colonne secondary_type_desc fournit le type d'index secondaire.

SELECT  * 
FROM    sys.xml_indexes

Vous pouvez également interroger l'affichage catalogue pour obtenir les informations sur l'index.

SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')

Vous pouvez ajouter des exemples de données puis passer en revue les informations de l'index XML.

INSERT INTO T VALUES (1,
'<doc id="123">
<sections>
<section num="2">
<heading>Background</heading>
</section>
<section num="3">
<heading>Sort</heading>
</section>
<section num="4">
<heading>Search</heading>
</section>
</sections>
</doc>')
GO
-- Check XML index information.
SELECT *
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED')
GO
-- Space usage of primary XML index
DECLARE @index_id int
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i 
WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
--- Space usage of secondary XML index (for example PATH secondary index)  PIdx_T_XmlCol_PATH
DECLARE @index_id int
SELECT  @index_id = i.index_id 
FROM    sys.xml_indexes i 
WHERE  i.name = 'PIdx_T_XmlCol_PATH' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
 
-- Space usage of all secondary XML indexes for a particular table
SELECT i.name, object_name(i.object_id), stats.* 
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats
JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)
WHERE secondary_type is not null
-- Drop secondary indexes.
DROP INDEX PIdx_T_XmlCol_PATH ON T
GO
DROP INDEX PIdx_T_XmlCol_VALUE ON T
GO
DROP INDEX PIdx_T_XmlCol_PROPERTY ON T
GO
-- Drop primary index.
DROP INDEX PIdx_T_XmlCol ON T
-- Drop table T.
DROP TABLE T
Go

C. Modification d'un index XML

Dans l'exemple suivant, un index XML est créé puis modifié en définissant l'option ALLOW_ROW_LOCKS sur OFF. Lorsque l'option ALLOW_ROW_LOCKS est ainsi définie sur OFF, les lignes ne sont pas verrouillées et l'accès aux index spécifiés est obtenu au moyen de verrous de niveau page et table.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary XML index. 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'

-- Modify and set an index option.
ALTER INDEX PIdx_T_XmlCol on T 
SET (ALLOW_ROW_LOCKS = OFF)

D. Désactivation et activation d'un index XML

Par défaut, un index XML est activé. S'il est désactivé par la suite, les requêtes sur la colonne XML n'utilisent alors pas l'index XML. Pour réactiver un index XML, passez par l'instruction ALTER INDEX avec l'option REBUILD.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol)
GO
ALTER INDEX PIdx_T_XmlCol on T DISABLE
Go
-- Verify index is disabled.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'
-- Rebuild the index.
ALTER INDEX PIdx_T_XmlCol on T REBUILD
Go

E. Création d'un index XML par le biais de l'option d'index DROP_EXISTING

Dans cet exemple, un index XML, s'appliquant à une colonne intitulée XmlColx, est créé. Ensuite, un autre index XML portant le même nom est également créé mais s'appliquant à une autre colonne appelée XmlColy. Puisque l'option DROP_EXISTING est précisée, l'index XML existant sur (XmlColx) est supprimé et un nouvel index sur (XmlColy) est créé.

DROP TABLE T
GO
CREATE TABLE T(Col1 int primary key, XmlColx xml, XmlColy xml)
GO
-- Create XML index on XmlColx.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColx)
GO
-- Create same name XML index on XmlColy.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColy) 
WITH (DROP_EXISTING = ON)
-- Verify the index is created on XmlColy.d.
SELECT sc.name 
FROM   sys.xml_indexes si inner join sys.index_columns sic 
ON     sic.object_id=si.object_id and sic.index_id=si.index_id
INNER  join sys.columns sc on sc.object_id=sic.object_id 
AND    sc.column_id=sic.column_id
WHERE  si.name='PIdx_T_XmlCol' 
AND    si.object_id=object_id('T')

La requête renvoie le nom de la colonne sur laquelle l'index XML spécifié est créé.

Voir aussi

Concepts

Type de données xml
Exemples d'applications XML

Autres ressources

sys.dm_db_index_physical_stats

Aide et Informations

Assistance sur SQL Server 2005