图片 9

SQL Server 中调解自增字段的当前开首值

前几天在把一个CommuntiyServer的数据库从SQL2000升级到SQL2005的时候,碰到一个怪异的问题,报如下错误:
ViolationofPRIMARYKEYconstraint’PK_cs_Threads’.Cannotinsertduplicatekeyinobject’dbo.cs_Threads’.
分析进去后,竟然发现这个表的自增字段数据库中已经达到了6144,而数据库维护的这个表的初始自增值只到6109。
解决方法很简单,利用以下SQL语句即可搞定: DBCCCHECKIDENT(‘cs_Threads’)
上述语句的意思就是:如果表’cs_Threads’的当前标识值小于列中存储的最大标识值,则使用标识列中的最大值对其进行重置。
CHECKIDENT命令可以有以下几种写法:
1、DBCCCHECKIDENT(‘table_name’,NORESEED)
不重置当前标识值。DBCCCHECKIDENT返回一个报表,它指明当前标识值和应有的标识值。
类似如下的报表:
Checkingidentityinformation:currentidentityvalue’6109′,currentcolumnvalue’6144′.
2、DBCCCHECKIDENT(‘table_name’)或DBCCCHECKIDENT(‘table_name’,RESEED)
如果表的当前标识值小于列中存储的最大标识值,则使用标识列中的最大值对其进行重置。
上述命令执行的时候,也会报类似上面的报表。
3、DBCCCHECKIDENT(‘table_name’,RESEED,new_reseed_value)
当前值设置为new_reseed_value。
如果自创建表后没有将行插入该表,则在执行DBCCCHECKIDENT后插入的第一行将使用new_reseed_value作为标识。否则,下一个插入的行将使用new_reseed_value+1。
如果new_reseed_value的值小于标识列中的最大值,以后引用该表时将产生2627号错误信息。

一、背景

  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创建测试表:

图片 1

--创建测试表
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]

图片 2

(二)
显示插入Id值,插入后表[Test_Identity]的记录如Figure1所示,接着再隐式插入Id值,插入后表[Test_Identity]的记录如Figure2所示。

图片 3

--显示插入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'

图片 4

图片 5

(Figure1:数据记录)

图片 6

(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'

图片 7

(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
    SELECT ‘name4’

图片 8

(Figure4:数据记录)

3)
查看现在的标识值,与上面的进行对比,你就可以理解【当前标识值】与【当前列值】的意义了;

--查询标识值
DBCC CHECKIDENT('Test_Identity', NORESEED)
/*
检查标识信息: 当前标识值'996',当前列值'1002'。
DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。
*/

4) 继续往[Test_Identity]表插入数据,执行3次后表的数据如Figure5所示;

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

图片 9