Creating Indexes Using T-SQL

This section examines the syntax used to create indexes using T-SQL. It is important to be familiar with this syntax as indexes are one of the more volatile database objects, often being dropped and re-created for performance reasons. To accommodate re-creation of indexes, it is common practice to script these actions using T-SQL.

The Transact-SQL CREATE INDEX Syntax

Indexes are created using the CREATE INDEX command. Listing 13.1 shows the complete CREATE INDEX syntax.

Listing 13.1 The CREATE INDEX Syntax
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > :: =
    { PAD_INDEX |
        FILLFACTOR = fillfactor |
        IGNORE_DUP_KEY |

Table 13.1 lists the CREATE INDEX arguments.

Table 13.1. Arguments for CREATE INDEX
Argument Explanation
UNIQUE Specifies that no duplicate rows be allowed. If duplicate rows exist in the data, the index creation fails.
CLUSTERED | NON-CLUSTERED Defines the index as clustered or nonclustered. Non-clustered is the default. Only one clustered index is allowed.
index_name Specifies the name of the index to be created.
table | view Specifies the name of the table or view on which the index is to be based.
column Specifies the column or columns that are to be indexed.
ASC | DESC Specifies whether the index should be sorted in ascending or descending order. ASC is the default.
ON Filegroup Determines on which filegroup the index should be stored.
PAD_INDEX Specifies that a percentage of space should be left free on the non-leaf levels of the index. The percentage is determined by FILLFACTOR.
FILLFACTOR = fillfactor Specifies what percentage to fill the leaf pages of the index on index creation. If inserts and updates are expected on the indexed columns, specifying a FILLFACTOR of less than 100 can improve performance by avoiding page splits. Valid values are 1?100; the default of 0 indicates a 100% fill.
IGNORE_DUP_KEY Used in conjunction with UNIQUE. If specified, and an attempt is made to insert a duplicate key in a UNIQUE index, the duplicate key is rejected, but the statement continues. If not specified, the entire statement is rolled back when a duplicate key is encountered.
DROP_EXISTING Used to re-create existing indexes. A performance gain can be realized when a clustered index is rebuilt, as the non-clustered indexes are not rebuilt unless the clustered keys change.
STATISTICS_NO_RECOMPUTE Specifies that index statistics will not be automatically updated.
SORT_IN_TEMPDB Stores the intermediate sort results used to create the index in tempdb. This increases disk space usage, but can improve index creation performance if tempdb is on a separate disk set than the user database.

Examples of Using Transact-SQL to Create Indexes

When creating an index in the current database, on the default filegroup, the minimum required syntax would be as follows:

CREATE INDEX emp_tel_idx ON employee (phone) 

This creates a nonclustered index named emp_tel_idx on the phone column of the employee table.

Often, for performance reasons, it is best to separate the index from the table data. To do this, you specify a filegroup on which to create the index:

CREATE INDEX emp_tel_idx ON employee (phone) 
ON index_fg1

A more complete script to create a unique clustered index that specifies several optional arguments is illustrated by the following example:

ON employee(phone)
ON index_fg1

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features