关于获取SQL Server2000的自增长字段值

过去的几个月里，我有幸面试了众多应聘DBA和DB开发岗位的求职者。我们希望开发人员能够创建存储过程，编写合理的复杂SQL语句，以及触发器。

我喜欢问这些求职者一个问题： “假设我们使用SQL Server2000进行开发。现在我需要传递给存储过程两个变量：firstname和lastname。存储过程负责向名字为TEST的表插入这两个变量，其中TEST表有两个字段，名字就叫firstname和lastname。TEST表的主键是一个自增长类型的字段，名字叫ContactID。问题是我如何获取插入的那条数据的主键值。”

 

 让我们想一下答案是什么。你是否知道如何创建存储过程，获取数值并返回给调用的应用程序？

 

 有人会直接问我－这个问题重要吗？对于我来说，我问这个问题的目的是为了测试求职者是否有求解非常规需求的能力。

 

设想一下标准的订单/订单明细表应用场景－－你是否知道如何不知道订单主键的情况下插入订单的详细信息？

 

当你因为没有使用主键时，可能会带来锁问题。这时，自增长字段加入是常用的一个手段。

 

但是使用@@Identity来获取插入数据的标识，有可能会带来问题，比如在触发器内使用就会发生问题。

 

这并不是一个可以给出唯一答案的问题，但是这个问题可以让我们对处理类似表的问题，来展开讨论。

 

我收到了很多不同的回答，但是绝大多数并非最优。

 

几乎每个人都知道如何插入数据、如何返回值，但是几乎每个人都在获取自增长字段取值上回答得不是很好。 错误回答 #1 - Select max(contactid) from Test. 因为你无法避免别人也同时在插入数据，因此这个回答是错误的。

 

当然，你可以通过提升隔离级别来达到目的，但是这将会大幅降低并发性能，因此不好。

 

 错误回答 #2 - Select top 1 contactid from test order by contactid desc. 错误的原因和回答#1一样。

 

 错误回答 #3 - 通过插入的数据来组合成一个唯一的标识，从而获得自增长字段的值。如果插入的数据确实组合起来是唯一的，能达到目的，但是如果不唯一，怎么办呢？因此这也不是好办法。

 

错误回答 #4 - 这个回答很接近正解了。

 

这些回答者建议使用@@Identity，自然这是可以的 (小心，要知道如何正确使用@@Identity), 但是当我问他们关于@@Identity的相关技术细节时，我收到最多的答案如下： - 对不起，我不是很清楚。

 

- 你应该尽快获取@@Identity的值，因为其它人的对表插入数据，也会改变这个值。

 

 - 是的，获取最后一个identity值，在大多数情况下是可行的，但是如果在TEST表上有触发器，这个触发器会自动向别的表插入数据，如果那个表也同样有一个自增长字段，那么错误就会发生。

 

此时，你获取的@@Identity取值是那个表的identity取值（注意：这个回答正确地描述了@@Identity的行为）。

 

正确答案 - 因为我们使用的是SQL Server 2000，因此使用Scope_Identity() , 如果用的是SQL Server 7，那么只有只用@@Identity，并且以output参数方式传递(return值一般是用来作为错误代码用)。

 

使用@@Identity意味着将来也许会发生错误，例如审核时使用自增长字段。

 

 现在我们来做一系列的试验来验证： create database IdentityTest use identitytest create table TEST (ContactID int not null identity (1, 1), firstname varchar(100) null, lastname varchar(100) null) insert into TEST Default Values select @@Identity 运行后会返回1。

 

如果在此运行，则返回 2。 insert into TEST Default Values select Scope_Identity() 运行后返回 3。 现在我们来设计如何使@@Identity返回错误结果。

 

我们先创建一个包含一个新的自增长字段的表TESTHISTORY，然后在TEST表上加触发器。 create table TESTHISTORY (HistoryID int not null identity (1, 1), ContactID int not null, firstname varchar(100) null, lastname varchar(100) null) create trigger i_TEST on dbo.TEST for insert as set nocount on insert into TESTHISTORY (ContactID, FirstName, LastName) select ContactID, FirstName, LastName from Inserted 现在看看会发生什么： insert into TEST Default Values select @@Identity 返回值为1。注意，此时TEST表最后插入的记录，主键值为4，而TESTHISTORY表作后插入记录的主键值＝1。 insert into TEST Default Values select @@Identity 返回值为5。TEST表最后插入记录的主键值为5，而且TESTHISTORY表的确也插入了第二条记录。现在我们再测试如果同时有别的连接来向插入TEST表插入数据，情况会如何。首先我们用当前的连接，运行： insert into TEST Default Values 此时，TEST插入了第6条记录。然后新建一个连接，并运行相同的SQL语句： insert into TEST Default Values 此时，TEST表插入了第7条记录。然后我们在原先那个连接里，来获取“错误”的结果，值为3。select @@Identity 现在我们用scope_identity()来测试。我们希望得到的结果是6，不是7！ select Scope_Identity() 结果确实如此，证明使用scope_identity()是正确的。我知道，这样测试很麻烦，你也许不会去仔细探究。但是如果你准备使用SQL Server 2000，你就必须知道它是如何工作的。有兴趣用这个问题来考考你的开发人员，可以教他们一些专业的开发技巧，这样也许会使你在将来省却很多本可以避免的麻烦。

SQL Server 2000帮助说明

SCOPE_IDENTITY

返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块——存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。

语法

SCOPE_IDENTITY( )

返回类型

sql_variant

注释

SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 在功能上相似，因为它们都返回插入到 IDENTITY 列中的值。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。有关更多信息，请参见 IDENT_CURRENT。

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

例如，有两个表 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 值。