Professional SQL Server™ 2005 Programming

8.3. Creating, Altering, and Dropping Indexes

These work much as they do on other objects such as tables. Take a look at each, starting with the CREATE.

Indexes can be created in two ways:

• Through an explicit CREATE INDEX command

• As an implied object when a constraint is created

Each of these has its own quirks about what it can and can't do, so take a look at each of them individually.

8.3.1. The CREATE INDEX Statement

The CREATE INDEX statement does exactly what it sounds like — it creates an index on the specified table or view based on the stated columns.

The syntax to create an index is somewhat drawn out, and introduces several items that I haven't really talked about up to this point:

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n])
INCLUDE (<column name> [, ...n])
[WITH
[PAD _ INDEX = { ON | OFF }]

[[,] FILLFACTOR = <fillfactor>]
[[,] IGNORE _ DUP _ KEY  = { ON | OFF }]
[[,] DROP _ EXISTING = { ON | OFF }]
[[,] STATISTICS _ NORECOMPUTE = { ON | OFF }]
[[,] SORT _ IN _ TEMPDB = { ON | OFF }]
[[,] ONLINE = { ON | OFF }
[[,] ALLOW_ROW_LOCKS = { ON | OFF }
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }
[[,] MAXDOP = <maximum degree of parallelism>
]
[ON {<filegroup> | <partition scheme name> | DEFAULT }]

There is legacy syntax available for many of these options, and so you may see that syntax put into use to support prior versions of SQL Server. That syntax is, however, considered deprecated and will be removed at ...