SQL Server 重置Identity标识列的值（INT爆了）

jacky4955

　　一、背景
　　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] ON
INSERT 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=1000
delete 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)