Microsoft SQL Server 2012 Internals

Index creation options

For creating B-tree 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, they can be limited to only the rows as defined by a filter, using WHERE <filter_predicate>. (SQL Server 2008 introduced the ability to filter indexes.) Both of these are discussed ...