Microsoft® SQL Server™ 2000 Unleashed, Second Edition

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

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 ...