MS SQL Basics Tutorial: Creating indexes

8.2.1 CREATE index with the CREATE INDEX command

Create index either creates a clustered index that alters the physical order of the table or creates a nonclustered index that improves query performance. The syntax is as follows:

CREATE [UNIQUE] [CLUSTERED | Nonclustered]

INDEX index_name on {table | view} column [ASC | DESC] [,... N])

[With

[Pad_index]

[[,] FILLFACTOR = FILLFACTOR]

[[,] ignore_dup_key]

[[,] drop_existing]

[[,] statistics_norecompute]

[[,] sort_in_tempdb]

]

[ON filegroup]

The parameters are described as follows:

UNIQUE

Creates a unique index, where the key value of the index is not duplicated. You cannot build a unique index when a column contains duplicate values. To use this option, you should determine that the indexes contain columns that do not allow null values, or you will often make errors when you use them. CLUSTERED

Indicates that the index created is a clustered index. If this option is default, the index created is not a clustered index. Nonclustered

Indicates that the index created is not a clustered index. The data page contains pointers to the actual table data pages in the database. Index_name

Specifies the name of the index that is created. Index names should be unique in a table, but can be duplicated in the same database or in different databases. Table

Specifies the name of the table where the index is created. You should also indicate the database name and owner name, if necessary. View

Specifies the name of the view in which the index is created. The view must be defined using the SCHEMABINDING option, and its specific information is shown in the "View Creation" section. ASC | DESC

Specifies how a particular indexed column is sorted. The default value is ascending (ASC). Column

Specifies the column to be indexed. If you use two or more columns to make up an index, it is called a composite index. You can specify up to 16 columns in an index, but the data type of the column cannot be longer than 900 bytes. Pad_index