Microsoft Docs

CREATE INDEX (SQL)

Creates a unique or non-unique index on a table or view.

Syntax

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON  ( {  [ ASC | DESC ] [ INCLUDE (  [ ,...n ] ) ] } [ ,...n ] )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]
    [ FILESTREAM_ON  ]

-- For Heaps or Non-clustered indexes on Clustered indexes
CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name
    ON  ( {  [ ASC | DESC ] [ INCLUDE ( column_name [ ,...n ] ) ] } [ ,...n ] )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]
    [ FILESTREAM_ON  ]

-- For Full-Text Indexes
CREATE FULLTEXT INDEX ON 
    ( { 
        [ LANGUAGE  ]
        [  ]
        [  ]
        [ BREAK  ]
      } [ ,...n ]
    )
    KEY INDEX index_name
    [ WITH CHANGE_TRACKING { AUTO | MANUAL | OFF } ]
    [ ON {  | FILEGROUP_NAME() } ]

-- For XML Indexes
CREATE PRIMARY XML INDEX index_name
    ON  ( xml___value_data )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]

CREATE XML INDEX index_name
    ON  ( xml_value_data )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]
    [ CONTENT | PATH ]

-- For Spatial Indexes
CREATE SPATIAL INDEX index_name
    ON  ( {   } [ ,...n ] )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]

-- For Columnstore Indexes
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON 
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]

CREATE NONCLUSTERED COLUMNSTORE INDEX index_name
    ON  ( column_name [ ,...n ] )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]

-- For Computed Column Indexes
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON  ( computed_column_expression [ ASC | DESC ] [ INCLUDE ( column_name [ ,...n ] ) ] )
    [ WITH (  [ ,...n ] ) ]
    [ ON  ]


            
Description

            
The CREATE INDEX statement is used to create an index on one or more columns of a table or view. Indexes are crucial for improving the performance of data retrieval operations by providing a quick lookup mechanism. When you query data, the database can use an index to find the relevant rows much faster than scanning the entire table.


            
Types of Indexes

            
    
                
  • Unique Index: Ensures that all values in the indexed column(s) are unique.
    • 
                
  • Non-Unique Index: Allows duplicate values in the indexed column(s).
    • 
                
  • Clustered Index: Determines the physical order of data rows in a table. A table can have only one clustered index. It's often created on the primary key.
    • 
                
  • Non-Clustered Index: Does not affect the physical order of data rows. It's a separate structure that contains pointers to the data rows.
    • 
                
  • Full-Text Index: Used for efficient searching of character-based data using natural language queries.
    • 
                
  • XML Index: Optimizes queries against XML data stored in XML columns.
    • 
                
  • Spatial Index: Used for indexing spatial data types, enabling efficient queries on geometric shapes and geographical locations.
    • 
                
  • Columnstore Index: Designed for data warehousing and analytical workloads, it stores data column by column, providing high compression and query performance.
    • 
            


            
Key Concepts

            
    
                
  • index_name: The name for the new index.
    • 
                
  • object: The table or view on which to create the index.
    • 
                
  • index_column: The column(s) to include in the index.
    • 
                
  • ASC/DESC: Specifies ascending or descending order for the indexed column.
    • 
                
  • INCLUDE: Adds non-key columns to the leaf level of a non-clustered index, which can improve query performance by covering queries without requiring a bookmark lookup.
    • 
                
  • WITH ( index_option ): Specifies various index options such as fill factor, pad index, and online operations.
    • 
                
  • ON : Specifies the filegroup where the index will be stored.
    • 
            


            

                
Example 1: Creating a Non-Unique Non-Clustered Index

                
This example creates a non-clustered index named IX_Customers_City on the City column of the Customers table.

                
CREATE NONCLUSTERED INDEX IX_Customers_City
ON Customers (City);

            


            

                
Example 2: Creating a Unique Clustered Index

                
This example creates a unique clustered index named PK_Products on the ProductID column of the Products table.

                
CREATE UNIQUE CLUSTERED INDEX PK_Products
ON Products (ProductID);

            


             

                
Example 3: Creating a Non-Clustered Index with Included Columns

                
This example creates a non-clustered index that includes the EmailAddress and PhoneNumber columns to cover queries that select these columns.

                
CREATE NONCLUSTERED INDEX IX_Contacts_LastName
ON Contacts (LastName)
INCLUDE (EmailAddress, PhoneNumber);

            



            
Syntax Elements

            
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name: Specifies the creation of an index, optionally making it unique and defining its type (clustered or non-clustered).

            
ON <object> ( { <index_column> [ ASC | DESC ] [ INCLUDE ( <index_column_name> [ ,...n ] ) ] } [ ,...n ] ): Defines the table or view, the columns to be indexed, their sort order, and any additional columns to be included at the leaf level.

            
WITH ( <index_option> [ ,...n ] ): Allows configuration of index properties like FILLFACTOR, PAD_INDEX, ONLINE, etc.

            
ON <filegroup>: Assigns the index to a specific filegroup.


            
See Also

            

                
            

        
    
    

        

            
©  Microsoft Corporation. All rights reserved.