SQL 基礎(chǔ)知識(shí) -- 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標(biāo)識(shí)列的值（INT爆了）

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

一、背景

　　SQL Server數(shù)據(jù)庫中表A中Id字段的定義是：[Id] [int] IDENTITY(1,1)，隨著數(shù)據(jù)的不斷增長，Id值已經(jīng)接近2147483647（int的取值范圍為：-2 147 483 648 到 2 147 483 647）了，雖然已經(jīng)對(duì)舊數(shù)據(jù)進(jìn)行歸檔，但是這個(gè)表需要保留最近的1億數(shù)據(jù)，有什么方法解決Id值就快爆的問題呢？

　　解決上面的問題有兩個(gè)辦法：一個(gè)是修改表結(jié)構(gòu)，把Id的int數(shù)據(jù)類型修改為bigint；第二個(gè)是重置Id（Identity標(biāo)識(shí)列）的值，使它重新增長。

　　當(dāng)前標(biāo)識(shí)值：current identity value，用于記錄和保存最后一次系統(tǒng)分配的Id值；下次分配Id就是：當(dāng)前標(biāo)識(shí)值+標(biāo)識(shí)增量（通常為+1，也可以自行設(shè)置）；

　　當(dāng)前列值：current column value，這Id值到目前為止的最大值；

二、重置過程

(一) 下面就測(cè)試重置Identity標(biāo)識(shí)列，首先使用下面的SQL創(chuàng)建測(cè)試表：

--創(chuàng)建測(cè)試表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：數(shù)據(jù)記錄）

（Figure2：數(shù)據(jù)記錄）

(三) DBCC CHECKIDENT('table_name', NORESEED)不重置當(dāng)前標(biāo)識(shí)值。DBCC CHECKIDENT 返回一個(gè)報(bào)表，它指明當(dāng)前標(biāo)識(shí)值和應(yīng)有的標(biāo)識(shí)值。執(zhí)行下面的SQL語句，返回的信息表示：當(dāng)前標(biāo)識(shí)值'1001'，當(dāng)前列值'1001'，如Figure2所示。

--查詢標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', NORESEED)/*檢查標(biāo)識(shí)信息: 當(dāng)前標(biāo)識(shí)值'1001'，當(dāng)前列值'1001'。
DBCC 執(zhí)行完畢。如果DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。*/

(四) 再隱式插入Id值，插入后表[Test_Identity]的記錄如Figure3所示。所以執(zhí)行上面的SQL語句是不會(huì)重置當(dāng)前標(biāo)識(shí)值的，可以放心執(zhí)行。

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

（Figure3：數(shù)據(jù)記錄）

--查詢標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', NORESEED)/*檢查標(biāo)識(shí)信息: 當(dāng)前標(biāo)識(shí)值'1002'，當(dāng)前列值'1002'。
DBCC 執(zhí)行完畢。如果DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。*/

(五) DBCC CHECKIDENT ('table_name') 或DBCC CHECKIDENT ('table_name', RESEED) 如果表的當(dāng)前標(biāo)識(shí)值小于列中存儲(chǔ)的最大標(biāo)識(shí)值，則使用標(biāo)識(shí)列中的最大值對(duì)其進(jìn)行重置。

因?yàn)樯厦娣祷亟Y(jié)果是：當(dāng)前標(biāo)識(shí)值'1002'，當(dāng)前列值'1002'，所以執(zhí)行下面的SQL語句是沒有影響的，什么時(shí)候才有影響呢？參考：（當(dāng)在Figure4狀態(tài)下執(zhí)行下面的SQL命令，結(jié)果就會(huì)如Figure7所示）

--重置標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', RESEED)/*檢查標(biāo)識(shí)信息: 當(dāng)前標(biāo)識(shí)值'1002'，當(dāng)前列值'1002'。
DBCC 執(zhí)行完畢。如果DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。*/

(六) DBCC CHECKIDENT('table_name', RESEED, new_reseed_value)當(dāng)前值設(shè)置為 new_reseed_value。如果自創(chuàng)建表后沒有將行插入該表，則在執(zhí)行 DBCC CHECKIDENT 后插入的第一行將使用 new_reseed_value 作為標(biāo)識(shí)。否則，下一個(gè)插入的行將使用 new_reseed_value + 1。如果 new_reseed_value 的值小于標(biāo)識(shí)列中的最大值，以后引用該表時(shí)將產(chǎn)生 2627 號(hào)錯(cuò)誤信息。

要理解上面的描述，可以進(jìn)行下面的測(cè)試：

1) 重新設(shè)置當(dāng)前值設(shè)置為new_reseed_value = 995，執(zhí)行下面的SQL語句返回的信息如下所示；

--重置標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', RESEED, 995)/*檢查標(biāo)識(shí)信息: 當(dāng)前標(biāo)識(shí)值'1002'，當(dāng)前列值'995'。
DBCC 執(zhí)行完畢。如果DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。*/

2) 繼續(xù)往[Test_Identity]表插入數(shù)據(jù)，執(zhí)行下面的SQL語句插入后的結(jié)果如Figure4所示；插入的Id值為new_reseed_value + 1 = 996；

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

（Figure4：數(shù)據(jù)記錄）

3) 查看現(xiàn)在的標(biāo)識(shí)值，與上面的進(jìn)行對(duì)比，你就可以理解【當(dāng)前標(biāo)識(shí)值】與【當(dāng)前列值】的意義了；

--查詢標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', NORESEED)/*檢查標(biāo)識(shí)信息: 當(dāng)前標(biāo)識(shí)值'996'，當(dāng)前列值'1002'。
DBCC 執(zhí)行完畢。如果DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。*/

4) 繼續(xù)往[Test_Identity]表插入數(shù)據(jù)，執(zhí)行3次后表的數(shù)據(jù)如Figure5所示；

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

（Figure5：數(shù)據(jù)記錄）

5) 如果現(xiàn)在繼續(xù)往[Test_Identity]表插入數(shù)據(jù)會(huì)發(fā)生什么事情呢？將產(chǎn)生 2627 號(hào)錯(cuò)誤信息，如下面的錯(cuò)誤信息；

消息2627，級(jí)別14，狀態(tài)1，第2 行

違反了PRIMARY KEY 約束'PK_testid'。不能在對(duì)象'dbo.Test_Identity' 中插入重復(fù)鍵。

語句已終止。

6) 下面來測(cè)試創(chuàng)建表后沒有插入行，如果這個(gè)時(shí)候執(zhí)行重置標(biāo)識(shí)值會(huì)發(fā)生什么事情？清空[Test_Identity]表，再重新設(shè)置標(biāo)識(shí)值，返回的信息如下面所示；

--清空表truncate table [Test_Identity]--重置標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', RESEED, 995)/*檢查標(biāo)識(shí)信息: 當(dāng)前標(biāo)識(shí)值'NULL'，當(dāng)前列值'995'。
DBCC 執(zhí)行完畢。如果DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。*/

7) 這個(gè)時(shí)候往[Test_Identity]表插入數(shù)據(jù)，數(shù)據(jù)就如Figure6所示，這說明了：“如果自創(chuàng)建表后沒有將行插入該表，則在執(zhí)行 DBCC CHECKIDENT 后插入的第一行將使用 new_reseed_value 作為標(biāo)識(shí)。”

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

（Figure6：數(shù)據(jù)記錄）

（Figure7：數(shù)據(jù)記錄）

8) 假如我們刪除了IdentityId為1000和1001的記錄，這個(gè)時(shí)候繼續(xù)插入數(shù)據(jù)，會(huì)重新生成1000和10001值嗎？效果如Figure10所示（重新覆蓋了）；

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

（Figure8：數(shù)據(jù)記錄）

--重置標(biāo)識(shí)值DBCC CHECKIDENT('Test_Identity', RESEED, 996)--隱式插入Id值INSERT INTO [Test_Identity](Name)SELECT 'name6'

（Figure9：數(shù)據(jù)記錄）

（Figure10：數(shù)據(jù)記錄）

(七) 總結(jié)：到這里，我們已經(jīng)可以解決Id值就快爆的問題了，因?yàn)槲覀兣f的數(shù)據(jù)會(huì)定時(shí)歸檔，所以不會(huì)出現(xiàn)2627錯(cuò)誤信息；而另外一個(gè)場景是當(dāng)出現(xiàn)Figure5的時(shí)候，可以執(zhí)行DBCC CHECKIDENT('Test_Identity', RESEED)，設(shè)置為當(dāng)前列最大值為標(biāo)識(shí)值，防止出現(xiàn)2627錯(cuò)誤信息。

三、補(bǔ)充說明

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

`IDs` int(11) unsigned NOT NULL AUTO_INCREMENT

在創(chuàng)建表的時(shí)候，會(huì)有一個(gè)選項(xiàng)AUTO_INCREMENT=17422061，直接可以設(shè)置起始值，還可以設(shè)置步長：

SHOW VARIABLES LIKE 'auto_inc%';

起始值：auto_increment_offset

步長：auto_increment_increment

SET @auto_increment_increment=10;

SELECT LAST_INSERT_ID();