CREATE INDEX

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

Syntax
      -- Relational Index 
      CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index
         ON object (column [ASC | DESC] [,...n ] ) 
            [INCLUDE (column [ ,...n] ) ]
               [WITH (option [ ,...n] ) ]
                  [ON { partition_scheme ( column ) 
                      | filegroup 
                      | default 
                      }
                  ]
      [;]

     -- XML Index 
     CREATE [ PRIMARY ] XML INDEX index
        ON object ( xml_column )
           [USING XML INDEX xml_index 
              [FOR { VALUE | PATH | PROPERTY } ] ]
                 [WITH ( option [ ,...n ] ) ]
      [;]

Object:
     database.[schema].table_or_view
     schema.table_or_view

Options:
   PAD_INDEX  = {ON | OFF}
   FILLFACTOR = fillfactor
   SORT_IN_TEMPDB = {ON | OFF}
   IGNORE_DUP_KEY = {ON | OFF}  **
   STATISTICS_NORECOMPUTE = {ON | OFF}
   DROP_EXISTING = {ON | OFF}
   ONLINE = {ON | OFF}  **
   ALLOW_ROW_LOCKS = {ON | OFF}
   ALLOW_PAGE_LOCKS = {ON | OFF}
   MAXDOP = max_degree_of_parallelism

     ** not supported for XML indexes

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.

Previous versions of SQL Server use a different CREATE INDEX syntax - this is supported for backward compatibility only in SQL 2005.

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 UNIQUE INDEX MyIndex01 ON MySchema.MyTable(MyColumn);
CREATE UNIQUE CLUSTERED INDEX MyIndex02 ON MyTable(MyColumn);

"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
sys.indexes
sys.index_columns
sys.xml_indexes
EVENTDATA
Equivalent Oracle commands: CREATE INDEX