Azure Synapse IDENTITY列种子值未按预期生效问题咨询

需求目标 #

创建一张表，先插入3条技术测试用的虚拟行，之后所有有效数据的Id需从100开始递增。

建表脚本 #

IF OBJECT_ID(N'dbo.IdentityInsertTest') IS NOT NULL DROP TABLE dbo.IdentityInsertTest; 
GO 
CREATE TABLE dbo.IdentityInsertTest ( 
 Id BIGINT NOT NULL IDENTITY (100, 1) -- 种子值为100，而非1! 
 ,Col1 INT 
 ,Col2 INT 
) WITH ( 
 HEAP 
 ,DISTRIBUTION = HASH(Col1) 
) ;

异常现象 #

• 若仅执行虚拟行插入+有效数据插入（跳过临时行插入步骤）：通过IDENTITY_INSERT插入Id为1、2、3的虚拟行后，再插入有效数据时，有效数据的Id会从4开始递增，完全不符合IDENTITY(100,1)的初始设置。
• 若先插入临时行，再执行虚拟行+有效数据插入：有效数据的Id会正常从100开始递增，符合预期。

临时解决流程 #

按照以下步骤执行即可保证有效数据Id从100开始：

1. 先向空表插入一条临时行（后续可删除）

INSERT INTO dbo.IdentityInsertTest(Col1, Col2) 
SELECT -1 AS Col1 ,-2 AS Col2 WHERE 1 = 1 ;

2. 插入技术虚拟行

SET IDENTITY_INSERT dbo.IdentityInsertTest ON;
INSERT INTO dbo.IdentityInsertTest(Id, Col1, Col2) VALUES(1, 1, 1);
INSERT INTO dbo.IdentityInsertTest(Id, Col1, Col2) VALUES(2, 2, 2);
INSERT INTO dbo.IdentityInsertTest(Id, Col1, Col2) VALUES(3, 3, 3);
SET IDENTITY_INSERT dbo.IdentityInsertTest OFF;

3. 删除第一步插入的临时行

DELETE FROM dbo.IdentityInsertTest WHERE Col1 = -1 AND Col2 = -2;

4. 后续插入数据时保持IDENTITY_INSERT为OFF，即可自动获取从100开始递增的Id

疑问解答：这是否是Azure Synapse的Bug？ #

这其实不算严格意义上的Bug，而是Azure Synapse作为分布式数据仓库的身份列行为特性，和单实例SQL Server的逻辑有差异：

• 在单实例SQL Server中，即使通过IDENTITY_INSERT插入低于种子值的行，后续自动生成的身份值依然会从初始种子值（这里是100）开始。
• 但在Azure Synapse中，当你开启IDENTITY_INSERT插入显式Id时，系统会将身份列的当前追踪值更新为你插入的最大Id值（也就是3），关闭IDENTITY_INSERT后，自动生成的Id就会从这个当前值+1（即4）开始递增，完全忽略了初始的种子设置。

而先插入临时行的作用，是让系统先将身份列的当前追踪值设置为种子值100，之后即使插入了更小的Id（1、2、3），系统也不会再更新这个当前追踪值，所以后续插入数据时会从100开始递增。

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