sql-server - 实际插入的行 ID

Question

我在这条语句中在我的数据库中创建了表

CREATE TABLE tPerson
    (
        id INT NOT NULL PRIMARY KEY identity(1,1)
        , name NVARCHAR(100) not null
        , email NVARCHAR(30) not null
    )
GO

现在我使用 INSERT 插入新值。我的问题是如何获取当前添加行的 ID？有什么想法吗？？

Answer 1

假设是 SQL 服务器，你应该查看 this article更好地理解检索身份。

这是一个片段:

SELECT @@IDENTITY

It returns the last IDENTITY value produced on aconnection, regardless of the table that produced the value, andregardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table inyour current session. While @@IDENTITY is limited to the currentsession, it is not limited to the current scope. If you have a triggeron a table that causes an identity to be created in another table, youwill get the identity that was created last, even if it was thetrigger that created it.

SELECT SCOPE_IDENTITY()

It returns the last IDENTITY value produced ona connection and by a statement in the same scope, regardless of thetable that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, willreturn the last identity value created in the current session, but itwill also limit it to your current scope as well. In other words, itwill return the last identity value that you explicitly created,rather than any identity that was created by a trigger or a userdefined function.

SELECT IDENT_CURRENT(‘tablename’)

It returns the last IDENTITY valueproduced in a table, regardless of the connection that created thevalue, and regardless of the scope of the statement that produced thevalue. IDENT_CURRENT is not limited by scope and session; it islimited to a specified table. IDENT_CURRENT returns the identity valuegenerated for a specific table in any session and any scope.