問題描述
主鍵標識為 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 ;)
這篇關于當種子以最大負值開始時，SQL 表中的最大行數其中 PK 為 INT 32?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！