SQL Server中自动增量主键的上限

问题描述：

SQL Server中自动增量主键的上限是多少? 当SQL Server自动增量主键达到上限时会发生什么?

What is the upper limit for an autoincrement primary key in SQL Server? What happens when an SQL Server autoincrement primary key reaches its upper limit?

答

乔尔的答案是正确的，它是您使用的任何数据类型的上限.

Joel's answer is correct, it is the upper limit of whatever datatype you use.

这是其中两个的示例:

int:2 ^ 31-1(2,147,483,647)
bigint:2 ^ 63-1(9,223,372,036,854,775,807)

我实际从事的工作已经达到极限.实际错误是:

I have actually hit the limit at a job I worked at. The actual error is:


    Msg 8115, Level 16, State 1, Line 1
    Arithmetic overflow error converting IDENTITY to data type int.
    Arithmetic overflow occurred.

对此有一些解决方法，我可以想到.数字1可能很困难，不太可能，数字2很容易，但是可能会在您的代码库中引起问题.

There are a couple fixes to this I can think of off the top of my head. Number 1 is probably very hard and not very likely, number 2 is easy, but will probably cause problems in your code base.

如果身份列对您来说无关紧要(它不是外键等)，那么您可以重新设置数据库的种子并重置身份列.
将身份"列更改为更大的数字.因此，例如，如果您溢出了一个int，请将您的identity列更改为一个大int.祝您好运:)

可能还有其他修复方法，但没有一个简单的魔术子弹.我只是希望不会在一系列关系的中心表中发生这种情况，因为如果发生这种情况，您会感到很痛苦.这不是一个硬性解决方案，只是一个乏味而漫长的解决方案.

There are probably other fixes, but there is no magic bullet easy one. I just hope this doesn't happen in a table that is the center of a bunch of relationships, because if it does, you're in for a lot of pain. It's not a hard fix, just a tedious and long one.

SQL Server中自动增量主键的上限

相关推荐