图片 9

SQL Server 重新恢复设置Identity标志列的值(INT爆了(转)

Identity是标志值,在SQL Server中,有ID列,ID属性,ID值,ID列的值等术语。

一、背景

Identity属性是指在开创Table时,为列钦命的Identity属性,其语法是:column_name type IDENTITY [
(seed , increment)
]
,Identity属性有多少个参数:seed和increment,seed是ID值的起来值,increment是ID值的增量。在Table中创设的Column,若是利用Identity属性标志,那么该列是ID列。默许情状下,不可能显式向ID列插入数值。ID列是由系统自动赋值的,在赋值时,系统基于该表的ID值,自动插入依次增加的,唯意气风发的数值,同有的时候间ID值根据Increment自动依次增加。ID值有机动依次增加的特征,当语句实践停业或业务回滚时,ID值不会回滚,那会产生ID列的值不总是。

  SQL Server数据库中表A中Id字段的定义是:[Id] [int]
IDENTITY(1,1),随着数据的穿梭加强,Id值已经周围2147483647(int的取值范围为:-2
147 483 648 到 2 147 483
647)了,固然已经对旧数据举办归档,不过那几个表须要保留这段时间的1亿数量,有哪些方法消除Id值就快爆的标题啊?

假如想要显式向ID列插入特定的数值,那么,必需启用
Identity_Insert选项,该采取自动将ID值更新为ID列的最大值。

  消逝地点的难题有三个形式:二个是校正表结构,把Id的int数据类型改良为bigint;第2个是重新载入参数Id(Identity标记列)的值,使它再一次增长。

set identity_insert schema_name.table_name on

  当前标识值:current identity
value,用于记录和保存最终一回系统一分配配的Id值;后一次分红Id正是:当前标记值+标记增量(平日为+1,也能够自动安装);

在transactional replication中,要是订阅端的ID列设置属性:not for replication,那么,当replication
agent实行插入操作,该列的ID值不会扩张,由此,ID列的最大值和ID值,不总是保持同风姿罗曼蒂克。

  当前列值:current column value,这Id值到方今结束的最大值;

If this property is specified for the
IDENTITY property, values are not incremented in identity columns when
replication agents perform inserts.

 

一,Identity函数

二、重新载入参数进程

1,只可以用来select-into子句中,新建多个饱含Identity 列的数据表

(生龙活虎) 上面就测量检验复位Identity标志列,首先应用上面包车型大巴SQL创立测验表:

IDENTITY (data_type [ , seed , increment ] ) AS column_name

图片 1

2,查看ID列的Seed,Increment和当前ID值

--创建测试表
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]
IDENT_INCR ( 'table_or_view' )
IDENT_SEED ( 'table_or_view' )
IDENT_CURRENT( 'table_or_view' )

图片 2

函数Ident_Current()用于重返钦点Table的近来ID值。

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

二,Identity 属性

图片 3

在创设(Create)或涂改(Alter)Table时,为列定义Identity属性,那么该列就是ID列。被属性
Identity 标志的ID列,能够被函数$IDENTITY引用;

--显示插入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'
IDENTITY [ (seed , increment) ]

图片 4

三,使用DBCC CheckIdent 查看或校正ID值

图片 5

DBCC 是Database Console Commands  的简写,DBCC CheckIdent
用于查看内定Table的此时此刻ID值,并基于必要,校订其ID值。

(Figure1:数据记录)

DBCC CHECKIDENT( table_name [, { NORESEED | { RESEED [, new_reseed_value ] } } ])
[ WITH NO_INFOMSGS ]

图片 6

1,查看ID列的当下ID值

(Figure2:数据记录)

dbcc checkident('table name',noreseed)

(三) DBCC CHECKIDENT(‘table_name’, NORESEED)不复位当前标志值。DBCC
CHECKIDENT
重回多少个表格,它指明当前标记值和应有的标记值。实施上边包车型大巴SQL语句,重回的音信表示:当前标记值’1001’,当前列值’1001’,如Figure2所示。

