Indizes für Spalten vom xml-Datentyp

XML-Instanzen werden in Spalten vom Typ xml als BLOBs (Binary Large Objects) gespeichert. Diese XML-Instanzen können groß sein, und die gespeicherte binäre Darstellung von Instanzen vom Datentyp xml kann bis zu 2 GB groß sein. Ohne Index werden diese BLOBs zur Laufzeit aufgeteilt, um eine Abfrage auszuwerten. Diese Aufteilung kann zeitaufwändig sein. Angenommen, beispielsweise liegt die folgende Abfrage vor:

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

Um die XML-Instanzen auszuwählen, die die Bedingung in der WHERE-Klausel erfüllen, wird der XML-BLOB (Binary Large Object) in jeder Zeile der Production.ProductModel-Tabelle zur Laufzeit aufgeteilt. Dann wird der Ausdruck (/PD:ProductDescription/@ProductModelID[.="19"]) in der exist()-Methode ausgewertet. Diese Aufteilung zur Laufzeit kann abhängig von der Größe und Anzahl der in der Spalte gespeicherten Instanzen kostenintensiv sein.

Wenn das Abfragen von XML-BLOBs in Ihrer Anwendungsumgebung häufig vorkommt, ist das Indizieren der Spalten des Datentyps xml hilfreich. Das Verwalten des Indexes während der Datenänderung verursacht jedoch auch Kosten.

XML-Indizes fallen in die folgenden Kategorien:

• Primärer XML-Index
• Sekundärer XML-Index

Der erste Index für die Spalte des Datentyps xml muss der primäre XML-Index sein. Mithilfe des primären XML-Indexes werden drei Arten sekundärer Indizes unterstützt: PATH, VALUE und PROPERTY. Abhängig vom Typ der Abfragen können diese sekundären Indizes die Abfrageleistung steigern.

Primärer XML-Index

Der primäre XML-Index ist eine aufgeteilte und dauerhafte Darstellung der XML-BLOBs in der xml-Datentypspalte. Für jeden XML-BLOB in der Spalte erstellt der Index mehrere Datenzeilen. Die Anzahl der Zeilen im Index entspricht ungefähr der Anzahl der Knoten im XML-BLOB.

Jede Zeile speichert die folgenden Knoteninformationen:

• Tagname, z. B. einen Element- oder Attributnamen.
• Knotenwert.
• Knotentyp, z. B. Elementknoten, Attributknoten oder Textknoten.
• Informationen zur Dokumentreihenfolge, die durch einen internen Knotenbezeichner dargestellt wird.
• Pfad von jedem Knoten zum Stamm der XML-Struktur. Diese Spalte wird in der Abfrage nach path-Ausdrücken durchsucht.
• Primärschlüssel der Basistabelle. Der Primärschlüssel der Basistabelle wird im primären XML-Index für die Rückwärtsverknüpfung mit der Basistabelle dupliziert, und die maximale Anzahl von Spalten im Primärschlüssel der Basistabelle ist auf 15 beschränkt.

Diese Knoteninformationen werden zum Auswerten und Erstellen der XML-Ergebnisse für eine angegebene Abfrage verwendet. Zu Optimierungszwecken werden der Tagname und die Knotentypinformationen als ganze Zahlen codiert; die Path-Spalte verwendet die gleiche Codierung. Pfade werden außerdem in umgekehrter Reihenfolge gespeichert, damit eine Pfadzuordnung erfolgen kann, wenn nur das Pfadsuffix bekannt ist. Beispiel:

• //ContactRecord/PhoneNumber, wobei nur die beiden letzten Schritte bekannt sind.

ODER:

