Microsoft® SQL Server® 2008 Internals

Index Creation Options

In terms of creating indexes, the CREATE INDEX command is relatively straightforward:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
   ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]

The required parts of an index are the index name, the key definition, and the table on which this index is defined. An index can have non-key columns included in the leaf level of the index, using INCLUDE. An index can be defined over the entire rowset of the table—which is the default—or, new in SQL Server 2008, can be limited to only the rows as defined by a filter, using WHERE <filter_predicate>. We discuss both of these as we analyze the physical structures ...