使用 SQLServer 在 CREATETABLE 语句中创建非聚集的非唯一索引

可以在 SQLServerCREATETABLE 语句中创建主键或唯一索引。是否可以在 CREATETABLE 语句中创建 非唯一性索引？

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

同样，目标是在 CREATETABLE 语句中创建非唯一索引，而不是在它之后创建。

不管怎样，我觉得 [ SQLServer 为 CREATETABLE 联机丛书条目]没什么帮助。

Also, [这个问题] is nearly identical, but the accepted answer does not apply.

166245 次浏览
小开

这是单独的声明。

也不可能插入到表中并从中进行选择，然后在同一语句中构建索引。

BOL 条目包含您需要的信息:

聚集 | 非聚集
说明一下 聚集索引或非聚集索引是 为主键或 UNIQUE 创建 主键约束 default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.

在 CREATETABLE 语句中，CLUSTERED 只能指定一个 如果指定了 CLUSTERED，则为 用于 UNIQUE 约束和 PRIMARY 还指定了 KEY 约束，则 主键默认为非聚集。

您可以在 PK 字段上创建索引，但不能在非 PK 非唯一约束字段上创建非聚集索引。

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.

这就是为什么它是一个单独的声明。从设计角度来看，NCL 索引与表无关(尽管进行了查询优化)。

小开
最佳答案

从 SQL 2014开始，这可以通过 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

在 SQL 2014之前，CREATE/ALTER TABLE 只接受要添加的 CONSTRINT，而不接受索引。主键和唯一约束是根据索引实现的，这是一个副作用。

小开

关于如何在 Table 创建脚本中内联创建 索引的公认答案对我来说不起作用。这样做的结果是:

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])
)

请记住，外键不会创建索引，所以最好将它们编入索引，因为您很可能会加入它们。

小开

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

根据 创建表文档，在2014年，列定义支持定义一个索引:

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

< column _ index > 语法定义为:

<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
)

您还可以将内联索引定义为列之后的另一行，但是在 create table 语句中，这允许索引中有多个列，但是仍然没有 include子句:

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

}

例如，这里我们在 c 和 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)
)

提交答案