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.
Indexes are created using the CREATE INDEX command. Listing 13.1 shows the complete CREATE INDEX syntax.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH < index_option > [ ,...n] ] [ ON filegroup ] < index_option > :: = { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB }
Table 13.1 lists the CREATE INDEX arguments.
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. |
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:
CREATE UNIQUE CLUSTERED INDEX emp_tel_idx ON employee(phone) WITH PAD_INDEX, FILLFACTOR = 50, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE ON index_fg1