Creates an index on a given table.
CREATE [ UNIQUE ] INDEX index_name ON table ( column [ ASC | DESC ] [ ,...n ] )
- Creates a unique index (one in which no two rows are permitted to have the same index value) on a table.
Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) 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 column(s). 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 CE returns an error. This is true even if the UPDATE or INSERT statement changes many rows but causes only one duplicate.
- Is the name of the index. Index names must be unique within a table but do not need to be unique within a database. Index names must follow the rules of identifiers.
- Is the table that contains the column or columns to be indexed.
- Is 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.
Important All columns in a UNIQUE INDEX must have NOT NULL specified.
- [ASC | DESC]
- Determines the sort direction for the particular index column: ASC, the default, is ascending; and DESC is descending.
- Is 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 10.
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)