See Also: Main_Page - Database Administration - Database Design - Indexing - XML Indexes

Keep in mind that each XML index can take up considerable space in your database. The "node" table basically translates the XML document into a relational table. The data of the node table is stored in its clustered index, which is the primary XML index of the table with the XML column. XML data type can hold up to 2GB of data per each row; translating such data into a relational table can require significant storage. So consider the querying needs of your application as well as the amount of available storage before creating XML indexes. If the column with XML data type will only be queried occasionally and performance of such queries isn't critical, you shouldn't create XML indexes.

• You can create one primary XML and three secondary indexes on each XML column. If a table has multiple XML columns you could have multiple primary XML indexes, one per each XML column.
• XML indexes are not allowed on views with a column that has the XML data type.
• XML indexes are not allowed on table variables with a column that has the XML data type.
• XML indexes are not allowed on variables with XML data type.
• XML Index must be created on the same file-group / partition as the table.
• You cannot create clustered or non-clustered indexes on columns with XML data type, but you can include such columns in a non-clustered index. Refer to "included columns" section for more information.
• You could also create full-text indexes on columns with XML data type. Please refer to Full-Text Search section of SQLServerPedia for more information about full text indexes.
• XML index cannot have the same name as another clustered or non-clustered index on the same table.
• You must set ARITHABORT option ON when creating XML indexes. Otherwise data modification statements using XML data type methods will fail.
• IGNORE_DUP_KEY has no meaning for XML indexes - this option must be set to OFF. You cannot build the XML index online, so ONLINE option must also be set to OFF.
• Secondary XML indexes are partitioned using the same partition function / scheme as the primary XML index.
• Secondary XML indexes are automatically dropped when you drop the primary XML index.
• You can use the ALTER INDEX statement to modify XML indexes; however, some options of this statement do not apply to XML indexes. If you specify "ALL" option with ALTER INDEX this statement can apply to clustered, non-clustered and XML indexes.
• XML indexes cannot be dropped using DROP INDEX table_name.index_name syntax. Instead you must state DROP INDEX index_name ON table_name.

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.