用法全解,重置Identity标识列的值

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

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

一、背景

Identity Column in SQL Server

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列的值不两次三番。

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值就快爆的题材吗?

If a column is marked as an identity column, then the values for this
column are automatically generated, when you insert a new row into the
table. The following, create table statement marks PersonId as an
identity column with seed = 1 and Identity Increment = 1. Seed and
Increment values are optional. If you don’t specify the identity and
seed they both default to 1. 

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

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

  消除地点的难点有四个章程:3个是修改表结构,把Id的int数据类型修改为bigint;第2个是重置Id(Identity标识列)的值,使它再也增加。

Create Table tblPerson
(
PersonId int Identity(1,1) Primary Key,
Name nvarchar(20)
)
set identity_insert schema_name.table_name on
set identity_insert schema_name.table_name on

  当前标识值:current identity
value,用于记录和保留最后一遍系统一分配配的Id值;下次分红Id正是:当前标识值+标识增量(常常为+1,也得以自行安装);

In the following 2 insert
statements, we only supply values for Name column and not for PersonId
column. 

在transactional replication中,若是订阅端的ID列设置属性:not for replication,那么,当replication
agent执行插入操作,该列的ID值不会增多,因此,ID列的最大值和ID值,不总是保持一致。

在transactional replication中,即便订阅端的ID列设置属性:not for replication,那么,当replication
agent执行插入操作,该列的ID值不会大增,因而,ID列的最大值和ID值,不接二连三保持一如既往。

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

Insert into tblPerson values ('Sam')
Insert into tblPerson values ('Sara')

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

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

 

If you select all the rows from tblPerson table, you will see that,
‘Sam’ and ‘Sara’ rows have got 1 and 2 as PersonId.

一,Identity函数

一,Identity函数

贰 、重置进程

Now, if I try to execute the following query, I get an error stating
– An explicit value for the identity column in table ‘tblPerson’ can
only be specified when a column list is used and IDENTITY_INSERT is
ON. 

1,只可以用来select-into子句中,新建三个包罗Identity 列的数据表

1,只可以用于select-into子句中,新建三个包涵Identity 列的数据表

(一) 上面就测试重置Identity标识列,首先利用下边包车型大巴SQL创制测试表:

Insert into tblPerson values (1,'Todd')
IDENTITY (data_type [ , seed , increment ] ) AS column_name
IDENTITY (data_type [ , seed , increment ] ) AS column_name

997755.com澳门葡京 1

So if you mark a column as an Identity column, you dont have to
explicitly supply a value for that column when you insert a new row. The
value is automatically calculated and provided by SQL server. So, to
insert a row into tblPerson table, just provide value for Name column.

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

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]
Insert into tblPerson values ('Todd')
IDENT_INCR ( 'table_or_view' )
IDENT_SEED ( 'table_or_view' )
IDENT_CURRENT( 'table_or_view' )
IDENT_INCR ( 'table_or_view' )
IDENT_SEED ( 'table_or_view' )
IDENT_CURRENT( 'table_or_view' )

997755.com澳门葡京 2

Delete the row, that you have just inserted and insert another row. You
see that the value for PersonId is 2. Now if you insert another row,
PersonId is 3. A record with PersonId = 1, does not exist, and I want to
fill this gap. To do this, we should be able to explicitly supply the
value for identity column. To explicitly supply a value for identity
column

函数Ident_Current()用于重返钦命Table的当下ID值。

函数Ident_Current()用于重回内定Table的近期ID值。

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

1. First turn on identity insert – SET
Identity_Insert tblPerson ON

二,Identity 属性

二,Identity 属性

997755.com澳门葡京 3

  1. In the insert query specify the column list
        

    Insert into tblPerson(PersonId, Name) values(2, ‘John’)

在成立(Create)或改动(Alter)Table时,为列定义Identity属性,那么该列正是ID列。被属性
Identity 标识的ID列,能够被函数$IDENTITY引用;

