我应该如何获得插入行的身份?

我知道@@IDENTITY和IDENT_CURRENT和SCOPE_IDENTITY,但不理解它们所附带的含义或影响。

谁能解释一下它们的区别,以及我什么时候使用它们?


当前回答

获取新插入行的标识的最好(也就是最安全的)方法是使用output子句:

create table TableWithIdentity
           ( IdentityColumnName int identity(1, 1) not null primary key,
             ... )

-- type of this table's column must match the type of the
-- identity column of the table you'll be inserting into
declare @IdentityOutput table ( ID int )

insert TableWithIdentity
     ( ... )
output inserted.IdentityColumnName into @IdentityOutput
values
     ( ... )

select @IdentityValue = (select ID from @IdentityOutput)

其他回答

在你的插入语句之后,你需要添加这个。确认插入数据的表名。您将得到当前行,而不是刚才插入语句所影响的行。

IDENT_CURRENT('tableName')

@@IDENTITY是使用当前SQL连接插入的最后一个标识。这是从插入存储过程中返回的一个很好的值,在该存储过程中,您只需要为新记录插入标识,而不关心之后是否添加了更多行。

SCOPE_IDENTITY是使用当前SQL Connection插入的最后一个标识,并且在当前作用域中——也就是说,如果在插入之后根据触发器插入了第二个identity,那么它将不会反映在SCOPE_IDENTITY中,只反映在您执行的插入中。坦率地说,我从来没有理由使用它。

IDENT_CURRENT(tablename)是插入的最后一个标识,无论连接或作用域如何。如果您想获取未插入记录的表的当前IDENTITY值,则可以使用此方法。

我不能与其他版本的SQL Server对话,但在2012年,直接输出工作得很好。您不需要为临时表而烦恼。

INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES (...)

顺便说一下,这种技术也适用于插入多行。

INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES
    (...),
    (...),
    (...)

输出

ID
2
3
4

获取新插入行的标识的最好(也就是最安全的)方法是使用output子句:

create table TableWithIdentity
           ( IdentityColumnName int identity(1, 1) not null primary key,
             ... )

-- type of this table's column must match the type of the
-- identity column of the table you'll be inserting into
declare @IdentityOutput table ( ID int )

insert TableWithIdentity
     ( ... )
output inserted.IdentityColumnName into @IdentityOutput
values
     ( ... )

select @IdentityValue = (select ID from @IdentityOutput)

尽管这是一个较旧的线程,但有一种较新的方法可以做到这一点,它可以避免旧版本SQL Server中IDENTITY列的一些缺陷,比如服务器重新启动后标识值的空白。序列在SQL Server 2016和转发中可用,这是一种较新的方法,使用TSQL创建SEQUENCE对象。这允许您在SQL Server中创建自己的数字序列对象,并控制它如何递增。

这里有一个例子:

CREATE SEQUENCE CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  

然后在TSQL中,您可以执行以下操作来获得下一个序列ID:

SELECT NEXT VALUE FOR CountBy1 AS SequenceID
GO

下面是CREATE SEQUENCE和NEXT VALUE FOR的链接