SALES: 1-800-867-1380

CREATE INDEX (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see CREATE INDEX (Transact-SQL).

Creates a relational index on a specified table or view on a specified table. An index can be created before there is data in the table. Relational indexes can be created on tables or views in another database by specifying a qualified database name.

Because Microsoft Microsoft Azure SQL Database does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

Syntax Conventions (Azure SQL Database)


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 ] ) ]


[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=column_name IN (constant ,…)

<comparison> ::=column_name <comparison_op> constant<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }




This syntax diagram demonstrates the supported arguments and options in Microsoft Azure SQL Database.

Microsoft Azure SQL Database does not support the following arguments and options with the CREATE INDEX statement:

  • ON Keyword for

    • partition_schema_name

    • filegroup name

    • default

  • FILESTREAM_ON

  • relational_index_option:

    • PAD_INDEX

    • FILLFACTOR

    • SORT_IN_TEMPDB

    • ALLOW_ROW_LOCKS

    • ALLOW_PAGE_LOCKS

    • MAXDOP

    • DATA_COMPRESSION

  • Backward-compatible relational index

noteNote
Microsoft Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

Microsoft Azure SQL Database does not support four-part names.

For more information about the arguments and the CREATE INDEX statement, see CREATE INDEX (Transact-SQL) in SQL Server Books Online.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft