SQL 基礎知識 -- identify seed overflow

DBCC CHECKIDENT (Transact-SQL)

Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Permissions

Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Resetting the current identity value, if it is needed

The following example resets the current identity value, if it is needed, of the specified table in the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType');  
GO

B. Reporting the current identity value

The following example reports the current identity value in the specified table in the AdventureWorks2012 database, and does not correct the identity value if it is incorrect.

USE AdventureWorks2012;   
GO  
DBCC CHECKIDENT ('Person.AddressType', NORESEED);   
GO

C. Forcing the current identity value to a new value

The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1.

USE AdventureWorks2012; 
GO 
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10); 
GO 

https://msdn.microsoft.com/en-IN/library/ms176057.aspx 

SQL Server 重置Identity標識列的值（INT爆了）

http://www.cnblogs.com/gaizai/archive/2013/04/23/3038318.html

一、背景

　　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();