sql-server - 单个表上的多个聚集索引？

Question

我以为我们只能在一张表上放置一个聚集索引，并在一张表上放置多个非聚集索引，但是使用下面的代码我可以轻松地向我的表添加多个聚集索引。

CREATE CLUSTERED INDEX TBL_MULTI_LC_HIST ON dbo.TBL_MULTI_LC_HIST (ID,AsOfDate) 

这是完全错误的吗？

Answer 1

不可能为单个表创建多个聚集索引。来自 the docs (强调我的):

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.

例如，这将失败:

CREATE TABLE Thing
(
    Column1 INT NOT NULL,
    Column2 INT NOT NULL
)

CREATE CLUSTERED INDEX IX1 ON dbo.Thing(Column1)

CREATE CLUSTERED INDEX IX2 ON dbo.Thing(Column2)

错误:

Cannot create more than one clustered index on table 'dbo.Thing'. Drop the existing clustered index 'IX1' before creating another.

示例:http://www.sqlfiddle.com/#!18/53a63/1

但是，您可以拥有一个包含多个列的索引，这可能是您感到困惑的地方:

CREATE CLUSTERED INDEX IX3 ON dbo.Thing(Column1, Column2)