XML Indexes Dialog Box
Use the XML Indexes dialog box to create indexes for columns of the data type XML, which cannot be indexed using the Index/Keys dialog box. Each XML column can have more than one XML Index, but the first one created (primary) will be the basis of the others (secondary). If you delete the primary XML index, the secondary indexes will also be deleted.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
Selected XML Index
Lists existing XML indexes. Select to show its properties in the grid to the right. If the list is empty, none have been defined for the table.Add Button
Creates a new XML index.Delete Button
Deletes XML index selected in the Selected XML Index list. If you delete the primary XML index, you will be notified that this will delete all secondary ones as well, and you can either continue or cancel the action.General Category
Expanded, it shows the property fields for Columns, Is Primary, and Type.Columns
Shows that this index is sorted in ascending order.Is Primary
Indicates whether this is the primary index. The first XML index created on the column will be the basis of the others.Primary reference name
Shows the name of the primary index if this is a secondary index. Only available if this is a secondary index.Secondary type
Shows the type of secondary index. Only available if this is a secondary index.Type
Shows that this is an XML index.Identity Category
Expanded, it shows the Name and Description property fields.Name
Shows the name of the XML index. When a new one is created, it is given a default name based on the table in the active window in Table Designer. You can change the name at any time.Description
Provides a place to describe the index. To write a more detailed description, click Description and then click the ellipsis (…) that appears to the right of the property field. This provides a larger area in which to write text.Table Designer Category
Expanded, shows information about the properties of this XML index.Fill Specification
Expanded, shows information for Fill Factor and Pad Index.Fill Factor
Specifies what percentage of the index page the system can fill. Once a page is full the system must split the pages if new data is added, impairing performance.A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table.
A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.
Pad Index
Indicates whether pages in this index are provided the same percentage of empty space (padding) specified in Fill Factor when they grow.Is Disabled
Indicates whether this index is disabled. Disabled indexes do not support searches, nor do they get updated when new items are added to the table. You can improve performance for bulk inserts and updates by disabling an index.Page Locks Allowed
Indicates whether page-level locking is allowed on this index. Allowing or disallowing page-level locking affects database performance.Re-compute Statistics
Indicates whether the underlying database engine computes new statistics when the index is created. Re-computing statistics slows the building of indexes but will very likely improve query performance.Row Locks Allowed
Indicates whether row-level locking is allowed on this index. Allowing or disallowing row-level locking affects database performance.