CREATE INDEX (TSQL Statement)

creates a new index on the specified table.

Syntax

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <table_qname> ( column [ ASC ] [ ,...n ] )
    [ ON <filegroup> ]

<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name

Arguments

UNIQUE

a UNIQUE constraint is created.

CLUSTERED | NONCLUSTERED

specifies whether the index is clustered or nonclustered.

if NONCLUSTERED, the new index is added to the table.
- The index file is created and filled with index entries, which can take a long time if the table is large.
if CLUSTERED, the internal structures of the table and all existing indexes are completely modified.
- The table is dropped and a new table is created.
- This operation is only allowed on an empty table for which no clustered index has been specified at creation time.
if not specified, the default is NONCLUSTERED.

index_name

is the name of the new index or UNIQUE constraint.

<table_qname>

is the qualified or unqualified name of the table on which the index will be created.

column [ ASC ] [ ,…n ]

list of columns (also called the index key) of the index.

the optional ASC specifier means ascending sort order and is the default. DESC is not implemented.

ON <file_group>

this clause often exists in scripts generated by MS SQL Server, but it is just ignored by RSQL.

Remarks

Only sa or dbo is allowed to execute this statement.

When UNIQUE is specified, a UNIQUE constraint will be created.

If a CLUSTERED index is created:

the native key of the table must be changed, as well as the structure of all indexes.
This is a big change in the table and index layouts. So, the table is dropped and a new table is created.
This operation is only allowed on an empty table for which no clustered index has been specified at creation time.

Examples

CREATE TABLE clients(id INT NOT NULL IDENTITY(100, 1) PRIMARY KEY, name VARCHAR(20));

CREATE INDEX idx_name ON clients (name);

Guide