CREATE INDEX

Create an index on a table or view. Also XML indexes.

Syntax
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON object ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE filter_predicate ]
    [ WITH ( relational_index_option [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

object ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

relational_index_option ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( low_priority_lock_wait ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = time [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { partition_number_expression | range }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { partition_number_expression | range }
     [ , ...n ] ) ]
}







filter_predicate ::=
    conjunct [ AND ] [ ...n ]

conjunct ::=
    disjunct | comparison

disjunct ::=
        column_name IN (constant ,...n)

comparison ::=
        column_name comparison_op constant

comparison_op ::=
    { IS | IS NOT | = | < > | != |  > |  >= | ! > | < | <= | !< }

low_priority_lock_wait::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = time [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

range ::=
partition_number_expression TO partition_number_expression

Key:
   ASC/DESC    The sort direction for the index column. 
   INCLUDE...  Nonkey columns to add to a nonclustered index
   partition_scheme  The filegroup partition scheme for a partitioned index
   filegroup    Create the index on a specific filegroup.
   xml_column   The xml column on which the index is based
   PAD_INDEX    Pad the index by fillfactor amount 
   fillfactor   Percentage of each index page to fill during index creation/rebuild.
                1-100, default=0

In a default (nonclustered) index, the physical order of the data is independent of the index order.

Indexes are not utilised when there is no WHERE clause or the search is for a NULL or NOT NULL value.

Previous versions of SQL Server may use a different CREATE INDEX syntax.

To create an index based on a view, the view must be defined with SCHEMABINDING.
A unique clustered index must be created on a view before any nonclustered index is created.

Examples

Create a nonclustered index:

CREATE INDEX indexState ON Schema64.T_DEMO_ADDRESSES (DA_State);

Create a clustered index on a table and use a 3-part name for the table:

CREATE CLUSTERED INDEX indexState ON database1.Schema64.T_DEMO_ADDRESSES (DA_State);

Create a nonclustered index with a unique constraint and specify the sort order:

CREATE UNIQUE INDEX index1 ON Schema64.T_DEMO_ADDRESSES (DA_State DESC, DA_City ASC, DA_Zip DESC);

“Anything you build on a large scale or with intense passion invites chaos” ~ Francis Ford Coppola

Related commands

ALTER INDEX
CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME
CREATE STATISTICS
Data Types
DBCC SHOW_STATISTICS
DROP INDEX
CREATE INDEX at Microsoft.com
sys.indexes
sys.index_columns
sys.xml_indexes
EVENTDATA