Creating an Index

Syntax:: CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
    INDEX index_name ON table ( column1, ... )
[ WITH
       [ PAD_INDEX ]
       [ [ , ] FILLFACTOR = fillfactor1 ]
       [ [ , ] IGNORE_DUP_KEY ]
       [ [ , ] DROP_EXISTING ]
       [ [ , ] STATISTICS_NORECOMPUTE ]
]
[ ON filegroup1 ]
UNIQUE: Indicates that a unique index is to be created.
CLUSTERED: Indicates that the index created is a clustered index.
NONCLUSTERED: Indicates that the index created is a nonclustered index.
index_name: Is the name of the index.
table: The name of the table on which the index is to be created.
column1, ...: The column or columns to which the index is to be applied.
PAD_INDEX: Specifies the space to be left open on each page (node) in the intermediate levels of the index. (This is useful only when FILLFACTOR is specified).
FILLFACTOR = fillfactor1: Specifies the fillfactor for the index as fillfactor1.
IGNORE_DUP_KEY: Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index.
DROP_EXISTING: Specifies that the named, preexisting clustered or nonclustered index should be dropped and the specified index rebuilt.
STATISTICS_NORECOMPUTE: Specifies that out-of-date index statistics are not automatically recomputed.
ON filegroup1: Creates the specified index on the given filegroup1.

An INDEX is created using the CREATE INDEX command.

Indexes may be:

Clustered Index

Clustered Indexes are suitable for:

Columns that contain a limited number of distinct values, such as a state column that contains only 50 distinct state codes.
Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
Columns that are accessed sequentially.
Queries that return large result sets.
Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns.
OLTP-type applications where very fast single row lookup is required, typically by means of the primary key.

Clustered indexes are not suitable for:

Columns that undergo frequent changes.
Covered queries. The more columns within the search key, the greater the chance for the data in the indexed column to change, resulting in additional I/O.

Nonclustered Index

Nonclustered Indexes are suitable for:

Columns that contain a high number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns).
Queries that do not return large result sets.
Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches.
Decision Support System applications for which joins and grouping are frequently required.
Covered queries.

Note:

If CLUSTERED is not specified, a nonclustered index is created.
Index names must be unique within a table but need not be unique within a database.
Index names must follow the rules of identifiers.
Only the owner of the table can create indexes on the same table.

Unique Index

Fillfactor

Examples

Code:


				CREATE CLUSTERED INDEX Index_Name_Clstd ON Students(Name);

Output:


				Index (Id = 2) is being rebuilt.

Explanation:

In the above example, we have built a clustered index, Index_Name_Clstd, on the 'Name' column of the 'Students' table.

Language(s): MS SQL Server

Code:


				CREATE NONCLUSTERED INDEX Index_Name_NonClstd ON Students(Name);

Output:


				The command(s) completed successfully.

Explanation:

In the above example, we have built a nonclustered index, Index_Name_NonClstd, on the 'Name' column of the 'Students' table.

Language(s): MS SQL Server

Code:


				CREATE UNIQUE INDEX Index_Name_Unique ON Students (Name);

Output:


				The command(s) completed successfully.

Explanation:

The above example creates a unique index, Index_Name_Unique, on the 'Name' column of the 'Students' table.

Language(s): MS SQL Server

Code:


				CREATE CLUSTERED 

				INDEX Index_Name_Clstd_FF10 ON Students (Name) 

				WITH FILLFACTOR=10;

Output:


				Index (Id = 2) is being rebuilt.

				Index (Id = 3) is being rebuilt.

				Index (Id = 4) is being rebuilt.

				Index (Id = 2) is being rebuilt.

				Index (Id = 3) is being rebuilt.

				Index (Id = 4) is being rebuilt.

Explanation:

In the above example, we have built a new clustered index on the 'Name' field of the 'Students' table with a fillfactor of 10.

Note: Drop any of the existing CLUSTERED INDEX using the DROP INDEX command.