SQLServer中如何取得刚插⼊的标识值
数据库实际应⽤中，我们往往需要得到刚刚插⼊的标志值来往相关表中写⼊数据。但我们平常得到的真的是我们需要的那个值么？有时我们会使⽤ 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 _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'有⾃增列'
Elsesql触发器的使用
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 返回为某个会话和⽤域中的指定表⽣成的最新标识值。
@@IDENTITY 返回为跨所有作⽤域的当前会话中的某个表⽣成的最新标识值。
SCOPE_IDENTITY 返回为当前会话和当前作⽤域中的某个表⽣成的最新标识值。
在空表中调⽤ 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'//获得表识值
发表评论