scope_identity 与 ident_current [英] scope_identity vs ident_current

问题描述

经过大量研究，我对应该在 sql 中使用哪个身份跟踪器感到有些困惑.

After much research I am a little confused by which identity tracker I should use in sql.

据我所知，scope_identity 会给我从任何表更新的最后一个 id，而 ident_current 将返回指定表中的最后一个 id.

From what I understand scope_identity will give me the last id updated from any table and ident_current will will return the last id from a specified table.

因此，鉴于这些信息，在我看来，最好使用的版本(如果您知道要更新哪个表)是 ident_current.然而，在阅读时，似乎大多数人更喜欢使用 scope_identity.这背后的原因是什么，我的逻辑是否有缺陷?

So given that information it would seem to me the best version to use (if you know which table you will be updating) is ident_current. Yet, upon reading it seems most people prefer to use scope_identity. What is the reasoning behind this and is there a flaw in my logic?

推荐答案

这种情况下需要写表名，如果决定改表名怎么办?然后，您也一定不要忘记更新您的代码以反映这一点.我总是使用 SCOPE_IDENTITY，除非我需要触发器中发生的插入中的 ID，然后我将使用 @@IDENTITY

In that case you need to write the table name, what happens if you decide to change the table name? You then also must not forget to update your code to reflect that. I always use SCOPE_IDENTITY unless I need the ID from the insert that happens in a trigger then I will use @@IDENTITY

另外一个更大的区别是 IDENT_CURRENT 会给你来自另一个执行插入的进程的身份(换句话说，任何用户最后生成的身份值)因此，如果您执行插入操作，然后有人在执行 SELECT IDENT_CURRENT 之前执行插入操作，您将获得该其他人的身份值

Also the bigger difference is that IDENT_CURRENT will give you the identity from another process that did the insert (in other words last generated identity value from any user) so if you do an insert and then someone does an insert before you do a SELECT IDENT_CURRENT you will get that other person's identity value

另见 6 种获取当前身份值的不同方法，其中有一些代码解释当你把触发器放在桌子上时会发生什么

See also 6 Different Ways To Get The Current Identity Value which has some code explaining what happens when you put triggers on the table

这篇关于scope_identity 与 ident_current的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！