当前位置: 澳门新濠3559 > 数据库 > 正文

ID列的值等术语,Id值已经接近2147483647(int的取值

时间:2019-10-12 05:04来源:数据库
一个session含有不同的Scope,一个触发器,一个存储过程,一个batch,一个动态查询语句都是是一个scope。在一个batch中执行多个存储过程,就会产生多个作用域,@@IDENTITY返回的ID值是最后

一个session含有不同的Scope,一个触发器,一个存储过程,一个batch,一个动态查询语句都是是一个scope。在一个batch中执行多个存储过程,就会产生多个作用域,@@IDENTITY返回的ID值是最后一个Scope产生的结果。如果要获取当前Scope中插入的最后一个ID值,需要使用SCOPE_IDENTITY()。

重置MSSQL的Identity标识列的值

Insert into tblPerson values ('Todd')
dbcc checkident('table name',noreseed)

澳门新濠3559 1

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. 

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.

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

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.

返回的消息是:Checking identity information: current identity value '517', current column value '517'.
2,如果ID列的最大值大于ID值,将ID值修改为ID列的最大值

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

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.

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

二、重置过程

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

DBCC 是Database Console Commands  的简写,DBCC CheckIdent 用于查看指定Table的当前ID值,并根据需要,修改其ID值。

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

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

IDENT_ID列的值等术语,Id值已经接近2147483647(int的取值范围为。CURRENT (Transact-SQL)

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

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

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

在MySQL中,也有类似Identity的功能:

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

注意,在插入显式数值时,必须将Target Table的所有列都显式列出在Insert 子句中。

(Figure1:数据记录)

Insert into tblPerson values (1,'Todd')

根据Scope的不同,有两个函数用于返回最后一个插入的ID值:

澳门新濠3559 2

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)

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

澳门新濠3559 3

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. 

函数Ident_Current()用于返回指定Table的当前ID值。

要理解上面的描述,可以进行下面的测试:

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

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

SHOW VARIABLES LIKE 'auto_inc%';

Insert into tblPerson values ('Sam')
Insert into tblPerson values ('Sara')
DBCC CHECKIDENT( table_name [, { NORESEED | { RESEED [, new_reseed_value ] } } ])
[ WITH NO_INFOMSGS ]

`IDs` int(11) unsigned NOT NULL AUTO_INCREMENT

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.

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

起始值:auto_increment_澳门新濠3559,offset

Example queries for getting the last generated identity value

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

(Figure9:数据记录)

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_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. 

SET IDENTITY_INSERT (Transact-SQL).aspx)

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

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. 

参考文档:

  当前标识值:current identity value,用于记录和保存最后一次系统分配的Id值;下次分配Id就是:当前标识值 标识增量(通常为 1,也可以自行设置);

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

  • @@IDENTITY:作用域是在当前Session中,返回最后一个插入的ID值
  • SCOPE_IDENTITY():作用域是在当前的Scope中,返回最后一个插入的ID值

一、背景

Create Table tblPerson
(
PersonId int Identity(1,1) Primary Key,
Name nvarchar(20)
)
SET IDENTITY_INSERT schema_name . table_name { ON | OFF }  

因为上面返回结果是:当前标识值'1002',当前列值'1002',所以执行下面的SQL语句是没有影响的,什么时候才有影响呢?参考:(当在Figure4状态下执行下面的SQL命令,结果就会如Figure7所示

  1. In the insert query specify the column list     

    Insert into tblPerson(PersonId, Name) values(2, 'John')

IDENTITY [ (seed , increment) ]

澳门新濠3559 4

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

二,Identity 属性

  解决上面的问题有两个办法:一个是修改表结构,把Id的int数据类型修改为bigint;第二个是重置Id(Identity标识列)的值,使它重新增长。

Identity Column in SQL Server

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

三、补充说明

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

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

SQLServer中的@@IDENTITY,SCOPE_IDENTITY和IDENT_CURRENT

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

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

3,将ID列的ID值修改指定的数值

步长:auto_increment_increment

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

(七) 总结:到这里,我们已经可以解决Id值就快爆的问题了,因为我们旧的数据会定时归档,所以不会出现2627错误信息;而另外一个场景是当出现Figure5的时候,可以执行DBCC CHECKIDENT('Test_Identity', RESEED),设置为当前列最大值为标识值,防止出现2627错误信息。

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列的值不连续。

5) 如果现在继续往[Test_Identity]表插入数据会发生什么事情呢?将产生 2627 号错误信息,如下面的错误信息;

 

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

  当前列值:current column value,这Id值到目前为止的最大值;

