Creates an index on a specified table. An index can be created before there is data in the table..

CREATE [UNIQUE] [NONCLUSTERED] INDEX index_name ON table_name (column_name [ASC|DESC][,…n])
WITH (STATISTICS_NORECOMPUTE = { ON | OFF })]

Arguments

Term

Definition

UNIQUE

Creates a unique index on a table. A unique index is one in which no two rows are permitted to have the same index value.

SQL Server Compact 3.5 checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. Duplicates must be eliminated before a unique index can be created on the columns. If duplicate key values exist, the CREATE INDEX statement is canceled and an error is returned. A unique index can be created only on columns that are defined as NOT NULL.

When a unique index exists, UPDATE or INSERT statements that would generate duplicate key values are rolled back, and SQL Server Compact 3.5 returns an error. This is true even if the UPDATE or INSERT statement changes many rows, but causes only one duplicate.

NONCLUSTERED

Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order. This is the only supported index type.  (The default is NONCLUSTERED)

index_name

Specifies the name of the index. Index names must be unique within a table, but do not have to be unique within a database.

table_name

Specifies the name of the table on which to create the index.

This table contains the column or columns to be indexed.

column name

The column or columns to which the index applies. Specify two or more column names to create a composite index on the combined values in the specified columns. List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table.

Columns consisting of the ntext or image data types cannot be specified as columns for an index.

ASC | DESC ]

Determines the ascending (ASC) or descending (DSC) sort direction for the particular index column. The default is ASC.

n

A placeholder indicating that multiple columns can be specified for any particular index. The maximum number of columns that can participate in an index is 16.

STATISTICS_NORECOMPUTE

Specifies whether distribution statistics are recomputed. The default is OFF.

  • ON

    Out-of-date statistics are not automatically recomputed.

  • OFF

    Automatic statistics updating are enabled

To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.

Example

The following example creates a unique index on the MyCustomers table.

CREATE TABLE MyCustomers (CustID int, CompanyName nvarchar(50));
CREATE UNIQUE INDEX idxCustId ON MyCustomers (CustId);

Concepts

Query Performance Tuning (SQL Server Compact)

Database Objects