分区切换（stage>dbo）后主键冲突：无需重新种子值的解决方法

Violation of PRIMARY KEY constraint 'PK_stmp_tst1'. Cannot insert duplicate key in object 'dbo.stmp_tst'. The duplicate key value is (1).

该错误发生在跨架构表执行分区切换后的插入操作中，完整重现步骤及脚本如下：

完整重现步骤 #

I. 创建同结构跨架构表 #

CREATE TABLE dbo.stmp_tst(
    [inn] [varchar](20) NULL,
    [id] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_stmp_tst] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go


CREATE TABLE stage.stmp_tst(
    [inn] [varchar](20) NULL,
    [id] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_stmp_tst] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

II. 向stage表插入数据 #

insert into stage.stmp_tst (inn)
select '1111'

III. 执行分区切换 #

alter table stage.stmp_tst switch partition 1 to dbo.stmp_tst partition 1;

IV. 向dbo表插入新数据 #

insert into dbo.stmp_tst (inn)
select '1111'

V. 触发主键冲突错误 #

Violation of PRIMARY KEY constraint 'PK_stmp_tst1'. Cannot insert duplicate key in object 'dbo.stmp_tst'. The duplicate key value is (1).

目前可通过DBCC CHECKIDENT ('dbo.stmp_tst', RESEED);解决，但该操作耗时，询问是否能无需重置种子值完成分区切换并避免后续冲突。

解决方案：避免IDENTITY种子冲突的分区切换方案 #

问题根源：分区切换是物理页移动操作，不会自动同步目标表的IDENTITY种子值。stage表插入数据生成ID=1后，切换到dbo表时，dbo表的IDENTITY种子仍停留在初始值1，后续插入会尝试生成重复ID，触发主键冲突。

以下是无需（或优化）重置种子值的解决思路：

1. 提前规划IDENTITY种子范围（简单直接） #

创建stage表时，指定与dbo表不重叠的IDENTITY起始值，确保两边生成的ID天然无冲突：

CREATE TABLE stage.stmp_tst(
    [inn] [varchar](20) NULL,
    [id] [bigint] IDENTITY(1000000,1) NOT NULL, -- 起始值设为远大于dbo表预期最大ID的数值
 CONSTRAINT [PK_stmp_tst] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

这种方式下，切换数据到dbo表后，dbo表的IDENTITY序列仍按自身规则增长，不会与stage表过来的ID冲突。

2. 切换前同步种子值（优化重置操作） #

如果无法提前规划种子范围，可在分区切换前同步dbo表的种子值，避免切换后插入冲突：

-- 获取stage表当前IDENTITY种子值
DECLARE @stageSeed bigint;
SELECT @stageSeed = IDENT_CURRENT('stage.stmp_tst');

-- 重置dbo表种子为stage表种子值，确保后续插入从@stageSeed+1开始
DBCC CHECKIDENT ('dbo.stmp_tst', RESEED, @stageSeed);

-- 执行分区切换
alter table stage.stmp_tst switch partition 1 to dbo.stmp_tst partition 1;

该方式比切换后再重置更高效，因为切换前dbo表数据量通常更小，可减少操作耗时。

3. 使用SEQUENCE替代IDENTITY（长期推荐方案） #

若使用SQL Server 2012及以上版本，可通过共享SEQUENCE对象替代IDENTITY列，让两个表共用同一个ID生成序列：

-- 创建共享SEQUENCE
CREATE SEQUENCE dbo.stmp_tst_id_seq
    AS bigint
    START WITH 1
    INCREMENT BY 1;
GO

-- 创建dbo表，用SEQUENCE生成ID
CREATE TABLE dbo.stmp_tst(
    [inn] [varchar](20) NULL,
    [id] [bigint] NOT NULL DEFAULT NEXT VALUE FOR dbo.stmp_tst_id_seq,
 CONSTRAINT [PK_stmp_tst] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 创建stage表，同样使用这个SEQUENCE
CREATE TABLE stage.stmp_tst(
    [inn] [varchar](20) NULL,
    [id] [bigint] NOT NULL DEFAULT NEXT VALUE FOR dbo.stmp_tst_id_seq,
 CONSTRAINT [PK_stmp_tst] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

这种方式下，无论直接插入dbo表还是从stage表切换数据，ID都由同一个SEQUENCE生成，天然避免重复，完全无需手动重置种子值。

内容的提问来源于stack exchange，提问作者Semyon-coder