数据库主键不连续问题:
分析:
(1)首次插入数据(正常)
(2)重启服务后,再次插入(不连续)
(3)分析:
谁跟你说过自增字段保证这个字段值连续的?自增字段就是“保证数值不减小”而已。
如果你以为自增字段是做业务流水号的,那是你理解错误。
sqlserver的增长机制跟oracle不一样
oracle用的序列,每次从序列里去取,跟你表里存了什么无关.
如果你当前序列值是100,你手动插入个101进去,下次自动插入的时候就会报错
Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.
Solutions
If we are not interested in this so called feature, then we can do two things to stop that jump.
Using Sequence
Register -t272 to SQL Server Startup Parameter
官方解释:https://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-column-Value-Jump-Is
分析:
(1)首次插入数据(正常)
(2)重启服务后,再次插入(不连续)
(3)分析:
谁跟你说过自增字段保证这个字段值连续的?自增字段就是“保证数值不减小”而已。
如果你以为自增字段是做业务流水号的,那是你理解错误。
sqlserver的增长机制跟oracle不一样
oracle用的序列,每次从序列里去取,跟你表里存了什么无关.
如果你当前序列值是100,你手动插入个101进去,下次自动插入的时候就会报错
而sqlserver本身按你当前内容最大值累加
Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.
Solutions
If we are not interested in this so called feature, then we can do two things to stop that jump.
Using Sequence
Register -t272 to SQL Server Startup Parameter
官方解释:https://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-column-Value-Jump-Is