Create a nonclustered non-unique index within the CREATE TABLE statement with SQL Server

It is possible to create a primary key or unique index within a SQL Server CREATE TABLE statement. Is it possible to create a non-unique index within a CREATE TABLE statement?

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL

    -- This creates a primary key
    ,CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (a)

    -- This creates a unique nonclustered index on columns b and c
    ,CONSTRAINT IX_MyTable1 UNIQUE (b, c)

    -- Is it possible to create a non-unique index on columns d and e here?
    -- Note: these variations would not work if attempted:
    -- ,CONSTRAINT IX_MyTable2 INDEX (d, e)
    -- ,CONSTRAINT IX_MyTable3 NONCLUSTERED INDEX (d, e)
);
GO

-- The proposed non-unique index should behave identically to
-- an index created after the CREATE TABLE statement. Example:
CREATE NONCLUSTERED INDEX IX_MyTable4 ON MY_TABLE (d, e);
GO

Again, the goal is to create the non-unique index within the CREATE TABLE statement, not after it.

For what it's worth, I did not find the [SQL Server Books Online entry for CREATE TABLE] to be helpful.

Also, [This Question] is nearly identical, but the accepted answer does not apply.

Solution 1:

As of SQL 2014, this can be accomplished via inline index creation:

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL

    -- This creates a primary key
    ,CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (a)

    -- This creates a unique nonclustered index on columns b and c
    ,CONSTRAINT IX_MyTable1 UNIQUE (b, c)

    -- This creates a standard non-clustered index on (d, e)
    ,INDEX IX_MyTable4 NONCLUSTERED (d, e)
);
GO

Prior to SQL 2014, CREATE/ALTER TABLE only accepted CONSTRAINTs to be added, not indexes. The fact that primary key and unique constraints are implemented in terms of an index is a side effect.

Solution 2:

TLDR:

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL index IX_indexName nonclustered
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL
)

Details

As per T-SQL CREATE TABLE documentation, in 2014 the column definition supports defining an index:

<column_definition> ::=  
column_name <data_type>  
    ...
    [ <column_index> ]  

and <column_index> grammar is defined as:

<column_index> ::=   
 INDEX index_name [ CLUSTERED | NONCLUSTERED ]  
    [ WITH ( <index_option> [ ,... n ] ) ]  
    [ ON { partition_scheme_name (column_name )   
         | filegroup_name  
         | default   
         }  
    ]   
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]  
  

So a lot of what you can do as a separate statement can be done inline. I noticed include is not an option in this grammar so some things are not possible.

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL index IX_indexName nonclustered
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL
)

You can also have inline indexes defined as another line after columns, but within the create table statement, and this allows multiple columns in the index, but still no include clause:

< table_index > ::=   
{  
    {  
      INDEX index_name [ CLUSTERED | NONCLUSTERED ]   
         (column_name [ ASC | DESC ] [ ,... n ] )   
    | INDEX index_name CLUSTERED COLUMNSTORE  
    | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE (column_name [ ,... n ] )  
    }  
    [ WITH ( <index_option> [ ,... n ] ) ]   
    [ ON { partition_scheme_name (column_name )   
         | filegroup_name  
         | default   
         }  
    ]   
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]  
  
}   

For example here we add an index on both columns c and d:

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL index IX_MyTable_b nonclustered
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL

    ,index IX_MyTable_c_d nonclustered (c,d)
)

Solution 3:

The accepted answer of how to create an Index inline a Table creation script did not work for me. This did:

CREATE TABLE [dbo].[TableToBeCreated]
(
    [Id] BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ,[ForeignKeyId] BIGINT NOT NULL
    ,CONSTRAINT [FK_TableToBeCreated_ForeignKeyId_OtherTable_Id] FOREIGN KEY ([ForeignKeyId]) REFERENCES [dbo].[OtherTable]([Id])
    ,INDEX [IX_TableToBeCreated_ForeignKeyId] NONCLUSTERED ([ForeignKeyId])
)

Remember, Foreign Keys do not create Indexes, so it is good practice to index them as you will more than likely be joining on them.

Solution 4:

It's a separate statement.

It's also not possible to insert into a table and select from it and build an index in the same statement either.

The BOL entry contains the information you need:

CLUSTERED | NONCLUSTERED
Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.

In a CREATE TABLE statement, CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED.

You can create an index on a PK field, but not a non-clustered index on a non-pk non-unique-constrained field.

A NCL index is not relevant to the structure of the table, and is not a constraint on the data inside the table. It's a separate entity that supports the table but is not integral to it's functionality or design.

That's why it's a separate statement. The NCL index is irrelevant to the table from a design perspective (query optimization notwithstanding).