SQL Server 中自增主键的上限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/261815/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Upper limit for autoincrement primary key in SQL Server
提问by Dema
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?
SQL Server 中自增主键的上限是多少？当 SQL Server 自动增量主键达到其上限时会发生什么？
回答by CubanX
Joel's answer is correct, it is the upper limit of whatever datatype you use.
乔尔的回答是正确的，它是您使用的任何数据类型的上限。
Here's an example of two of them:
这是其中两个的示例：
- int: 2^31-1 (2,147,483,647)
- bigint: 2^63-1 (9,223,372,036,854,775,807)
- 整数：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.
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.
对此我可以想到一些解决方法。编号 1 可能非常困难且不太可能，编号 2 很容易，但可能会导致您的代码库出现问题。
- If the identity column doesn't matter to you (it's not a Foreign Key, etc.) then you can just reseed the database and reset the identity column.
- Change your identity column to a bigger number. So for example if you've overflowed an int, change your identity column to a big int. Good luck overflowing that :)
- 如果身份列对您无关紧要（它不是外键等），那么您只需重新设定数据库的种子并重置身份列即可。
- 将您的身份列更改为更大的数字。因此，例如，如果您溢出了一个 int，请将您的标识列更改为一个大 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.
可能还有其他修复方法，但没有简单的灵丹妙药。我只是希望这不会发生在作为一堆关系中心的桌子上，因为如果发生了，你会很痛苦。这不是一个困难的修复，只是一个乏味而漫长的修复。
回答by Joel Coehoorn
It depends on the datatype. If you use bigint, you're unlikely to ever overflow. Even a normal int gives you a couple billion rows. I've never overflowed, so I can't tell you what happens if you do.
这取决于数据类型。如果使用 bigint，则不太可能溢出。即使是普通的 int 也会给你几十亿行。我从来没有溢出，所以我不能告诉你如果你这样做会发生什么。
回答by Joel Coehoorn
I'll tell you what happens.... my data stopped inserting into that specific table. The database still works but I found data missing and inconsistent. With a little research, I found the error table, then ran a manual insert. The error is the same as above.
我会告诉你发生了什么......我的数据停止插入到那个特定的表中。数据库仍然有效，但我发现数据丢失且不一致。通过一点研究，我找到了错误表，然后运行了手动插入。错误与上述相同。
Had to change the column to BIGINT. On a 26GB database on a somewhat slow server, took about 30 minutes. On the archive version of the database (150GB or so) it took quite a bit longer.
不得不将列更改为 BIGINT。在有点慢的服务器上的 26GB 数据库上，大约需要 30 分钟。在数据库的存档版本（150GB 左右）上，它花费了相当长的时间。
Fortunately, not too many relationships for this table so the pain was pretty slight.
幸运的是，这张表没有太多关系，所以痛苦很轻微。
回答by Joel Coehoorn
DBCC CHECKIDENT (SomeTable, RESEED, 1)
DBCC CHECKIDENT (SomeTable, RESEED, 1)
This resets the identity to 1 on table 'SomeTable'
这会将表 'SomeTable' 上的标识重置为 1
Not sure if this is the best way to do this.
不确定这是否是最好的方法。
回答by Fred
Data types descriptions:
数据类型说明：
BIGINT Integer data from -2^63 through 2^63 - 1
INT Integer data from -2^31 through 2^31 - 1
SMALLINT Integer data from -2^15 through 2^15 - 1
TINYINT Integer data from 0 through 255
When you reach the upper limit the autoincrement goes to the lower limit.
当您达到上限时，自动增量会转到下限。
