问题描述

主键标识为 INT 32 类型的 SQL 服务器表中的最大行数:

Is the maximum number of rows in an SQL server table where the Primary Key Identity is INT 32 type:

4,294,967,294

4,294,967,294

即 2,147,483,647 个正数加上 2,147,483,647 个负数

i.e 2,147,483,647 positive numbers add 2,147,483,647 negative numbers

我问的原因是一个系统注定要使用所有 2,147,483,647 个数字.

The reason I ask is a system was destined to use all 2,147,483,647 numbers.

在 -2,147,483,647 个月前成功将种子重置为负数.

Reset the seed to negative -2,147,483,647 months ago successfully.

还实现了一个代理任务来删除未使用的正数.

Also implemented an Agent task to remove unused positive numbers.

每天对 PK ID 的表计数检查报告表中的行数惊人地接近 2,147,483,647，但迄今为止尚未违反.

Table count check of PK ID each day reports the number of rows in the table is tantalisingly close to 2,147,483,647 but has not breached to date.

周六 28/2,125,167,844

Sat 28 / 2,125,167,844

星期五 27/2,128,445,105

Fri 27 / 2,128,445,105

星期四 26/2,128,704,866

Thur 26 / 2,128,704,866

星期三 25/2,128,935,436

Wed 25 / 2,128,935,436

周六 21/2,141,016,422

Sat 21 / 2,141,016,422

星期四 19/2,143,413,531

Thur 19 / 2,143,413,531

理论上，如果使用的所有正负标识都正确，那么这个行数应该达到 4,294,967,294 吗?

In theory this row count should reach 4,294,967,294 if all positive and negative identities where in use correct ?

奇怪的是，此计数从未大于最大正值.下面的查询是一个简单的行数，+/- 标识应该无关紧要.

Find it odd that this count has never been greater than the max positive value. The query below is a simple row count, +/- identities should not matter.

选择 COUNT (id) from table with (NOLOCK)

select COUNT (id) from table with (NOLOCK)

谢谢

推荐答案

是的，您可以再次一直到标识 0，并且您可以在表中保存 4,294,967,294 条记录.但是你说你正在删除未使用的正数"，如果这个过程总是先删除最旧的记录，那么它应该会带来很多麻烦.如果您要删除随机数据，请不要指望这些数字会被身份自动使用.插入标识列时，SQL Server 会记住最后插入的标识并将其加 1，然后插入，如果数字存在，如果该列上有唯一键，则会给出键冲突错误.

Yes you can go all the way up to identity 0 again and you could go up to holding 4,294,967,294 records in your table. However you say you're "removing unused positive numbers" if this process always deletes the oldest records first then it should give much troubles. If you're deleting random data don't expect those numbers to be automatically be used by identity. When inserting into an identity column SQL Server remembers the last inserted identity and increments it by 1 and then does the insert, if the number exists, it will give a key violation error if there is a unique key on that column.

PS:有更快的方法来计算表中的所有行:

PS: There are faster ways to count all rows in a table:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('Transactions')   
AND (index_id=0 or index_id=1);

PS2:确保将计数结果放入 bigint ;)

PS2: Makes sure to put the result of an count in a bigint ;)