在开立(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'

As long as the Identity_Insert is turned on for a table, you need to
explicitly provide the value for that column. If you don’t provide the
value, you get an error – Explicit value must be specified for identity
column in table ‘tblPerson1’ either when 用法全解,重置Identity标识列的值。IDENTITY_INSERT is set to ON
or when a replication user is inserting into a NOT FOR REPLICATION
identity column. 

IDENTITY [ (seed , increment) ]
IDENTITY [ (seed , increment) ]

997755.com澳门葡京 4

After, you have the gaps in the identity column filled, and if you wish
SQL server to calculate the value, turn off Identity_Insert.
SET Identity_Insert tblPerson OFF

三,使用DBCC CheckIdent 查看或修改ID值

三,使用DBCC CheckIdent 查看或修改ID值

997755.com澳门葡京 5

If you have deleted all the rows in a table, and you want to reset the
identity column value, use DBCC CHECKIDENT command. This command will
reset PersonId identity column.
DBCC CHECKIDENT(tblPerson, RESEED, 0)

DBCC 是Database Console Commands  的简写,DBCC CheckIdent
用于查看钦点Table的眼下ID值,并依据须求,修改其ID值。

DBCC 是Database Console Commands  的简写,DBCC CheckIdent
用于查看内定Table的此时此刻ID值,并根据供给,修改其ID值。

(Figure1:数据记录)

How to get the last generated identity column value in SQL Server

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

997755.com澳门葡京 6

There are several ways in sql server, to retrieve the last identity
value that is generated. The most common way is to use SCOPE_IDENTITY()
built in function. 
Apart, from using SCOPE_IDENTITY(), you also have @@IDENTITY and
IDENT_CURRENT(‘TableName’) function. 

1,查看ID列的此时此刻ID值

1,查看ID列的眼下ID值

(Figure2:数据记录)

Example queries for getting
the last generated identity value

dbcc checkident('table name',noreseed)
dbcc checkident('table name',noreseed)

(三) DBCC CHECKIDENT(‘table_name’, NORESEED)不重置当前标识值。DBCC
CHECKIDENT
再次来到2个报表,它指明当前标识值和应有的标识值。执行上面包车型大巴SQL语句,重返的新闻表示:当前标识值’1001’,当前列值’1001’,如Figure2所示。

Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('tblPerson')

回来的音信是:Checking identity
information: current identity value ‘517’, current column value
‘517’.
2,假如ID列的最大值大于ID值,将ID值修改为ID列的最大值

回到的信息是: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 输出了错误信息,请与系统管理员联系。
*/

Let’s now understand the difference between, these 3 approaches.

DBCC CHECKIDENT ( 'table_name' )
--or
DBCC CHECKIDENT ( 'table_name', RESEED )
DBCC CHECKIDENT ( 'table_name' )
--or
DBCC CHECKIDENT ( 'table_name', RESEED )

(四)
再隐式插入Id值,插入后表[Test_Identity]的记录如Figure3所示。所以实行上面包车型大巴SQL语句是不会重置当前标识值的,能够放心执行。

SCOPE_IDENTITY() returns the last identity value that is created in
the same session (Connection) and in the same scope (in the same
Stored procedure, function, trigger). Let’s say, I have 2 tables
tblPerson1 and tblPerson2, and I have a trigger on tblPerson1 table,
which will insert a record into tblPerson2 table. Now, when you insert a
record into tblPerson1 table,  SCOPE_IDENTITY() returns the
idetentity value that is generated in tblPerson1 table, where as
@@IDENTITY returns, the value that is generated in tblPerson2 table. So,
@@IDENTITY returns the last identity value that is created in the same
session without any consideration to the scope.
IDENT_CURRENT(‘tblPerson’) returns the last identity value created for
a specific table across any session and any scope.

3,将ID列的ID值修改钦命的数值

3,将ID列的ID值修改钦点的数值

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

In brief:
SCOPE_IDENTITY() – returns the last identity value that is created in
the same session and in the same scope.
@@IDENTITY – returns the last identity value that is created in the
same session and across any scope.
IDENT_CURRENT(‘TableName’) – returns the last identity value that is
created for a specific table across any session and any scope.

DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )
DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )

