CREATE INDEX (SQL Server Compact Edition)
New: 14 April 2006
Creates an index on a specified table. An index can be created before there is data in the table..
Syntax
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 2005 Compact Edition (SQL Server Compact Edition) 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 Edition 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. Note 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.
To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause. Important 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)
See Also
Other Resources
Query Performance Tuning (SQL Server Compact Edition)
Database Objects