I thought it was a bug but after reading this article http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is, I found that it's a new feature of SQL Server 2012.
我认为这是一个错误,但在阅读了这篇文章后,我发现这是一个新功能http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is SQL Server 2012。
This feature increments your last identity column value by 1000(ints) for new rows(10000 for bigints) automatically.
此功能会自动将您的上一个标识列值增加1000(int)以获取新行(bigint为10000)。
I am still trying the solution given in the article but I don't have any problem if this jump happens at client side. Because I am showing hashed version of IDs to client. It's his own demand not mine.
我仍在尝试文章中给出的解决方案,但如果在客户端发生这种跳转,我没有任何问题。因为我向客户端显示了哈希版本的ID。这是他自己的要求而不是我的要求。
But I am wondering what if the values of these identity columns goes more than the range of the data type (int or bigint)? How it handles the range and size of the column?
但我想知道如果这些标识列的值超过数据类型的范围(int或bigint)会怎么样?它如何处理色谱柱的范围和大小?
3 个解决方案
#1
4
Existing Identity columns will fail with "Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred." See http://www.sql-server-performance.com/2006/identity-integer-scope/ for discussion.
现有标识列将失败,并显示“服务器:消息8115,级别16,状态1,行2,算术溢出错误,将IDENTITY转换为数据类型int。发生算术溢出。”有关讨论,请参见http://www.sql-server-performance.com/2006/identity-integer-scope/。
There isnt a reason to suspect that Identity Jump will have a different behaviour. I would not want it to go and hunt for unused identities in an earlier sequence.
没有理由怀疑Identity Jump会有不同的行为。我不希望它在早期的序列中寻找未使用的身份。
#2
2
Why don't you use Sequence in MS Server 2012.
为什么不在MS Server 2012中使用Sequence。
Sample Code For Sequence will be as follows and you don't need ADMIN permission to create Sequence.
序列的示例代码如下,您不需要ADMIN权限来创建序列。
CREATE SEQUENCE SerialNumber AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999
CYCLE;
GO
In case if you need to add the leading '0' to Sequence then simple do it with following code :
如果您需要将前导'0'添加到Sequence,那么使用以下代码进行简单操作:
RIGHT ('0000' + CAST (NEXT VALUE FOR SerialNumber AS VARCHAR(5)), 4) AS SerialNumber
#3
-7
The issue was fixed in the latest version of the SQL Server updates. It was a bug.
最新版本的SQL Server更新中已修复此问题。这是一个错误。
If you still want to use the same version, you will have to check your identity at intervals and reseed it with the current identity if they had jumped over 1000s value.
如果您仍想使用相同的版本,则必须每隔一段时间检查一次身份,如果超过1000s值,则使用当前身份重新设置。
This script can help you to know Seed Values, Increment Values and Current Identity Column Value http://blog.sqlauthority.com/2014/08/29/sql-server-query-to-find-seed-values-increment-values-and-current-identity-column-value-of-the-table-with-max-value-of-datatype-part-2/
此脚本可以帮助您了解种子值,增量值和当前标识列值http://blog.sqlauthority.com/2014/08/29/sql-server-query-to-find-seed-values-increment-values - 和 - 表与 - 最大 - 值的-数据类型部分-2-电流身份列值的-/
My suggestion: Apply resent patches, CU or service pack.
我的建议:应用重新发送补丁,CU或Service Pack。
#1
4
Existing Identity columns will fail with "Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred." See http://www.sql-server-performance.com/2006/identity-integer-scope/ for discussion.
现有标识列将失败,并显示“服务器:消息8115,级别16,状态1,行2,算术溢出错误,将IDENTITY转换为数据类型int。发生算术溢出。”有关讨论,请参见http://www.sql-server-performance.com/2006/identity-integer-scope/。
There isnt a reason to suspect that Identity Jump will have a different behaviour. I would not want it to go and hunt for unused identities in an earlier sequence.
没有理由怀疑Identity Jump会有不同的行为。我不希望它在早期的序列中寻找未使用的身份。
#2
2
Why don't you use Sequence in MS Server 2012.
为什么不在MS Server 2012中使用Sequence。
Sample Code For Sequence will be as follows and you don't need ADMIN permission to create Sequence.
序列的示例代码如下,您不需要ADMIN权限来创建序列。
CREATE SEQUENCE SerialNumber AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999
CYCLE;
GO
In case if you need to add the leading '0' to Sequence then simple do it with following code :
如果您需要将前导'0'添加到Sequence,那么使用以下代码进行简单操作:
RIGHT ('0000' + CAST (NEXT VALUE FOR SerialNumber AS VARCHAR(5)), 4) AS SerialNumber
#3
-7
The issue was fixed in the latest version of the SQL Server updates. It was a bug.
最新版本的SQL Server更新中已修复此问题。这是一个错误。
If you still want to use the same version, you will have to check your identity at intervals and reseed it with the current identity if they had jumped over 1000s value.
如果您仍想使用相同的版本,则必须每隔一段时间检查一次身份,如果超过1000s值,则使用当前身份重新设置。
This script can help you to know Seed Values, Increment Values and Current Identity Column Value http://blog.sqlauthority.com/2014/08/29/sql-server-query-to-find-seed-values-increment-values-and-current-identity-column-value-of-the-table-with-max-value-of-datatype-part-2/
此脚本可以帮助您了解种子值,增量值和当前标识列值http://blog.sqlauthority.com/2014/08/29/sql-server-query-to-find-seed-values-increment-values - 和 - 表与 - 最大 - 值的-数据类型部分-2-电流身份列值的-/
My suggestion: Apply resent patches, CU or service pack.
我的建议:应用重新发送补丁,CU或Service Pack。