1,查看ID列的当前ID值

澳门新濠3559 5

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

澳门新濠3559 6

DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )

SELECT LAST_INSERT_ID();

set identity_insert schema_name.table_name on

(Figure2:数据记录)

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

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

一,Identity函数

(一) 下面就测试重置Identity标识列,首先使用下面的SQL创建测试表:

DBCC CHECKIDENT (Transact-SQL).aspx)

违反了PRIMARY KEY 约束'PK_testid'。不能在对象'dbo.Test_Identity' 中插入重复键。

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.

澳门新濠3559 7

1,只能用于select-into子句中,新建一个含有Identity 列的数据表

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

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

(Figure6:数据记录)

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

(Figure8:数据记录)

 

澳门新濠3559 8

(三) DBCC CHECKIDENT('table_name', NORESEED)不重置当前标识值。DBCC CHECKIDENT 返回一个报表,它指明当前标识值和应有的标识值。执行下面的SQL语句,返回的信息表示:当前标识值'1001',当前列值'1001',如Figure2所示。

(Figure5:数据记录)

6) 下面来测试创建表后没有插入行,如果这个时候执行重置标识值会发生什么事情?清空[Test_Identity]表,再重新设置标识值,返回的信息如下面所示;

(Figure4:数据记录)

澳门新濠3559 9

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

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

    --隐式插入Id值 INSERT INTO Test_Identity SELECT 'name4'

澳门新濠3559 10

(Figure3:数据记录)

 

  SQL Server数据库中表A中Id字段的定义是:[Id] [int] IDENTITY(1,1),随着数据的不断增长,Id值已经接近2147483647(int的取值范围为:-2 147 483 648 到 2 147 483 647)了,虽然已经对旧数据进行归档,但是这个表需要保留最近的1亿数据,有什么方法解决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]

7) 这个时候往[Test_Identity]表插入数据,数据就如Figure6所示,这说明了:“如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插入的第一行将使用 new_reseed_value 作为标识。

在创建表的时候,会有一个选项AUTO_INCREMENT=17422061,直接可以设置起始值,还可以设置步长:

(Figure10:数据记录)

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

澳门新濠3559 11

澳门新濠3559 12

SET @auto_increment_increment=10;

(Figure7:数据记录)

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

澳门新濠3559 13

澳门新濠3559 14

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

DBCC CHECKIDENT (Transact-SQL)

(五) DBCC CHECKIDENT ('table_name') 或DBCC CHECKIDENT ('table_name', RESEED) 如果表的当前标识值小于列中存储的最大标识值,则使用标识列中的最大值对其进行重置。

语句已终止。

四、参考文献

--重置标识值
DBCC CHECKIDENT('Test_Identity', RESEED, 995)
/*
检查标识信息: 当前标识值'1002',当前列值'995'。
DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。
*/
--查询标识值
DBCC CHECKIDENT('Test_Identity', NORESEED)
/*
检查标识信息: 当前标识值'996',当前列值'1002'。
DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。
*/
--删除和
delete from [Test_Identity] where IdentityId=1000
delete from [Test_Identity] where IdentityId=1001

 

SCOPE_IDENTITY (Transact-SQL)

(六) DBCC CHECKIDENT('table_name', RESEED, new_reseed_value)当前值设置为 new_reseed_value。如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插入的第一行将使用 new_reseed_value 作为标识。否则,下一个插入的行将使用 new_reseed_value 1。如果 new_reseed_value 的值小于标识列中的最大值,以后引用该表时将产生 2627 号错误信息。

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

澳门新濠3559 15

1) 重新设置当前值设置为new_reseed_value = 995,执行下面的SQL语句返回的信息如下所示;

澳门新濠3559 16

编辑:数据库 本文来源:ID列的值等术语,Id值已经接近2147483647(int的取值

关键词: 澳门新濠3559