Créer des index XML
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Cet article explique comment créer des index XML principaux et secondaires.
Créer un index XML principal
Pour créer un index XML principal, utilisez l’instruction CREATE INDEX (Transact-SQL)Transact-SQL DDL. Les options disponibles pour les index non XML ne sont pas toutes prises en charge pour les index XML.
Notez ce qui suit lorsque vous créez un index XML :
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. Cet index cluster garantit que si la table de base est partitionnée, l’index XML principal peut être partitionné à l’aide du même schéma de partitionnement et de la même fonction de partitionnement.
Si un index XML existe, la clé primaire du cluster de la table ne peut pas être modifiée. Vous devez supprimer tous les index XML de la table avant de modifier la clé primaire.
Un index XML primaire portant sur une seule colonne de type xml peut être créé. Vous ne pouvez pas créer d’autre type d’index avec la colonne de type xml comme colonne clé. Toutefois, vous pouvez 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. Par conséquent, vous ne pouvez pas avoir d’index XML et un index non XML sur la même table portant le même nom.
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.
Le groupe de fichiers ou les informations de partitionnement de la table utilisateur sont appliquées à l’index XML et ne peuvent pas être spécifiées séparément.
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. Toutefois, cette option ne peut pas supprimer un index XML secondaire pour créer un index XML principal 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 pas créer d’index XML sur une colonne de type XML dans une vue, sur unevariable table avec des colonnes de type XML ou 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 interroger, insérer, supprimer ou mettre à jour des valeurs dans la colonne XML à l’aide de méthodes de type de données XML , la même option doit être définie sur la connexion. Si ce n’est pas le cas, les méthodes de type de données xml échouent.
Note
Des informations à propos d'un index XML donné sont répertoriées dans les affichages catalogue. Toutefois, sp_helpindex n’est pas pris 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.
Lors de la création ou de la recréation d’un index XML principal sur une colonne de type de données XML qui contient des valeurs des types xs:date de schéma XML ou xs:dateTime (ou des sous-types de ces types) dont l’année est inférieure à 1, la création d’index échoue dans SQL Server 2008 (10.0.x) et les versions ultérieures. SQL Server 2005 (9.x) a autorisé ces valeurs. Ce problème peut donc se produire lors de la création d’index dans une base de données générée dans SQL Server 2005 (9.x). Pour plus d’informations, consultez Comparer du XML typé et du XML non typé.
Exemple : Créer un index XML principal
La table T (pk INT PRIMARY KEY, xCol XML) avec une colonne XML non typée est utilisée dans la plupart des exemples. Cet exemple peut être étendu au xml typé de manière simple. Par souci de simplicité, les requêtes sont décrites pour les instances de données XML, comme indiqué dans l’exemple suivant :
<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
<title>Writing Secure Code</title>
<author>
<first-name>Michael</first-name>
<last-name>Howard</last-name>
</author>
<author>
<first-name>David</first-name>
<last-name>LeBlanc</last-name>
</author>
<price>39.99</price>
</book>
L’instruction suivante crée un index XML appelé idx_xCol, sur la colonne xCol XML de la table T:
CREATE PRIMARY XML INDEX idx_xCol on T (xCol)
Créer un index XML secondaire
Utilisez l’instruction CREATE INDEX (Transact-SQL)Transact-SQL DDL pour créer des index XML secondaires et spécifier le type de l’index XML secondaire souhaité.
Notez ce qui suit lorsque vous créez des index XML secondaires :
Toutes les options d'indexation qui s'appliquent à un index non-cluster, hormis 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. La colonne secondary_type_desc de l’affichage catalogue sys.xml_indexes fournit le type d’index secondaire :
SELECT *
FROM sys.xml_indexes;
Les valeurs retournées dans la secondary_type_desc colonne peuvent être NULL, PATH, VALUE ou PROPERTY. Pour l'index XML primaire, la valeur renvoyée correspond à NULL.
Exemple : Créer des 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 la sys.xml_indexes vue catalogue pour récupérer des informations sur les 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
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour