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
A clustered index actually stores the data records in physical order. Because a table can only be physically sorted in one order, a table can only define one clustered index. Having a clustered index also means that each time a record is inserted or deleted from a table, the table or at least parts of the table must be rewritten to maintain the physical order. Even an update to the key field of a clustered index causes the table to delete the 'old' record and then insert the 'new' record. Obviously maintaining the physical order of the records in a table can be quite expensive, especially if there are frequent updates to the table. However, if the table is used mostly for retrieval and reporting, the advantages of a clustered index come to the forefront.

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
A nonclustered index creates a separate 'internal' table that stores only the selected key values of the table in order. Each 'record' in this index contains the key value from one record in the table along with a pointer to either the data record itself or to a value in the clustered index. Because a nonclustered index does not require a physical sorting of the actual data, we can have as many nonclustered indexes associated with a single table as possible. The maximum limit is 249. For each insert, delete, and update performed, SQL updates the table.

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
An index in which no two index values are the same is called a unique index. Sql Server checks for duplicate values when a unique index is created and each time data is subsequently added. If duplicate values exist, then an error message is generated. Unique indexes cannot be created on columns that already contain duplicate values.

Fillfactor
Fillfactor allows us to reserve free space on each page of the index. Fillfactor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. If we do not use the fillfactor option and an index page fills up, SQL Server must take time to move half the index page to a new page to make room for new rows (Page Split). This operation is quite expensive. For update-intensive tables, a properly chosen fillfactor value yields better update performance than an improper fillfactor value. If no value is specified the default value for the fillfactor is 0.

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.

Language(s): MS SQL Server