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.

Answer 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.