Online Documentation for SQL Manager for SQL server

Creating/editing index


Use the Index tab of Index Editor to create/edit an index on a specified table or view, or an XML 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.

 

Table or view

The drop-down list of tables and views allows you to select the table or view to be indexed.

 

Fill factor

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

 

Index Editor - Editing index definition

 

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

 

CPUs for indexing

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 is specified, the actual number of processors is used.

 

CheckBox Default

If this option is checked, the default setting for the number of processors is applied.

 

CheckBox Online operation

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

 

CheckBox Sort in tempdb

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

 

XML Index options

Select the XML index type:

RadioButton Value index

This selection indicates creation of a secondary XML index on columns where key columns are (node value and path) of the primary XML index. If your workload involves querying for values from XML instances without knowing the element or attribute names that contain the values, VALUE index may be useful.

RadioButton Path index

This selection indicates creation of a secondary XML index on columns built on path values and node values in the primary XML index. If your queries generally specify path expressions on xml type columns, a PATH secondary index may be able to speed up the search.

RadioButton Property index

This selection indicates creation of a secondary XML index on columns (PK, path and node value) of the primary XML index where PK is the primary key of the base table. Queries that retrieve one or more values from individual XML instances may benefit from a PROPERTY index.

 

 

Index options

 

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 or view is allowed one clustered index at a time.

 

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.

 

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 Do not recompute statistics

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

 

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.

 

CheckBox Hash index (for memory-optimized tables)

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 hash buckets (for memory-optimized tables)

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

 

Index filter

Use this field to set the filter for the data from the column(s) included in this index. Available for SQL Server 2008 and higher.

 

Columns for index

To include column(s) in the index, you need to move it from the Available list to the Selected list. Use the Items - Move to Selected Items - Move all to Selected Items - Move to Available Items - Move all to Available buttons or drag-and-drop operations to move the columns from one list to another.

 

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

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