Physical Modeling › SQL Server Physical Property Editors › SQL Server Model Objects › SQL Server Indexes › 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 a SQL Server 2005/2008 physical model.

To define SQL Server table XML indexes

1. Click Indexes, XML on the Model menu.

   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 and work with the following options:

   Note: Click New on the toolbar to create a new table XML index.

   • 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.

3. Click the General tab and work with the following options:
   • Allow Page Locks

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

   • Allow Row Locks

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

   • Drop Existing

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

   • 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.

   • Max Parallel

     Defines the number of processors used in a parallel plan execution. Enter a number 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.

4. (Optional) Click the Comment tab and enter any comments that you want to associate with the table XML index.
5. (Optional) Click the UDP tab to work with user-defined properties for the table XML index.
6. Click Close.

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