SQL Server中如何获取刚插入的标识值 [转贴]

数据库实际应用中，我们往往需要得到刚刚插入的标志值来往相关表中写入数据。但我们平常得到的真的是我们需要的那个值么？
有时我们会使用 SELECT @@Identity 来获得我们刚刚插入的值，比如下面的代码

代码一：
use tempdb
if exists (select * from sys.objects where object_id = object_id(N'[test1]') and type in (N'u'))
drop table [test1]
go
create table test1
(
id int identity(1,1),
content nvarchar(100)
)
insert into test1 (content) values ('solorez')
select @@identity

乐观情况下，这样做是没问题的，但如果我们如果先运行下面的代码二创建一个触发器、再运行代码三：

代码二：
create table test2
(
id int identity(100,1),
content nvarchar(100)
)

create trigger tri_test1_identitytest_I
on test1 after insert
as
begin
insert into test2
select content from inserted
end

代码三：
insert into test1 (content) values ('solorez2')
select @@identity
 
我们可以看到，此时得到的标识值已经是100多了，很明显，这是表test2的生成的标识值，已经不是我们想要的了。
我们可以看看@@identity的定义：Identity
原来，@@identity返回的是当前事务最后插入的标识值。
这时我们或许会用下面的方法：

代码四：
insert into test1 (content) values ('solorez3')
SELECT IDENT_CURRENT('test1')

看来结果还比较正确，但如果我们在多次运行代码四的同时运行下面的代码五：

代码五：
insert into test1 (content) values ('solorez3')

waitfor delay '00:00:20'
SELECT IDENT_CURRENT('test1')
 
结果又不是我们想要的了！
再看看IDENT_CURRENT(Tablename) 的定义：IDENT_CURRENT(Tablename)
是返回指定表的最后标识值。

到这里，是该亮出答案的时候了，我们可以使用下面的代码：

代码六：
insert into test1 (content) values ('solorez3')
SELECT scope_identity()

这时，我们无论是添加触发器还是运行并行插入，得到的始终是当前事务的标识值。

scope_identity()的定义：scope_identity()
 

 

Table表中有一字段为自增长列(也称标识列，即Identity Increment)，那么如何获取该列的下一行标识值呢，或刚插入的标识值？
 

经过一番探索，下面的代码有效：

--获取刚插入的标识值

SELECT IDENT_CURRENT('TableName') from TableName
 

--获取下一行的标识值

declare @NextIdentity int

SELECT @NextIdentity = IDENT_CURRENT('TableName') + IDENT_INCR('TableName') from TableName

if (@NextIdentity is null)

   set @NextIdentity = IDENT_SEED('TableName')  --如查询表为空，则取标识列起始值

将TableName换成所需计算的表名即可。

 

摘要：本文向您介绍如何在SQL Server中准确的获得标识值，在SQL Server中，有三种不同的函数可以帮助我们达到目的。

SQL Server有三种不同的函数可以用来获得含有标识列的表里最后生成的标识值：

@@IDENTITY   SCOPE_IDENTITY()   IDENT_CURRENT('数据表名') 以上三个函数虽然都可以返回数据库引擎最后生成插入标识列的值，但是根据插入行的来源(例如：存储过程或触发器)以及插入该行的连接不同，这三个函数在功能上也有所不同。

@@IDENTITY函数可以返回所有范围内当前连接插入最后所生成的标识值(包括任何调用的存储过程和触发器)。这个函数不止可以适用于表。函数返回的值是最后表插入行生成的标识值。

SCOPE_IDENTITY()函数跟上一个函数几乎是一摸一样的，不同的地方：即前者返回的值只限于当前范围(即执行中的存储过程)。

最后是IDENT_CURRENT函数，它可以用于所有范围和所有连接，获得最后生成的表标识值。跟前面两个函数不同的是，这个函数只用于表，并且使用[数据表名]作为一个参数。

我们可以举实例来演示上述函数是如何运作的。

首先，我们创建两个简单的例表：一个代表客户表，一个代表审计表。创建审计表的目的是为了跟踪数据库里插入和删除信息的所有记录。

以下是引用片段：

CREATE TABLE dbo.customer  
(customerid INT IDENTITY(1,1) PRIMARY KEY)  
GO  
CREATE TABLE dbo.auditlog  
(auditlogid INT IDENTITY(1,1) PRIMARY KEY,   customerid INT, action CHAR(1),   changedate datetime DEFAULT GETDATE())  
GO
然后，我们还要创建一个存储过程和一个辅助触发器，这个存储过程将在数据库表里插入新的客户行，并返回生成的标识值，而触发器则会向审计表插入行：

