SQL Server 重設Identity識別欄位的值（INT爆了）(轉載)

標籤：

一、背景

　　SQL Server資料庫中表A中Id欄位的定義是：[Id] [int] IDENTITY(1,1)，隨著資料的不斷增長，Id值已經接近2147483647（int的取值範圍為：-2 147 483 648 到 2 147 483 647）了，雖然已經對舊資料進行歸檔，但是這個表需要保留最近的1億資料，有什麼方法解決Id值就快爆的問題呢？

　　解決上面的問題有兩個辦法：一個是修改表結構，把Id的int資料類型修改為bigint；第二個是重設Id（Identity識別欄位）的值，使它重新增長。

　　當前標識值：current identity value，用於記錄和儲存最後一次系統分配的Id值；下次分配Id就是：當前標識值+識別值增量（通常為+1，也可以自行設定）；

　　當前列值：current column value，這Id值到目前為止的最大值；

 

二、重設過程

(一) 下面就測試重設Identity識別欄位，首先使用下面的SQL建立測試表：

--建立測試表CREATE TABLE [dbo].[Test_Identity](    [IdentityId] [int] IDENTITY(1,1) NOT NULL,    [Name] [nchar](10) NULL, CONSTRAINT [PK_testid] PRIMARY KEY CLUSTERED (    [IdentityId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

(二) 顯示插入Id值，插入後表[Test_Identity]的記錄如Figure1所示，接著再隱式插入Id值，插入後表[Test_Identity]的記錄如Figure2所示。

--顯示插入Id值SET IDENTITY_INSERT [Test_Identity] ONINSERT INTO [Test_Identity](IdentityId,Name)SELECT 1000,‘name1‘SET IDENTITY_INSERT [Test_Identity] OFF--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT ‘name2‘

（Figure1：資料記錄）

（Figure2：資料記錄）

(三) DBCC CHECKIDENT(‘table_name‘, NORESEED)不重設當前標識值。DBCC CHECKIDENT 返回一個報表，它指明當前標識值和應有的標識值。執行下面的SQL語句，返回的資訊表示：當前標識值‘1001‘，當前列值‘1001‘，如 Figure2所示。

--查詢標識值DBCC CHECKIDENT(‘Test_Identity‘, NORESEED)/*檢查標識資訊: 當前標識值‘1001‘，當前列值‘1001‘。DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/

(四) 再隱式插入Id值，插入後表[Test_Identity]的記錄如Figure3所示。所以執行上面的SQL語句是不會重設當前標識值的，可以放心執行。

--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT ‘name3‘

（Figure3：資料記錄）

--查詢標識值DBCC CHECKIDENT(‘Test_Identity‘, NORESEED)/*檢查標識資訊: 當前標識值‘1002‘，當前列值‘1002‘。DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/

(五) DBCC CHECKIDENT (‘table_name‘) 或DBCC CHECKIDENT (‘table_name‘, RESEED) 如果表的當前標識值小於列中儲存的最大標識值，則使用識別欄位中的最大值對其進行重設。

因為上面返回結果是：當前標識值‘1002‘，當前列值‘1002‘，所以執行下面的SQL語句是沒有影響的，什麼時候才有影響呢？參考：（當在Figure4狀態下執行下面的SQL命令，結果就會如Figure7所示）

--重設標識值DBCC CHECKIDENT(‘Test_Identity‘, RESEED)/*檢查標識資訊: 當前標識值‘1002‘，當前列值‘1002‘。DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/

(六) DBCC CHECKIDENT(‘table_name‘, RESEED, new_reseed_value)當前值設定為 new_reseed_value。如果自建立表後沒有將行插入該表，則在執行 DBCC CHECKIDENT 後插入的第一行將使用 new_reseed_value 作為標識。否則，下一個插入的行將使用 new_reseed_value + 1。如果 new_reseed_value 的值小於識別欄位中的最大值，以後引用該表時將產生 2627 號錯誤資訊。

要理解上面的描述，可以進行下面的測試：

1) 重新設定當前值設定為new_reseed_value = 995，執行下面的SQL語句返回的資訊如下所示；

--重設標識值DBCC CHECKIDENT(‘Test_Identity‘, RESEED, 995)/*檢查標識資訊: 當前標識值‘1002‘，當前列值‘995‘。DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/

2) 繼續往[Test_Identity]表插入資料，執行下面的SQL語句插入後的結果如Figure4所示；插入的Id值為new_reseed_value + 1 = 996；

--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT ‘name4‘

（Figure4：資料記錄）

3) 查看現在的標識值，與上面的進行對比，你就可以理解【當前標識值】與【當前列值】的意義了；

--查詢標識值DBCC CHECKIDENT(‘Test_Identity‘, NORESEED)/*檢查標識資訊: 當前標識值‘996‘，當前列值‘1002‘。DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/

4) 繼續往[Test_Identity]表插入資料，執行3次後表的資料如Figure5所示；

--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT ‘name5‘

（Figure5：資料記錄）

5) 如果現在繼續往[Test_Identity]表插入資料會發生什麼事情呢？將產生 2627 號錯誤資訊，如下面的錯誤資訊；

訊息2627，層級14，狀態1，第2 行

違反了PRIMARY KEY 約束‘PK_testid‘。不能在對象‘dbo.Test_Identity‘ 中插入重複鍵。

語句已終止。

6) 下面來測試建立表後沒有插入行，如果這個時候執行重設標識值會發生什麼事情？清空[Test_Identity]表，再重新設定標識值，返回的資訊如下面所示；

--清空表truncate table [Test_Identity]--重設標識值DBCC CHECKIDENT(‘Test_Identity‘, RESEED, 995)/*檢查標識資訊: 當前標識值‘NULL‘，當前列值‘995‘。DBCC 執行完畢。如果DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/

7) 這個時候往[Test_Identity]表插入資料，資料就如Figure6所示，這說明了：“如果自建立表後沒有將行插入該表，則在執行 DBCC CHECKIDENT 後插入的第一行將使用 new_reseed_value 作為標識。”

--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT ‘name5‘

（Figure6：資料記錄）

（Figure7：資料記錄）

8) 假如我們刪除了IdentityId為1000和1001的記錄，這個時候繼續插入資料，會重建1000和10001值嗎？效果如Figure10所示（重新覆蓋了）；

--刪除和delete from [Test_Identity] where IdentityId=1000delete from [Test_Identity] where IdentityId=1001

（Figure8：資料記錄）

--重設標識值DBCC CHECKIDENT(‘Test_Identity‘, RESEED, 996)--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT ‘name6‘

（Figure9：資料記錄）

（Figure10：資料記錄）

(七) 總結：到這裡，我們已經可以解決Id值就快爆的問題了，因為我們舊的資料會定時歸檔，所以不會出現2627錯誤資訊；而另外一個情境是當出現 Figure5的時候，可以執行DBCC CHECKIDENT(‘Test_Identity‘, RESEED)，設定為當前列最大值為標識值，防止出現2627錯誤資訊。

 

三、補充說明

在MySQL中，也有類似Identity的功能：

`IDs` int(11) unsigned NOT NULL AUTO_INCREMENT

在建立表的時候，會有一個選項AUTO_INCREMENT=17422061，直接可以設定起始值，還可以設定步長：

SHOW VARIABLES LIKE ‘auto_inc%‘;

起始值：auto_increment_offset

步長：auto_increment_increment

SET @auto_increment_increment=10;

SELECT LAST_INSERT_ID();

 

四、參考文獻

重設MSSQL的Identity識別欄位的值

DBCC CHECKIDENT (Transact-SQL)

SQLServer中的@@IDENTITY，SCOPE_IDENTITY和IDENT_CURRENT

SCOPE_IDENTITY (Transact-SQL)

 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1、newid長度長，像4樓說的，每個頁面存放的數量就會少，查詢時要訪問的資料頁就多，通俗點說，就是I/O會更高。效能不見得好。
2、3樓說的newid無序，不適合做叢集索引，是正確的。
3、對於資料庫底層操作而言。由於數值型變成16進位時是明確的。而字元型或者其他類型到了底層會存在一些不可預知性。這樣需要額外的資訊來確定資料。所以一般建議使用數值型來做主鍵及索引，補充一下，最快的運算操作是2進位，其實也就是SQLServer的bit類型。但是由於索引需要考慮選擇性，所以不建議使用bit類型來做索引。選擇性不高。
4、newid號稱3000年內不會重複，但是幾乎沒有項目需要這麼舊。int類型足夠存放2億資料。對於大部分項目來說綽綽有餘。
5、你的文章僅僅突出不重複，而不是高效。
6、由於newid的不確定性，在匹配時，比如表關聯，會有一定的內耗。
綜上所述，不建議在非常特殊的情況下使用newid來做主鍵或者索引

SQL Server 重設Identity識別欄位的值（INT爆了）(轉載)