再次回到的新闻是:Checking identity
information: current identity value ‘517’, current column value
‘517’.
2,如若ID列的最大值大于ID值,将ID值修正为ID列的最大值

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

(四)
再隐式插入Id值,插入后表[Test_Identity]的记录如Figure3所示。所以进行上边的SQL语句是不会复位当前标记值的,能够放心推行。

3,将ID列的ID值改过钦定的数值

--隐式插入Id值
INSERT INTO [Test_Identity](Name)
SELECT 'name3'
DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )

图片 7

四,查看最终二个布置的ID值

(Figure3:数据记录)

叁个session含有分歧的Scope,一个触发器,三个囤积进度,一个batch,一个动态查询语句都是是三个scope。在二个batch中进行四个存款和储蓄进度,就会生出两个效率域,@@IDENTITY重回的ID值是最后一个Scope爆发的结果。假若要拿走当前Scope中插入的终极三个ID值,须要利用SCOPE_IDENTITY()。

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

MSDN:A scope is a
module: a stored procedure, trigger, function, or batch. Therefore, two
statements are in the same scope if they are in the same stored
procedure, function, or batch.

(五) DBCC CHECKIDENT (‘table_name’) 或DBCC CHECKIDENT (‘table_name’,
RESEED)
假使表的一时一刻标记值小于列中积存的最大标志值,则利用标志列中的最大值对其进展复位。

依照Scope的两样,有七个函数用于再次来到最终三个安插的ID值:

因为地点再次回到结果是:当前标志值’1002’,当前列值’1002’,所以举行下边的SQL语句是未有影响的,曾几何时才有影响呢?参照他事他说加以考查:(当在Figure4状态下推行上面包车型大巴SQL命令,结果就能如Figure7所示

  • @@IDENTITY:成效域是在当下Session中,再次来到最终三个插入的ID值
  • SCOPE_IDENTITY():作用域是在当时此刻的Scope中,重返最终一个布置的ID值
--重置标识值
DBCC CHECKIDENT('Test_Identity', RESEED)
/*
检查标识信息: 当前标识值'1002',当前列值'1002'。
DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。
*/

五,向ID列显式插入钦点数值

(六) DBCC CHECKIDENT(‘table_name’, RESEED,
new_reseed_value)当前值设置为
new_reseed_value。假诺自创始表后并未将行插入该表,则在举办 DBCC
CHECKIDENT 后插入的首先行将运用 new_reseed_value
作为标记。不然,下一个插入的将在使用 new_reseed_value + 1。如果
new_reseed_value 的值稍低于标志列中的最大值,未来引用该表时将发出 2627
号错误消息。

将Identity_Insert选项设置为ON,允许向ID列插入显式数值。

要理解地点的描述,能够进行下边包车型地铁测量检验:

SET IDENTITY_INSERT schema_name . table_name { ON | OFF }  

1) 重新安装当前值设置为new_reseed_value =
995,实践上边包车型客车SQL语句重回的新闻如下所示;

要是插入的数值高于当前的ID值,SQL
Server自动将近来的ID值设置为ID列的最大值。

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

If the value inserted is larger than the
current identity value for the table, SQL Server automatically uses the
new inserted value as the current identity value.

2)
继续往[Test_Identity]表插入数据,施行上边的SQL语句插入后的结果如Figure4所示;插入的Id值为new_reseed_value

在乎,在插入显式数值时,必需将Target Table的兼具列都显式列出在Insert
子句中。

  • 1 = 996;

    –隐式插入Id值
    INSERT INTO Test_Identity
    SELECT ‘name4’

 

图片 8

参照文书档案:

(Figure4:数据记录)

SET IDENTITY_INSERT
(Transact-SQL).aspx)

3)
查看今后的标志值,与地方的扩充对照,你即可通晓【当前标记值】与【当前列值】的意义了;

DBCC CHECKIDENT
(Transact-SQL).aspx)

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

IDENT_CURRENT
(Transact-SQL)

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

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

图片 9