Microsoft SQL Server：现有表新增主键与索引问题

数据背景 #

• 表Photos包含195k行数据，主键为uniqueidentifier类型，当前聚集索引几乎100%碎片
• 表行大小未超出int范围，无外键关联至该表主键
• Job_ID列为关联DeliveryTicket表的非唯一外键，类型为递增int，与DeliveryTicket是一对多关系
• 多数业务查询基于Job_ID而非主键

问题解答 #

1. 添加自增列New_ID并设为主键（填充现有数据） #

由于无外键依赖原主键，可按以下步骤在维护窗口执行：

1. 删除原聚集主键约束：

ALTER TABLE [dbo].[Photos]
DROP CONSTRAINT [PK_Photos];

2. 添加自增列New_ID：
   SQL Server会自动为现有195k行数据填充连续递增的int值：

ALTER TABLE [dbo].[Photos]
ADD [New_ID] INT IDENTITY(1,1) NOT NULL;

3. 创建New_ID的非聚集主键：
   （预留空间将聚集索引绑定到Job_ID，最大化查询性能）

ALTER TABLE [dbo].[Photos]
ADD CONSTRAINT [PK_Photos_New_ID] PRIMARY KEY NONCLUSTERED ([New_ID]);

2. 是否应在Job_ID创建聚集索引？ #

完全应该，核心原因：

• 聚集索引的物理数据按索引键排序存储，针对Job_ID的查询（如WHERE Job_ID = N、关联DeliveryTicket的JOIN操作）可直接定位数据，无需额外书签查找，是查询效率最高的索引类型
• Job_ID是递增int类型，新数据插入时会追加到索引末尾，不会产生页分裂，从根源解决原uniqueidentifier主键导致的100%碎片问题
• 创建非唯一聚集索引的SQL语句：

CREATE CLUSTERED INDEX [CI_Photos_Job_ID] ON [dbo].[Photos] ([Job_ID]);

3. 基于Job_ID最小值创建自增主键是否更优？ #

不推荐此方案，理由如下：

• 主键的核心作用是唯一标识行，int自增列New_ID已满足需求，结构简单、维护成本低
• 查询性能的核心优化点是聚集索引的位置，而非主键是否与查询键关联。将聚集索引直接绑定到Job_ID，比改造主键结构的收益更直接、高效
• 若将主键设计为Job_ID+自增后缀的复合键，会增大主键体积，反而增加索引维护开销，对性能无增益

原表创建语句 #

CREATE TABLE [dbo].[Photos]
(
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
    [Photo_Serial_Number] [int] IDENTITY(1,1) NOT NULL,
    [Job_ID] [int] NOT NULL,
    [Photo_Upload_Disposition_Type] [int] NOT NULL,
    [NET_Rating] [real] NULL,
    [Rating_ID] [int] NULL,
    [Image_Data] [varbinary](max) FILESTREAM  NOT NULL,
    [File_Name] [nvarchar](50) NOT NULL,
    [File_Extension] [varchar](5) NOT NULL,
    [Image_Comments] [nvarchar](1000) NULL,

     CONSTRAINT [PK_Photos] PRIMARY KEY CLUSTERED (ID)
);

内容的提问来源于stack exchange，提问作者NDallasDan