以下是引用片段：

CREATE PROCEDURE dbo.p_InsertCustomer
@customerid INT output  
AS  
SET nocount ON  
INSERT INTO dbo.customer DEFAULT VALUES  SELECT @customerid = @@identity  
GO  

CREATE TRIGGER dbo.tr_customer_log ON dbo.customer   FOR INSERT, DELETE  
AS  
IF EXISTS (SELECT 'x' FROM inserted)   INSERT INTO dbo.auditlog (customerid, action)   SELECT customerid, 'I'   FROM inserted ELSE   IF EXISTS (SELECT 'x' FROM deleted)   INSERT INTO dbo.auditlog (customerid, action)   SELECT customerid, 'D'   FROM deleted  
GO
现在我们可以执行程序，创建客户表的第一行了，以下是引用片段：

DECLARE @customerid INT   EXEC dbo.p_InsertCustomer @customerid output   SELECT @customerid AS customerid

执行后返回了我们需要的第一个客户的值，并记录了插入审计表的条目。到目前为止，数据显示没有任何问题。

假设由于先前沟通出现了偏差，一个客户服务代表现在需要从数据库里删除掉这个新增的客户。我们现在就来把新插入的客户行删除掉：

以下是引用片段：

DELETE FROM dbo.customer WHERE customerid = 1 现在，客户工作表为空表，而审计工作表里则有两行——第一行是记录第一次插入行，第二行是记录删除客户记录。

现在我们再往数据库里增加第二个客户信息并检测一下获得的标识值：

以下是引用片段：

DECLARE @customerid INT   EXEC dbo.p_InsertCustomer @customerid output   SELECT @customerid AS customerid

哇!看看出现了什么情况!如果我们现在再看客户工作表，就会发现虽然创建了客户2，但是我们的程序返回的标识值为3!到底出了什么问题呢?回想一下，前面讲过@@IDENTITY函数的作用范围，它会返回主程序调用的任何存储过程或触动任何触发器最后生成的标识值，取决于哪一个在函数被调用前最后生成标识值。在我们的例子里，初始范围是p_InsertCustomer，然后是触发器用来记录插入条目的tr_customer_log。因此我们返回获得的标识值是审计工作表里触发器插入生成的标识值，而不是我们想要的客户工作表里的生成的标识值。

在SQL Server 2000之前的版本，@@IDENTITY函数是获得标识值的唯一方法。由于会出现这样的存储过程/触发器问题，SQL Server开发团队在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT这两个函数来解决这个问题。所以在旧的SQL Server版本里，要解决这个问题比较麻烦。如果是SQL Server6.5版本，我建议可以去掉标识列，然后创建一个可以包含下一个需要使用的值的辅助表，可以达到标识列的作用效果。不过这个办法也不是什么高明的办法。

现在我们来修改一下存储过程来使用SCOPE_IDENTITY()函数，并重新执行程序来添加第三个客户条目：

以下是引用片段：

ALTER PROCEDURE dbo.p_InsertCustomer @customerid INT output  
AS  
SET nocount ON  
INSERT INTO dbo.customer DEFAULT VALUES   SELECT @customerid = SCOPE_IDENTITY()  
GO  
DECLARE @customerid INT   EXEC dbo.p_InsertCustomer @customerid output  
SELECT @customerid AS customerid
我们返回的标识值还是3，不过这次我们获得的标识值是正确的，因为我们添加了第三个客户条目。如果我们检查一下审计工作表，就会发现里面已经有第四个条目记录新插入的客户记录。由于函数SCOPE_IDENTITY()只作用于当前范围，只返回当前执行程序的值，这样就避免了发生刚才那样的问题。

前面讲过，函数@@IDENTITY和函数SCOPE_IDENTITY()不止用于表，不像函数IDENT_CURRENT那样可以用表作为参数。使用@@IDENTITY和SCOPE_IDENTITY()这两个函数的话在设置代码时需要加倍小心，才能够从所需要的表里获得正确的标识值。从表面上来看，放弃这两个函数，只使用函数IDENT_CURRENT并指定表是更安全的办法。这样可以避免出现获得错误标识值的情况，对吧?记得先前说过函数IDENT_CURRENT不仅会跨范围，而且它还会跨连接。也就是说，使用这个函数生成的值不仅仅限于你的连接所执行的程序，它的涵盖范围还包括整个数据库所有的连接。因此，即使是在规模较小的OLTP环境里，它也会出现不能准确返回所需值的问题。这样就可能发生类似前面@@IDENTITY函数/触发器的数据损坏问题。

