SQL Server中标识种子触及主键已存在值时的插入行为

咱们直接说实际情况——你的三个设想都不对，具体行为分两种场景来看：

单次插入的情况 #

当你把IDENTITY种子设成某个值，导致下一次生成的ID刚好是表中已存在的主键值时：

• 第一次插入会直接失败，抛出主键约束冲突的错误（类似 Violation of PRIMARY KEY constraint... Cannot insert duplicate key）。
• SQL Server不会自动调整IDENTITY的种子/计数器——下一次插入时，它还是会尝试生成同一个冲突的ID，导致再次失败。
• 这种情况下，你必须显式调用 DBCC CHECKIDENT 重新设定种子到当前表中最大的ID值（比如你的场景里是1030），这样下一次生成的ID就是1031，才能正常插入。

多行插入的情况 #

如果是一次性插入多行数据（比如用 VALUES ('x'), ('y') 这种语法），行为会有一点不同：

• SQL Server会预先分配一批IDENTITY值（默认INT类型的缓存是100个），然后尝试插入所有行。只要其中某一行的ID和已存在的主键冲突，整个插入操作会回滚，没有任何行被插入。
• 这里要注意：即使插入失败，IDENTITY的计数器已经被更新到这批预分配值的最后一个。比如你种子设为1029，预分配了100个值（1029到1128），插入时第一个ID1029冲突，插入失败后，下一次插入会直接尝试1129，而不是回到1029。

举个你的场景例子：假设你把种子设为1029，下一个要生成的ID是1030（因为增量是1），而表中已经有ID1030了：

• 单次插入'x'：失败，报错主键冲突，下一次插入还是会尝试1030，仍然失败。
• 多行插入'x'和'y'：整个插入回滚，没有数据插入，下一次插入会尝试1129（因为预分配了100个值，1029-1128，计数器跳到1129）。

总结一下：不管是单次还是多行插入，只要生成的IDENTITY值冲突，插入都会失败，且不会自动调整种子——要么你显式重置种子，要么在多行插入失败后，计数器跳到预分配的下一个值（这时候如果那个值不冲突就能插入）。

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