Wednesday, 16 September 2009

Rebuilding XML indexes

To find the XML indexes in a database:

SELECT * FROM [sys].xml_indexes

To identify the primary XML index:

SELECT [sxi].name "Primary index name", [so].name "Table name" 
FROM [sys].xml_indexes sxi 
JOIN [sys].objects so ON (so.object_id = sxi.object_id) 
WHERE using_xml_index_id IS NULL

To identify the secondary XML indexes: 

SELECT sxi.name "Secondary index name", so.name "Table name" 
FROM [sys].xml_indexes sxi 
JOIN [sys].objects so ON (so.object_id = sxi.object_id) 
WHERE using_xml_index_id IS NOT NULL;

To rebuild an XML index:

ALTER INDEX [XML index name here] ON [table name here] REBUILD