sql-server - 创建聚集主键的 SQL Server 语法

Question

我想将表移动到新文件组。使用the accepted answer as a starting point :

CREATE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

我根据我的用途调整它:

CREATE CLUSTERED INDEX PK_AuditLog_AuditLogID
   ON dbo.AuditLog(AuditLogID)
   WITH DROP_EXISTING
   ON [TheOtherFileGroup]

给出错误:

Msg 1907, Level 16, State 1, Line 1
Cannot recreate index 'PK_AuditLog_AuditLogID'. The new index definition does not match the constraint being enforced by the existing index.

我认为这是因为 PK_AuditLog_AuditLogID 是

• 聚集索引
• 主键

所以我需要创建主键聚集索引的语法WITH DROP_EXISTING

Answer 1

CREATE UNIQUE CLUSTERED INDEX PK_AuditLog_AuditLogID
   ON dbo.AuditLog(AuditLogID)
   WITH DROP_EXISTING
   ON [TheOtherFileGroup]

保留了逻辑主键约束(尽管在 2012 年进行了测试)

CREATE TABLE dbo.AuditLog
(
AuditLogID int constraint PK_AuditLog_AuditLogID primary key
)


CREATE UNIQUE CLUSTERED INDEX PK_AuditLog_AuditLogID
   ON dbo.AuditLog(AuditLogID)
   WITH DROP_EXISTING
   ON [Primary]



SELECT CONSTRAINT_TYPE  /*Returns PRIMARY KEY*/
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'PK_AuditLog_AuditLogID'