997755.com澳门葡京 7

四,查看最后一个插入的ID值

四,查看最终一个安顿的ID值

(Figure3:数据记录)

3个session含有分歧的Scope,3个触发器,一个仓库储存进程,多少个batch,一个动态查询语句都以是2个scope。在3个batch中履行四个存款和储蓄进程,就会生出多个功用域,@@IDENTITY再次回到的ID值是最后二个Scope产生的结果。借使要拿走当前Scope中插入的尾声一个ID值,必要运用SCOPE_IDENTITY()。

3个session含有差异的Scope,2个触发器,2个仓库储存进度,二个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.

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值:

据说Scope的区别,有四个函数用于重回最终四个插入的ID值:

因为地点再次回到结果是:当前标识值’1002’,当前列值’1002’,所以进行上边包车型客车SQL语句是未曾影响的,几时才有影响呢?参考:(当在Figure4状态下执行下面包车型客车SQL命令,结果就会如Figure7所示

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

五,向ID列显式插入内定数值

五,向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列插入显式数值。

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

要明白地点的叙述,可以实行上边包车型地铁测试:

SET IDENTITY_INSERT schema_name . table_name { ON | OFF }  
SET IDENTITY_INSERT schema_name . table_name { ON | OFF }  

1) 重新设置当前值设置为new_reseed_value =
995,执行上面包车型地铁SQL语句再次来到的音信如下所示;

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

假定插入的数值高于当前的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.

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
子句中。

瞩目,在插入显式数值时,必须将Target Table的具备列都显式列出在Insert
子句中。

  • 1 = 996;

    –隐式插入Id值
    INSE奥迪Q5T INTO Test_Identity
    SELECT ‘name4’

 

 

997755.com澳门葡京 8

参照文书档案:

参照文书档案:

(Figure4:数据记录)

SET IDENTITY_INSERT
(Transact-SQL).aspx)

SET IDENTITY_INSERT
(Transact-SQL).aspx)

3)
查看未来的标识值,与地点的进展自己检查自纠,你就足以领略【当前标识值】与【当前列值】的意义了;

DBCC CHECKIDENT
(Transact-SQL).aspx)

DBCC CHECKIDENT
(Transact-SQL).aspx)

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

IDENT_CURRENT
(Transact-SQL)

IDENT_CURRENT
(Transact-SQL)

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

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

997755.com澳门葡京 9

(Figure5:数据记录)

5) 假若今日接二连三往[Test_Identity]表插入数据会爆发怎么着业务呢?将发出
2627 号错误音信,如下边包车型地铁错误音讯;

消息2627,级别14,状态1,第2 行

违反了PRIMARY KEY 约束’PK_testid’。无法在指标’dbo.Test_Identity’
中插入重复键。

讲话已告一段落。

6)
上面来测试成立表后不曾插入行,要是这些时候实施重置标识值会发生什么样工作?清空[Test_Identity]表,再另行设置标识值,重返的新闻如上边所示;

997755.com澳门葡京 10

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

997755.com澳门葡京 11

7)
这一个时候往[Test_Identity]表插入数据,数据仿佛Figure6所示,那表达了:“万一自创始表后从未将行插入该表,则在履行
DBCC CHECKIDENT 后插入的首先行将利用 new_reseed_value 作为标识。

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

997755.com澳门葡京 12

(Figure6:数据记录)

997755.com澳门葡京 13

(Figure7:数据记录)

8)
假使我们删除了IdentityId为1000和1001的记录,那一个时候继续插入数据,会再度生成一千和10001值吗?效果如Figure10所示(重新覆盖了);

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

997755.com澳门葡京 14

(Figure8:数据记录)

--重置标识值
DBCC CHECKIDENT('Test_Identity', RESEED, 996)
--隐式插入Id值
INSERT INTO [Test_Identity](Name)
SELECT 'name6'

997755.com澳门葡京 15

(Figure9:数据记录)

997755.com澳门葡京 16

997755.com澳门葡京,(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)

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website