Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Index Properties (General Page)

Use this page to view or modify index properties for the selected table or view.

For general information about indexes and how they work, see Creating Indexes (Database Engine).

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

Select the type of index from the list: Clustered, Nonclustered, Primary XML, or Spatial.

Note   Only one clustered index is allowed for each table. If a clustered index already exists, an error message is displayed and you are asked if the existing clustered index should be dropped and a new clustered index created.

The following type conversions are permitted:

  • Clustered to clustered.

  • Nonclustered to nonclustered.

  • Nonclustered to clustered.

The following conversions are not allowed:

  • Clustered to nonclustered.

  • Non-XML index to XML index, and vice versa.

  • Non-spatial index to spatial index, and vice versa.

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 Index with Included Columns.

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.

NoteNote

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

Data Type

Displays the data type information.

NoteNote

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. For more information about column order in an index, see General Index Design Guidelines.

Move Down

Moves the selected column down in the index key grid.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.