我的建议是函数SCOPE_IDENTITY()是三个函数里最安全的函数，应该设置为默认函数。使用这个函数，你可以放心地添加触发器和次存储过程，无需担心意外损坏数据。而另外两个函数可以保留应付特殊的情况，当遇到需要使用这两个函数的特殊情况时，建议记录它们的使用情况并进行测试。

小技巧：

Sql Server 判断表是存在标识列

If Exists(Select * from SysColumns Where ID=OBJECT_ID(N'TEST1') And COLUMNPROPERTY(ID,Name,'IsIdentity')=1)
Print N'有自增列'
Else
Print N'没有自增列'

Sql Server 显示当前数据库包含自增列的表
Select b.name,a.* from SysColumns a,sysobjects b Where a.id=b.id and COLUMNPROPERTY(a.ID,a.Name,'IsIdentity')=1

SQL SERVER自增长字段复位方法：

SQLSERVER 复位:

Truncate table Ashare_CJHB
Dbcc checkident (Ashare_CJHB,RESEED,0)

获取最后sql影响记录的最后ID,慎用@@Identity 我们常用的获sql影响的最后记录id常用的是@@identity，但有些情况下会有问题，其实sql一共提供了3个不同的方法

SELECT SCOPE_IDENTITY()

select IDENT_CURRENT(TableName)

select @@IDENTITY

IDENT_CURRENT 类似于 SQL Server 2000 标识函数 SCOPE_IDENTITY 和 @@IDENTITY。这三个函数都返回最后生成的标识值。但是，上述每个函数中定义的“最后”的作用域和会话有所不同。

IDENT_CURRENT 返回为某个会话和用域中的指定表生成的最新标识值。返回为任何会话和任何作用域中的特定表最后生成的标识值。IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。

@@IDENTITY 返回为跨所有作用域的当前会话中的某个表生成的最新标识值。返回为当前会话的所有作用域中的任何表最后生成的标识值。

SCOPE_IDENTITY 返回为当前会话和当前作用域中的某个表生成的最新标识值。返回为当前会话和当前作用域中的任何表最后生成的标识值

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。

使用示例：
set @NewID=SCOPE_IDENTITY()
set @NewID=@@IDENTITY
set @NewID=IDENT_CURRENT('表名')
例如，有两个表 T1 和 T2，在 T1 上定义了一个 INSERT 触发器。当将某行插入 T1 时，触发器被激发，并在 T2 中插入一行。此例说明了两个作用域：一个是在 T1 上的插入，另一个是作为触发器的结果在 T2 上的插入。假设 T1 和 T2 都有 IDENTITY 列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。@@IDENTITY 返回插入到当前会话中任何作用域内的最后一个 IDENTITY 列值，该值是插入 T2 中的值。SCOPE_IDENTITY() 返回插入 T1 中的 IDENTITY 值，该值是发生在相同作用域中的最后一个 INSERT。如果在作用域中发生插入语句到标识列之前唤醒调用 SCOPE_IDENTITY() 函数，则该函数将返回 NULL 值。而IDENT_CURRENT('T1') 和 IDENT_CURRENT('T2') 返回的值分别是这两个表最后自增的值。

ajqc的实验:(40条本地线程,40+40条远程线程同时并发测试,插入1200W行),得出的结论是:

1.在典型的级联应用中.不能用@@IDENTITY,在CII850,256M SD的机器上1W多行时就会并发冲突.在P42.8C,512M DDR上,才6000多行时就并发冲突.

2.SCOPE_IDENTITY()是绝对可靠的,可以用在存储过程中,连触发器也不用建,没并发冲突
=================================

@@IDENTITY与SCOPE_IDENTITY()
在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含语句生成的最后一个标识值。如果语句未影响任何包含标识列的表，则 @@IDENTITY 返回 NULL。如果插入了多个行，生成了多个标识值，则 @@IDENTITY 将返回最后生成的标识值。如果语句触发了一个或多个触发器，该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。如果对包含标识列的表执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表执行了插入操作，则 @@IDENTITY 将返回第一次插入的标识值。出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY 值不会恢复为以前的设置。

