Online Documentation for SQL Administrator for SQL Server

Properties


Use the Properties tab of the Index Editor to create/edit an index on a specified table and specify index properties.

 

Name

Enter a name for the new index, or modify the name of the index being edited.

 

Index Editor - Properties

 

 

Table

In this field the Table name of the index being edited is displayed.

 

!CheckBox Enabled

Enables/disables the index immediately after it is created.

 

!CheckBox Clustered

Use this option to create an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table is allowed one clustered index at a time.

 

Index type

 

!RadioButton Primary Key

This selection indicates that the primary key index is created.

 

!RadioButton Unique key

This selection indicates that the unique key index is created (no two rows are permitted to have the same index value). A clustered index on a view must be unique.

 

!RadioButton Unique index

This selection indicates that the unique index is created (ensures that the index key contains no duplicate values and therefore each row in the table or view is in some way unique). Both clustered and nonclustered indexes can be unique.

 

!RadioButton XML index

This selection indicates that the XML index on the specified XML column is created.

 

!RadioButton Spatial index

This selection indicates that the spatial index is created. These indexes are used by spatial databases (databases which store information related to objects in space).

 

!RadioButton Columnstore index

This selection indicates that the columnstore index is created. These indices group and store data for each column and then join all the columns to complete the whole index.

 

!RadioButton Index

This selection specifies a regular, non-unique index.

 

Index creation options

 

Max degree of parallelism

This option allows you to limit the number of processors used in a parallel plan execution. Set a value to restrict the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload. If 0 or !CheckBox Default is specified, the actual number of processors is used.

 

!CheckBox Sort in tempdb

This option specifies whether temporary sort results should be stored in tempdb.

 

!CheckBox Keep table online

This option specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation.

 

Options

 

!CheckBox Do not recompute statistics

This option specifies that out-of-date index statistics are not automatically recomputed.

 

!CheckBox Pad index

This option determines whether the percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

 

!CheckBox Ignore duplicate keys

This option is available if the Unique option is checked. This option specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index.

 

In cases when access patterns are well understood and consistent, limiting the locking levels available for an index can be beneficial. Disallowing a locking level can affect the concurrency for a table or index.

 

!CheckBox Allow row locks

This option specifies whether row locks are allowed when accessing the index.

 

!CheckBox Allow page locks

This option specifies whether page locks are allowed when accessing the index.

 

Hash index options (for memory-optimized tables only)

 

!CheckBox Hash index

Checking this option indicates that a HASH index is created. A hash index consists of a collection of buckets organized in an array.

 

Number of buckets

Define the number of buckets that should be created in the hash index.

 

Fields for index

To include field(s) in the index, you need to move it from the Available list to the Selected list. Use the MoveAllToAvailable MoveToAvailable MoveToSelected MoveAllToSelected buttons or drag-and-drop operations to move the fields from one list to another.

 

To change the sorting order for a field, select the field in the Selected list and change the Order value (ascending/descending).

Set the !CheckBox Included Only flag to specify that the non-key column is added to the leaf level of the nonclustered index.