当@@IDENTITY 不返回时，SCOPE_IDENTITY 如何返回 null?

问题描述

执行插入后，我选择SCOPE_IDENTITY 或@@IDENTITY.

After executing an insert, I either select SCOPE_IDENTITY or @@IDENTITY.

SCOPE_IDENTITY 返回 null 但 @@IDENTITY 不返回.

SCOPE_IDENTITY returns null but @@IDENTITY does not.

我不明白这怎么可能.

你能想出发生这种情况的原因吗?

Can you think of a reason why this happens?

推荐答案

以下是 SCOPE_IDENTITY() 为空但 @@IDENTITY 有值的一个示例:

here is one example of how SCOPE_IDENTITY() will be null but @@IDENTITY will have a value:

插入一个没有标识的表，该表有一个插入触发器然后插入到历史表中一个身份.SCOPE_IDENTITY() 将是null(在本地范围内没有身份)，但@@IDENTITY 会报告来自触发器的身份.

仅供参考，SCOPE_IDENTITY() 存在一个已知错误:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

FYI, there is a known bug with SCOPE_IDENTITY(): https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

最好的方法是使用 OUTPUT INTO，它可以捕获一组 ID，并且不受 SCOPE_IDENTITY() 错误的影响:

Your best bet with identities is to use OUTPUT INTO, it can capture a set of IDs and is not subject to the SCOPE_IDENTITY() bug:

declare @x table (tableID int identity not null primary key, datavalue varchar(10))
declare @y table (tableID int, datavalue varchar(10))

INSERT INTO @x values ('aaaa')
INSERT INTO @x values ('bbbb')
INSERT INTO @x values ('cccc')
INSERT INTO @x values ('dddd')
INSERT INTO @x values ('eeee')


INSERT INTO @x
    (datavalue)
    OUTPUT INSERTED.tableID, INSERTED.datavalue     --<<<<OUTPUT INTO SYNTAX
    INTO @y                                         --<<<<OUTPUT INTO SYNTAX
SELECT
    'value='+CONVERT(varchar(5),dt.NewValue)
    FROM (SELECT id as NewValue from sysobjects where id<20) dt
    ORDER BY dt.NewValue


select * from @x
select * from @y

这篇关于当@@IDENTITY 不返回时，SCOPE_IDENTITY 如何返回 null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！