Index Properties F1 Help

Use the General page to view or modify index properties for the selected table or view. The options for each page may change based on the type of index selected.

Table name

Displays the name of the table or view that the index was created on. This field is read-only. To select a different table, close the Index Properties page, select the correct table, and then open the Index Properties page again.

Spatial indexes cannot be specified on indexed views. Spatial indexes can be defined only for a table that has a primary key. The maximum number of primary key columns on the table is 15. The combined per-row size of the primary-key columns is limited to a maximum of 895 bytes.

Index name

Displays the name of the index. This field is read-only for an existing index. When creating a new index, type the name of the index.

Index type

Indicates the type of index. For new indexes, indicates the type of index selected when opening the dialog box. Indexes can be: Clustered, Nonclustered, Primary XML, Secondary XML,, Spatial, or Nonclustered Columnstore.

Note   Only one clustered index is allowed for each table. Only one xVelocity memory optimized columnstore index is allowed for each table.

Unique

Selecting this check box makes the index unique. No two rows are permitted to have the same index value. By default, this check box is cleared. When modifying an existing index, index creation will fail if two rows have the same value. For columns where NULL is permitted, a unique index permits one NULL value.

If you select Spatial in the Index type field, the Unique check box is dimmed.

Index key columns

Add the desired columns to the Index key columns grid. When more than one column is added, the columns must be listed in the order desired. The column order in an index can have a great impact on the index performance.

No more than 16 columns can participate in a single composite index. For greater than 16 columns, see included columns at the end of this topic.

A spatial index can be defined only on a single column that contains a spatial data type (a spatial column).

Name

Displays the name of the column that participates in the index key.

Sort Order

Specifies the sort direction of the selected index column, either Ascending or Descending.

Note
If the index type is Primary XML or Spatial, this column does not appear in the table.

Data Type

Displays the data type information.

Note
If the table column is a computed column, Data type displays "computed column."

Size

Displays the maximum number of bytes required to store the column data type. Displays zero (0) for a spatial or XML column.

Identity

Displays whether the column participating in the index key is an identity column.

Allow NULLs

Displays whether the column participating in the index key allows NULL values to be stored in the table or view column.

Add

Adds a column to the index key. Select table columns from the Select Columns from <table name> dialog box that appears when you click Add. For a spatial index, after you select one column, this button is dimmed.

Remove

Removes the selected column from participation in the index key.

Move Up

Moves the selected column up in the index key grid.

Move Down

Moves the selected column down in the index key grid.

Columnstore columns

Click Add to select columns for the columnstore index. For limitations on a columnstore index, see CREATE COLUMNSTORE INDEX (Transact-SQL).

Included columns

Include nonkey columns in the nonclustered index. This option allows you to bypass the current index limits on the total size of an index key and the maximum number of columns participating in an index key by adding columns as nonkey columns in the leaf level of the nonclustered index. For more information, see Create Indexes with Included Columns

[Top]

Use this page to add columns to the Index Properties General page when creating or modifying an index.

Check box

Select to add columns.

Name

Name of the column.

Data Type

The data type of the column.

Bytes

The size of the column in bytes.

Identity

Displays Yes for identity columns, and No when the column is not an identity column.

Allow Nulls

Displays Yes when the table definition allows null values for the column. Displays No when the table definition does not allow nulls for the column.

[Top]

Use this page to view or modify filegroup or partition scheme properties for the selected index. Only shows options related to the type of index.

Filegroup

Stores the index in the specified filegroup. The list only displays standard (row) filegroups. The default list selection is the PRIMARY filegroup of the database. For more information, see Database Files and Filegroups.

Filestream filegroup

Specifies the filegroup for FILESTREAM data. This list displays only FILESTREAM filegroups. The default list selection is the PRIMARY FILESTREAM filegroup. For more information, see FILESTREAM (SQL Server).

Partition scheme

Stores the index in a partition scheme. Clicking Partition Scheme enables the grid below. The default list selection is the partition scheme that is used for storing the table data. When you select a different partition scheme in the list, the information in the grid is updated. For more information, see Partitioned Tables and Indexes.

The partition scheme option is unavailable if there are no partition schemes in the database.

Filestream partition scheme

Specifies the partition scheme for FILESTREAM data. The partition scheme must be symmetric with the scheme that is specified in the Partition scheme option.

If the table is not partitioned, the field is blank.

Partition Scheme Parameter

Displays the name of the column that participates in the partition scheme.

Table Column

Select the table or view to map to the partition scheme.

Column Data Type

Displays data type information about the column.

Note
If the table column is a computed column, Column Data Type displays "computed column."

Allow online processing of DML statements while moving the index

Allows users to access the underlying table or clustered index data and any associated nonclustered indexes during the index operation. For more information, see Perform Index Operations Online.

Note
This option is not available for XML indexes, or if the index is a disabled clustered index.

Set maximum degree of parallelism

Limits the number of processors to use during parallel plan execution. The default value, 0, uses the actual number of available CPUs. Setting the value to 1 suppresses parallel plan generation; setting the value to a number greater than 1 restricts the maximum number of processors used by a single query execution. This option only becomes available if the dialog box is in the Rebuild or Recreate state. For more information, see Set the Max Degree of Parallelism Option for Optimal Performance.

Note
If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.

[Top]

Use the Spatial page to view or specify the values of the spatial properties. For more information, see Spatial Data (SQL Server).

Bounding Box

General

Grids

Use this page to enter the filter predicate for a filtered index. For more information, see Create Filtered Indexes.

Filter Expression

Defines which data rows to include in the filtered index. For example, StartDate > '20000101' AND EndDate IS NOT NULL'.

[Top]

Reference

Concepts