XML Indexes (SQL Server)

SQL Server 2005 provides improvements in indexing XML data. PowerDesigner supports these new features through table indexes with the type set to XML.

Creating an XML Index

To create an XML index:
  1. Create a standard index and, on the General tab, select XML in the Type field.
  2. Click the Microsoft tab and specify any appropriate additional options.

XML Index Properties

You can modify an object's properties from its property sheet. To open an XML index property sheet, double-click its Browser entry.

The following extended attributes are available on the Microsoft tab:

Name

Description

Primary

Specifies that this is the primary xml index.

Scripting name: XMLPrimary

Primary index

Specifies the primary XML index to use in creating a secondary XML index.

Scripting name: PrimaryXMLIndex

Secondary XML index type

Specifies the type of the secondary XML index.

Scripting name: SecondaryXMLIndexType

Fill factor

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild.

Scripting name: FillFactor

Max degree of parallelism

Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

Scripting name: MaxDop

Pad index

Specifies index padding.

Scripting name: PadIndex

Statistics no recompute

Specifies whether distribution statistics are recomputed.

Scripting name: StatisticsNoRecompute

Drop existing

Specifies that the named, preexisting clustered, nonclustered, or XML index is dropped and rebuilt.

Scripting name: DropExisting

Sort in temporary database

Specifies whether to store temporary sort results in tempdb.

Scripting name: SortInTempDB

Allow row locks

Specifies whether row locks are allowed.

Scripting name: AllowRowLocks

Allow page locks

Specifies whether page locks are allowed.

Scripting name: AllowPageLocks