• /Book/*/Title; dabei wird das Platzhalterzeichen (*) in der Mitte des Ausdrucks angegeben.

Der Abfrageprozessor verwendet den primären XML-Index für Abfragen, die XML-Datentypmethoden einschließen, und entweder Skalarwerte oder die XML-Teilbäume vom primärem Index selbst wiedergeben. (Dieser Index speichert alle notwendigen Informationen, um die XML-Instanz zu rekonstruieren).

Die folgende Abfrage gibt z. B. in der CatalogDescription-Spalte vom Typ xml gespeicherte Zusammenfassungsinformationen aus der ProductModel-Tabelle zurück. Die Abfrage gibt <Summary>-Informationen nur für die Produktmodelle zurück, deren Katalogbeschreibung auch die <Features>-Beschreibung speichert.

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

Hinsichtlich des primären XML-Indexes werden die einzelnen XML-BLOB-Instanzen in der Basistabelle nicht aufgeteilt, sondern die Zeilen in dem Index, der dem jeweiligen XML-BLOB entspricht, werden sequenziell nach dem in der exist()-Methode angegebenen Ausdruck durchsucht. Wenn der Pfad in der Path-Spalte im Index gefunden wird, wird das <Summary>-Element mit seinen Unterstrukturen aus dem primären XML-Index abgerufen und in einen XML-BLOB als Ergebnis der query()-Methode konvertiert.

Beachten Sie, dass der primäre XML-Index beim Abrufen einer vollständigen XML-Instanz nicht verwendet wird. Die folgende Abfrage ruft z. B. die gesamte XML-Instanz aus der Tabelle ab, die die Produktionsanweisungen für ein bestimmtes Produktmodell beschreibt.

USE AdventureWorks;

SELECT Instructions
FROM Production.ProductModel 
WHERE ProductModelID=7;

Sekundäre XML-Indizes

Wenn Sie die Suchleistung verbessern möchten, können Sie sekundäre XML-Indizes erstellen. Ein primärer XML-Index muss zuerst beendet werden, bevor Sie sekundäre Indizes erstellen können. Die folgenden Typen werden unterschieden:

• Sekundärer XML PATH-Index
• Sekundärer XML VALUE-Index
• Sekundärer XML PROPERTY-Index

Sekundärer XML PATH-Index

Wenn Ihre Abfragen im Allgemeinen path-Ausdrücke für Spalten des Typs xml angeben, beschleunigt ein sekundärer PATH-Index möglicherweise die Suche. Wie bereits zuvor in diesem Thema beschrieben, ist der primäre Index hilfreich, wenn Sie Abfragen verwenden, die die exist()-Methode in der WHERE-Klausel angeben. Wenn Sie einen sekundären PATH-Index hinzufügen, können Sie die Suchleistung in solchen Abfragen möglicherweise verbessern.

Zwar vermeidet ein primärer XML-Index das Aufteilen des XML-BLOBs zur Laufzeit, er bietet jedoch möglicherweise nicht die optimale Leistung für Abfragen, die auf path-Ausdrücken basieren. Da alle Zeilen im primären XML-Index, die einem XML-BLOB entsprechen, sequenziell nach großen XML-Instanzen durchsucht werden, kann diese sequenzielle Suche langsam sein. In diesem Fall kann ein sekundärer Index, der auf den Pfad- und Knotenwerten im primären Index aufbaut, die Indexsuche erheblich beschleunigen. Im sekundären PATH-Index sind die path- und node-Werte Schlüsselspalten, die effizientere Suchläufe beim Suchen nach Pfaden ermöglichen. Der Abfrageoptimierer kann den PATH-Index für Ausdrücke wie die im folgenden Beispiel gezeigten verwenden:

• /root/Location; gibt nur den Pfad an.

ODER:

• /root/Location/@LocationID[.="10"]; gibt den Pfad- und Knotenwert an.

Die folgende Abfrage zeigt, in welchen Fällen der PATH-Index hilfreich ist:

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

In der Abfrage entsprechen der path-Ausdruck /PD:ProductDescription/@ProductModelID und der Wert "19" in der exist()-Methode den Schlüsselfeldern des PATH-Indexes. Auf diese Weise wird die direkte Suche im PATH-Index ermöglicht und eine bessere Suchleistung als bei einer sequenziellen Suche nach path-Werten im primären Index bereitgestellt.

Sekundärer XML VALUE-Index

Wenn Abfragen wertbasiert sind, z. B. /Root/ProductDescription/@*[. = "Mountain Bike"] oder //ProductDescription[@Name = "Mountain Bike"] und der Pfad nicht vollständig angegeben wird oder einen Platzhalter enthält, erzielen Sie möglicherweise schnellere Ergebnisse, indem Sie einen sekundären XML-Index erstellen, der auf Knotenwerten im primären XML-Index basiert.

Die Schlüsselspalten des VALUE-Indexes sind der Knotenwert und der Pfad des primären XML-Indexes. Wenn die Arbeitslast das Abfragen von Werten aus XML-Instanzen umfasst, ohne dass die Element- oder Attributnamen bekannt sind, die die Werte enthalten, kann der VALUE-Index hilfreich sein. Der folgende Ausdruck profitiert z. B. vom Vorhandensein eines VALUE-Indexes:

• //author[LastName="someName"], wobei der Wert des <LastName>-Elements bekannt ist, das übergeordnete <author>-Element jedoch an beliebiger Position auftreten kann.
• /book[@* = "someValue"], wobei die Abfrage nach dem <book>-Element sucht, das ein Attribut mit dem Wert "someValue" aufweist.

Die folgende Abfrage gibt ContactID aus der Contact-Tabelle zurück. Die WHERE-Klausel gibt einen Filter an, der nach Werten in der AdditionalContactInfo-Spalte vom Typ xml sucht. Die Kontakt-IDs werden nur zurückgegeben, wenn der entsprechende XML-BLOB mit den zusätzlichen Kontaktinformationen eine bestimmte Rufnummer enthält. Da das <telephoneNumber>-Element an beliebiger Position im XML auftreten kann, gibt der path-Ausdruck die nachfolgende oder Selbstachse an.

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

In dieser Situation ist der Suchwert für <number> bekannt, er kann jedoch an beliebiger Position in der XML-Instanz als untergeordnetes Element des <telephoneNumber>-Elements angezeigt werden. Diese Art der Abfrage kann möglicherweise von einer Indexsuche profitieren, die auf einem bestimmten Wert basiert.

Sekundärer PROPERTY-Index

Abfragen, die einen oder mehrere Werte aus einzelnen XML-Instanzen abrufen, können möglicherweise von einem PROPERTY-Index profitieren. Dieses Szenario tritt ein, wenn Sie Objekteigenschaften mithilfe der value()-Methode des Datentyps xml abrufen und der Wert des Primärschlüssels des Objekts bekannt ist.

Der PROPERTY-Index basiert auf Spalten (PK, Pfad- und Knotenwert) des primären XML-Indexes, wobei PK der Primärschlüssel der Basistabelle ist.

Die folgende Abfrage ruft z. B. für Produktmodell 19 die folgenden ProductModelID- und ProductModelName-Attributwerte mithilfe der value()-Methode ab. Der PROPERTY-Index kann eine schnellere Ausführung als die Verwendung des primären XML-Indexes oder der anderen sekundären XML-Indizes bereitstellen.

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

Mit Ausnahme der Unterschiede, die weiter unten in diesem Thema beschrieben werden, gleicht das Erstellen eines XML-Indexes für eine Spalte vom Typ xml dem Erstellen eines Indexes für eine Spalte, die nicht den Typ xml aufweist. Die folgenden Transact-SQL-DDL-Anweisungen können zum Erstellen und Verwalten von XML-Indizes verwendet werden:

• CREATE PRIMARY XML INDEX
• ALTER INDEX
• DROP INDEX

Erstellen eines primären XML-Indexes

Verwenden Sie die CREATE PRIMARY XML INDEX Transact-SQL-DDL-Anweisung, um einen primären XML-Index zu erstellen. Nicht alle Optionen, die für Nicht-XML-Indizes verfügbar sind, werden für XML-Indizes unterstützt.

Beachten Sie beim Erstellen eines XML-Indexes Folgendes:

• Damit ein primärer XML-Index erstellt werden kann, muss die Tabelle, die die zu indizierende XML-Spalte enthält (als Basistabelle bezeichnet), einen gruppierten Index für den Primärschlüssel enthalten. Auf diese Weise wird sichergestellt, dass der primäre XML-Index mithilfe des gleichen Partitionierungsschemas und der gleichen Partitionierungsfunktion partitioniert werden kann, wenn die Basistabelle partitioniert ist.
• Wenn ein XML-Index vorhanden ist, kann der gruppierte Primärschlüssel der Tabelle nicht geändert werden. Sie müssen alle XML-Indizes für die Tabelle löschen, bevor Sie den Primärschlüssel ändern können.
• Ein primärer XML-Index kann für eine einzelne Spalte vom Typ xml erstellt werden. Sie können keinen anderen Indextyp mit der Spalte vom Typ XML als Schlüsselspalte erstellen. Sie können jedoch die Spalte vom Typ xml in einen Nicht-XML-Index einschließen. Jede Spalte vom Typ xml in einer Tabelle kann ihren eigenen primären XML-Index aufweisen. Es ist jedoch nur ein primärer XML-Index pro Spalte vom Typ xml zulässig.
• XML-Indizes werden im gleichen Namespace wie Nicht-XML-Indizes gespeichert. Aus diesem Grund dürfen ein XML-Index und ein Nicht-XML-Index für die gleiche Tabelle nicht den gleichen Namen besitzen.
• Die Optionen IGNORE_DUP_KEY- und ONLINE werden für XML-Indizes immer auf OFF festgelegt. Sie können diese Optionen mit einem Wert von OFF angeben.
• Die Dateigruppen- oder Partitionierungsinformationen der user-Tabelle werden auf den XML-Index angewendet. Benutzer können diese nicht separat für einen XML-Index angeben.
• Die DROP_EXISTING-Indexoption kann einen primären XML-Index löschen und einen neuen primären XML-Index erstellen oder einen sekundären XML-Index löschen und einen neuen sekundären XML-Index erstellen. Mit dieser Option kann jedoch nicht ein sekundärer XML-Index gelöscht werden, um einen neuen primären XML-Index zu erstellen oder umgekehrt.
• Für die Namen primärer XML-Indizes gelten die gleichen Einschränkungen wie für Sichtnamen.

Sie können keinen XML-Index für eine Spalte vom Typ xml in einer Sicht für eine Tabellenwertvariable mit Spalten vom Typ xml oder Variablen vom Typ xml erstellen.

• Wenn Sie eine Spalte vom Typ xml mithilfe der Option ALTER TABLE ALTER COLUMN aus nicht typisiertem in typisiertes XML oder umgekehrt ändern möchten, sollte kein XML-Index für die Spalte vorhanden sein. Wenn ein XML-Index vorhanden ist, muss dieser gelöscht werden, bevor der Änderungsversuch des Spaltentyps unternommen wird.
• Die Option ARITHABORT muss auf ON festgelegt werden, wenn ein XML-Index erstellt wird. Zum Abfragen, Einfügen, Löschen oder Aktualisieren von Werten in der XML-Spalte mithilfe der Methoden des XML-Datentyps muss die gleiche Option für die Verbindung festgelegt werden. Wenn dies nicht der Fall ist, schlagen die Methoden des XML-Datentyps fehl.

  Hinweis:
  Informationen zu einem XML-Index finden Sie in den Katalogsichten. sp_helpindex wird jedoch nicht unterstützt. Beispiele, die weiter unten in diesem Abschnitt bereitgestellt werden, zeigen, wie die Katalogsichten zum Ermitteln von XML-Indexinformationen abgefragt werden.

Erstellen eines sekundären XML-Indexes

Verwenden Sie die CREATE XML INDEXTransact-SQL-DDL-Anweisung zum Erstellen sekundärer XML-Indizes und zum Angeben des Typs des gewünschten sekundären XML-Indexes.

Beachten Sie beim Erstellen sekundärer XML-Indizes Folgendes:

• Alle Indizierungsoptionen, die für einen nicht gruppierten Index gelten, sind mit Ausnahme von IGNORE_DUP_KEY und ONLINE für sekundäre XML-Indizes zulässig. Die beiden Optionen müssen für sekundäre XML-Indizes immer auf OFF festgelegt sein.
• Die sekundären Indizes werden ebenso wie der primäre XML-Index partitioniert.
• DROP_EXISTING kann einen sekundären Index für die user-Tabelle löschen und einen anderen sekundären Index für die user-Tabelle erstellen.

Sie können die sys.xml_indexes-Katalogsicht abfragen, um XML-Indexinformationen abzurufen. Beachten Sie, dass die secondary_type_desc-Spalte in der sys.xml_indexes-Katalogsicht den Typ des sekundären Indexes bereitstellt.

SELECT  * 
FROM    sys.xml_indexes

Der zurückgegebene Wert in der secondary_type_desc-Spalte kann NULL, PATH, VALUE oder PROPERTY sein. Für den primären Index lautet der zurückgegebene Wert NULL.

Ändern eines XML-Indexes

Die ALTER INDEX Transact-SQL-DDL-Anweisung kann zum Ändern vorhandener und nicht vorhandener XML-Indizes verwendet werden. Nicht alle ALTER INDEX-Optionen sind jedoch für XML-Indizes verfügbar. Die folgenden Optionen sind beim Ändern von XML-Indizes nicht zulässig:

• Die REBUILD- und SET-Option IGNORE_DUP_KEY ist für XML-Indizes nicht zulässig. Die REBUILD-Option ONLINE muss für sekundäre XML-Indizes auf OFF festgelegt werden. Die Option DROP_EXISTING ist in der ALTER INDEX-Anweisung nicht zulässig. Wenn der Index neu erstellt wird, müssen Verbindungsoptionen festgelegt werden, wie unter Festlegen von Optionen (XML-Index) beschrieben.
• Die Änderungen der primary key-Einschränkung in der user-Tabelle werden nicht automatisch an XML-Indizes weitergegeben. Der Benutzer muss die XML-Indizes zuerst löschen und dann neu erstellen.
• Wenn ALTER INDEX ALL angegeben wird, gilt dies für Nicht-XML- und XML-Indizes. Möglicherweise werden Indizierungsoptionen angegeben, die nicht für beide Indextypen zulässig sind. In diesem Fall schlägt die gesamte Anweisung fehl.

Löschen eines XML-Indexes

Die DROP INDEX Transact-SQL-Anweisung kann zum Löschen vorhandener primärer oder sekundärer XML- und Nicht-XML-Indizes verwendet werden. Die DROP INDEX-Optionen gelten jedoch nicht für XML-Indizes. Wenn Sie den primären XML-Index löschen, werden sämtliche vorhandenen sekundären Indizes ebenfalls gelöscht.

Die DROP-Syntax mit TableName**.**IndexName ist veraltet und wird für XML-Indizes nicht unterstützt.

Beispiele:

In den folgenden Beispielen wird gezeigt, wie XML-Indizes erstellt, geändert und gelöscht werden.

A. Erstellen und Löschen eines primären XML-Indexes

Im folgenden Beispiel wird ein XML-Index für eine Spalte vom Typ xml erstellt:

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

Beim Löschen einer Tabelle werden auch XML-Indizes für diese automatisch gelöscht. Eine XML-Spalte kann jedoch nicht aus einer Tabelle gelöscht werden, wenn ein XML-Index für die Spalte vorhanden ist.

Im folgenden Beispiel wird ein XML-Index für eine Spalte vom Typ xml erstellt: Weitere Informationen finden Sie unter Typisiertes im Vergleich zu nicht typisiertem XML.

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

Nun können Sie einen primären XML-Index für Co12 erstellen:

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

B. Erstellen sekundärer XML-Indizes

Das folgende Beispiel zeigt, wie sekundäre XML-Indizes erstellt werden. Im Beispiel werden außerdem Informationen zu den von Ihnen erstellten XML-Indizes bereitgestellt.

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

Sie können die sys.xml_indexes-Katalogsicht abfragen, um XML-Indexinformationen abzurufen. Der Typ wird in der secondary_type_desc-Spalte des zweiten Indizes bereitgestellt.

SELECT  * 
FROM    sys.xml_indexes

Sie können auch die Katalogsicht nach Indexinformationen abfragen.

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

Sie können Beispieldaten hinzufügen und anschließend die XML-Indexinformationen überprüfen.

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. Ändern eines XML-Indexes

Im folgenden Beispiel wird ein XML-Index erstellt und dann durch Festlegen der Option ALLOW_ROW_LOCKS auf OFF geändert. Wenn ALLOW_ROW_LOCKS auf OFF festgelegt wurde, sind die Zeilen gesperrt, und der Zugriff auf die angegebenen Indizes erfolgt über die Sperren auf Seiten- und Tabellenebene.

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. Deaktivieren und Aktivieren eines XML-Indexes

Standardmäßig ist ein XML-Index aktiviert. Wenn ein XML-Index deaktiviert ist, verwenden die Abfragen, die für eine XML-Spalte ausgeführt werden, den XML-Index nicht. Verwenden Sie ALTER INDEX mit der Option REBUILD, um einen XML-Index zu aktivieren.

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. Erstellen eines XML-Indexes mithilfe der DROP_EXISTING-Indexoption

Im folgenden Beispiel wird ein XML-Index für eine Spalte (XmlColx) erstellt. Anschließend wird ein weiterer XML-Index mit dem gleichen Namen für eine andere Spalte (XmlColy) erstellt. Da die Option DROP_EXISTING angegeben wird, wird der vorhandene XML-Index für (XmlColx) gelöscht und ein neuer XML-Index für (XmlColy) erstellt.

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

Diese Abfrage gibt den Spaltennamen zurück, für den der angegebene XML-Index erstellt wird.

Siehe auch

Konzepte

XML-Datentyp
XML-Beispielanwendungen

Andere Ressourcen

sys.dm_db_index_physical_stats

Hilfe und Informationen

Informationsquellen für SQL Server 2005