Create a non-clustered non-unique index in a CREATE TABLE statement with SQL Server

Question

In SQL Server CREATE TABLE, you can create a primary key or a unique index. Is it possible to create a non-unique index inside 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 an imperfect index inside the CREATE TABLE statement, and not after it.

For what it's worth, I have not found [SQL Server Books Online entry for CREATE TABLE] to be useful.

In addition, [This question] is almost identical, but the accepted answer does not apply.

Answer 1

You can not. CREATE / ALTER TABLE accept only added CONSTRAINT, not indexes. The fact that the primary key and unique constraints are implemented in terms of the index is a side effect. To manage indexes, you have CREATE / ALTER / DROP INDEX, as you are well aware.

Why do you have such a requirement for declarations other than non-clustered indexes in the CREATE TABLE statement?

Note that SQL Server 2014 introduced an inline index option to create :

 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 non-clustered index on (d, e) ,INDEX IX_MyTable4 NONCLUSTERED (d, e) ); GO 

Answer 2

This is a separate statement.

It is also impossible to insert into a table and select from it and construct an index in the same expression.

The BOL record contains the necessary information:

CLUSTER | NONCLUSTERED
Indicates that a clustered or non-clustered index created for the PRIMARY KEY or UNIQUE constraint. Constraints PRIMARY KEY defaults to CLUSTERED and UNIQUE defaults to NONCLUSTERED.

In the CREATE TABLE, CLUSTERED statement, only one restriction can be specified. If CLUSTERED is specified for a UNIQUE constraint and PRIMARY, a KEY constraint is also specified. PRIMARY KEY is NOT NECESSARY by default.

You can create an index in a PK field, but a non-clustered index in a non-pk non-unique restricted field.

The NCL index is not related to the structure of the table and is not a limitation on the data inside the table. This is a separate object that maintains a table, but is not an integral part of its functionality or design.

That is why this is a separate statement. The NCL index is not related to the table in terms of design (despite query optimization).

Answer 3

The accepted answer on how to create an Index , built a table creation script, didn't work for me. It happened:

 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 that foreign keys do not create indexes, so it is recommended that you index them, since you are more likely to join them.

Answer 4

According to T-SQL CREATE TABLE , in 2014, the column definition supports index definition:

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

and 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" } ] 

Thus, much of what you can do as a separate statement can be made inline. I noticed that include not an option in this grammar, so some things are impossible.

 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 ) 

You can also have inline indexes defined as another row after the columns, but inside the create table statement, and this allows multiple columns to be used in the index, but still not 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 for 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) )