如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 是相似的函数，因为他们都返回插入到表的 IDENTITY 列的最后一个值。

@@IDENTITY 和 SCOPE_IDENTITY 可以返回当前会话中的所有表中生成的最后一个标识值。但是，SCOPE_IDENTITY 只在当前作用域内返回值，而 @@IDENTITY 不限于特定的作用域。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 可以返回任何会话和任何作用域中为特定表生成的标识值。

@@IDENTITY 函数的作用域是执行该函数的本地服务器上的当前会话。此函数不能应用于远程或链接服务器。若要获得其他服务器上的标识值，请在远程服务器或链接服务器上执行存储过程，并使（在远程或链接服务器的环境中执行的）该存储过程收集标识值，并将其返回本地服务器上的发出调用的连接。

以下示例向包含标识列 (LocationID) 的表中插入一行，并使用 @@IDENTITY 显示新行中使用的标识值：

USE AdventureWorks;
GO
--Display the value of LocationID in the last row in the table.
SELECT MAX(LocationID) FROM Production.Location;
GO
INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';
GO
--Display the value of LocationID of the newly inserted row.
SELECT MAX(LocationID) FROM Production.Location;
GO


补充：
　　我们要慎用@@IDENTITY，原因是 @@IDENTITY 它总是获取最后一条变更数据的自增字段的值，
而忽略了进行变更操作所在的范围约束。比如，我有表 A 和表 B 两个表，现在我在表 A 上定义了一个Insert触发器，当在表 A 中插入一条数据时，自动在表 B 也插入一条数据。此时，大家注意，有两个原子操作：在A中插入一条数据，接着在B中随后插入一条数据。

　　现在我们想下，假设上面表 A 和表 B 都有IDENTITY自增域，那么我们在表 A 插入一条数据后，使用了 SELECT @@IDENTITY 输出时，输出的到底是 A 还是 B 的自增域的值呢？ 答案很明显，是谁最后插入就输出谁，那么就是 B 了。于是，我本意是想得到 A 的自增域值，结果得到了 B 的自增域值，一只 BUG 随之诞生，搞不好还会影响到整个系统数据的混乱。

　　因此，对于这种情况，建议大家慎用 @@IDENTITY，而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值，但是它是仅限在一个操作范围之内，而不@@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。

==========================

@@IDENTITY SCOPE_IDENTITY（）2008-11-05 15:02


INSERT INTO Calculation_Option (Option_Type, VIP_Option_ID, Option_Code, Option_Description, Last_Updated_Date, Last_Updated_By)
VALUES (@Option_Type, @VIP_Option_ID, @Option_Code, @Option_Description, GetDate(), @Last_Updated_By)

SELECT @Option_ID = SCOPE_IDENTITY()

Calculation_Option这个表里面的option_Id是自动增长的，scope_identity（）是为了得到自动增长后的值

这个地方用的是scope_Identity()

以下引用msdn

例如，有两个表 T1 和 T2，并且在 T1 上定义了 INSERT 触发器。当将某行插入 T1 时，触发器被激发，并在 T2 中插入一行。该方案演示了两个作用域：在 T1 上的插入，以及在 T2 通过触发器的插入。

假设 T1 和 T2 都有标识列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。@@IDENTITY 将返回在当前会话中的任何作用域内插入的最后一个标识列的值。这是在 T2 中插入的值。SCOPE_IDENTITY() 将返回在 T1 中插入的 IDENTITY 值。这是在同一个作用域内发生的最后的插入。如果在任何 INSERT 语句作用于作用域中的标识列之前调用 SCOPE_IDENTITY() 函数，则该函数将返回空值。



=====================

CREATE TABLE #T(ID INT IDENTITY(1,1) PRIMARY KEY,NAME VARCHAR(20))
INSERT INTO #T SELECT 'A'
SELECT @@IDENTITY AS '@@IDENTITY',
SCOPE_IDENTITY() AS 'SCOPE_IDENTITY',
IDENT_CURRENT('#T') AS 'IDENT_CURRENT'

在空表中调用 IDENT_CURRENT 函数时，此函数将返回 NULL。

如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

@@IDENTITY 表示插入后新的表示种子值，例子如下：
假如已经存在表job，向表插入一条数据，
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'//获得表识值