| 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 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. 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 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: CREATE UNIQUE CLUSTERED INDEX emp_tel_idx ON employee(phone) WITH PAD_INDEX, FILLFACTOR = 50, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE ON index_fg1  | 