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

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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   lt column definition gt        column name  lt data type gt                   lt column index gt       and  lt column index gt  grammar is defined as   lt column index gt          INDEX index name   CLUSTERED   NONCLUSTERED           WITH    lt index option gt         n               ON   partition scheme name  column name                 filegroup name              default                                FILESTREAM ON   filestream filegroup name   partition scheme name    quot NULL quot            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   lt  table index  gt                           INDEX index name   CLUSTERED   NONCLUSTERED                column name   ASC   DESC          n              INDEX index name CLUSTERED COLUMNSTORE         INDEX index name   NONCLUSTERED   COLUMNSTORE  column name        n                     WITH    lt index option gt         n                ON   partition scheme name  column name                 filegroup name              default                                FILESTREAM ON   filestream filegroup name   partition scheme name    quot NULL quot                 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

[sql-server] Create a nonclustered non-unique index within the CREATE TABLE statement with SQL Server

Examples related to sql-server