如何減少標識值以避免整數溢出？

我有一些葉表（對它們沒有 FK），其中包含數十萬條記錄，這些記錄用於使用 Entity Framework ORM 同步一些外部數據。這涉及一些 DELETE，然後是 BULK INSERT。

對於大多數表，一些舊值可能會永遠保留，因此我不能將 SEQUENCEs 與 CYCLE 一起使用，正如其中一條評論所建議的那樣。

一種影響是身份值會隨著時間的推移不斷增加，我希望能夠降低它們的值。

這個問題及其答案解釋了身份值無法更新，即使identity_insert是在表上。

一種快速的方法是將所有數據傳輸到緩衝區表並通過重命名執行切換。類似於以下內容：

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);  

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.    

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)    

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.  

-- this should be replaced with commit when not in test mode
rollback

我有能力在夜間進行這種操作，而且我的數據量似乎只需要幾秒鐘（本例為 4 秒）。

問題： **是否有任何方法可以避免執行完整數據複製以獲取標識列的較小值？**或者這是在我的上下文中最好的方法之一（合理的數據量並且能夠鎖定一些表幾秒鐘）。

您可以嘗試簡單地刪除並重新添加 Identity 列而不複製整個表。這與您嘗試使用問題中的程式碼具有相同的效果。根據問題中的“某些舊值可能永遠保留”語句，這兩種方法都沒有真正處理保留現有行，但是對於問題中的程式碼適用的任何情況，以下內容也應該有效。

您還應該考慮從最小值開始標識範圍。從 1 開始會給你超過 21 億個值，所以從最小值（或接近它）開始會給你 42 億個值。因此，將來您不太可能需要這種重新播種操作。

設置

-- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
 ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
 SomethingElse UNIQUEIDENTIFIER NOT NULL,
 SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
 SELECT NEWID(), so1.[name] + N'~' + so2.[name]
 FROM   master.sys.all_columns so1
 CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

刪除並重新創建身份 PK

BEGIN TRY
 BEGIN TRAN;

 ALTER TABLE dbo.IdentityTest
   DROP CONSTRAINT [PK_IdentityTest];

 ALTER TABLE dbo.IdentityTest
   DROP COLUMN [ID];

 ALTER TABLE dbo.IdentityTest
   ADD [ID] INT
   IDENTITY(-2140000000, 1)
   NOT NULL
   CONSTRAINT [PK_IdentityTest]
     PRIMARY KEY;

 COMMIT TRAN;
END TRY
BEGIN CATCH
 IF (@@TRANCOUNT > 0)
 BEGIN
   ROLLBACK TRAN;
 END;

 THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;

引用自：https://dba.stackexchange.com/questions/169180