使用 SQL Server 在 CREATE TABLE 语句中创建非聚集非唯一索引 [英] Create a nonclustered non-unique index within the CREATE TABLE statement with SQL Server

问题描述

可以在 SQL Server CREATE TABLE 语句中创建主键或唯一索引.是否可以在 CREATE TABLE 语句中创建非唯一索引?

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

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

Again, the goal is to create the non-unique index within the CREATE TABLE statement, not after it.

对于它的价值，我没有找到 [SQL Server 联机丛书条目为 CREATE TABLE] 提供帮助.

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.

推荐答案

从 SQL 2014 开始，这可以通过 内联索引创建:

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

---

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

---

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.

这篇关于使用 SQL Server 在 CREATE TABLE 语句中创建非聚集非唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！