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.
Best Answer
As of SQL 2014, this can be accomplished via inline index creation:
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.