Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server Property Editors › SQL Server Common Functionality › Define SQL Server Table XML Indexes

Define SQL Server Table XML Indexes

You can create a table that has one or more columns of type XML. Query execution processes each XML instance at runtime, so a mechanism for indexing XML columns is provided to speed up queries. Use the SQL Server Table XML Index Editor to define the XML columns participating in the table XML index in either a SQL Server 2008, 2012, 2014, or 2016 physical model.

To define SQL Server table XML indexes

1. Right-click a table XML index in the Model Explorer and click Properties.

   The SQL Server Table XML Index Editor opens.

2. Select the table from the Table drop-down that contains the XML index that you want to define.
3. Select the index in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a new table XML index. Use the Enter filter text box to filter a very large list of XML indexes to quickly locate the one that you want to define.

   Name

   Displays the table XML index name. You can change the table XML index name in this field.

   Column

   Specifies the XML column on which to base the table XML index.

   Primary XML Index

   Specifies if the XML column is the primary, or first, table XML index.

   Secondary XML Index Type

   Specifies a secondary table XML index type. Select one of the following types if you want to specify a secondary table XML index:

   Note: Before you can specify a secondary table XML index, you must have specified a primary table XML index.

   For Value

   Specifies to create a secondary table XML index on columns where key columns (node value and path) are of the primary table XML index.

   For Path

   Specifies to create a secondary table XML index on columns built on node values and path values in the primary table XML index. In this secondary table XML index the node and path values are the key columns that enable effective path searches.

   For Property

   Specifies to create a secondary table XML index on columns (PK, node value, and path) of the primary table XML index where PK is the primary key of the base table.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

4. Click the General tab and work with the following options:

   Fill Factor

   Defines a percentage to indicate how full the database makes the leaf level of each index page during index creation or rebuild. Enter a percentage in this field.

   Pad Index

   Specifies whether index padding is used.

   Sort In Temp DB

   Specifies whether to store temporary sort results in tempdb or in the same database as the index.

   No Recompute

   Specifies whether statistics are recomputed.

   Drop Existing

   Specifies whether the table XML index is dropped and rebuilt. Select the check box to enable this option.

   Allow Row Locks

   Specifies whether row locks are allowed when the table XML index is accessed.

   Allow Page Locks

   Specifies whether page locks are allowed when the table XML index is accessed.

   Maximum Degree of Parallelism

   Defines the number of processors used in a parallel plan execution. Enter a number in this field.

5. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
6. (Optional) Click the UDP tab to work with user-defined properties for the object.
7. (Optional) Click the Notes tab to view and edit user notes.
8. Click Close.

   The table XML index is defined and the SQL Server Table XML Index Editor closes.

More information:

Add a Comment in a Property Editor

Add a UDP in a SQL Server Physical Property Editor