Microsoft® SQL Server 2012 Unleashed

Indexing xml Columns

Three types of XML indexes are available for xml columns in SQL Server: selective, primary, and secondary. This section covers each of these index types, as well as the underlying technologies that make them work. We’ll first cover selective XML indexes since they are the most efficient and powerful.

Using Selective XML Indexes (SXIs)

Prior to SQL Server 2012, primary and secondary XML indexes were the only way to optimize stored XML content for querying. These indexes, while still relevant today and necessary for fully indexing 100% of the XML in any given column, have some pitfalls, including their huge size (upwards of 2x the size of the underlying content); sluggish data insertion performance; slow, single-threaded, ...