Editare

Partajați prin

@@IDENTITY (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

@@IDENTITY is a system function that returns the last-inserted identity value.

Transact-SQL syntax conventions

Syntax

@@IDENTITY

Return types

numeric(38,0)

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value that the statement generated.

  • If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
  • If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
  • If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers.
  • If a trigger fires after an insert action on a table that has an identity column, and the trigger inserts into another table that doesn't have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value doesn't revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table isn't committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

  • @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY isn't limited to a specific scope.

  • IDENT_CURRENT isn't limited by scope and session; it's limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

The scope of the @@IDENTITY function is the current session on the local server on which it runs. This function can't be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which runs in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

Replication can affect the @@IDENTITY value, because it's used within the replication triggers and stored procedures. @@IDENTITY isn't a reliable indicator of the most recent user-created identity if the column is part of a replication article. You can use the SCOPE_IDENTITY() function syntax instead of @@IDENTITY. For more information, see SCOPE_IDENTITY (Transact-SQL).

Note

The calling stored procedure or Transact-SQL statement must be rewritten to use the SCOPE_IDENTITY() function, which returns the latest identity used within the scope of that user statement, and not the identity within the scope of the nested trigger used by replication.

Examples

A. Retrieve the last-inserted identity value

The following example inserts a row into a table with an identity column (LocationID) and uses @@IDENTITY to display the identity value used in the new row.

USE AdventureWorks2022;
GO

--Display the value of LocationID in the last row in the table.
SELECT MAX(LocationID) FROM Production.Location;
GO

INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO

SELECT @@IDENTITY AS 'Identity';
GO

--Display the value of LocationID of the newly inserted row.
SELECT MAX(LocationID) FROM Production.Location;
GO

B. Insert parent and child rows using @@IDENTITY

The following example demonstrates using @@IDENTITY to capture a parent row's identity value and use it when you insert related child rows. This pattern is common in order-entry and parent-child table designs.

-- Create sample tables
CREATE TABLE dbo.Orders (
    OrderID int IDENTITY(1, 1) PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL,
    OrderDate datetime NOT NULL DEFAULT GETDATE()
);

CREATE TABLE dbo.OrderDetails (
    DetailID int IDENTITY(1, 1) PRIMARY KEY,
    OrderID int NOT NULL REFERENCES dbo.Orders(OrderID),
    ProductName nvarchar(100) NOT NULL,
    Quantity int NOT NULL
);
GO

-- Insert a parent row and capture its identity
INSERT INTO dbo.Orders (CustomerName, OrderDate)
VALUES ('Contoso Ltd', GETDATE());

DECLARE @NewOrderID int = @@IDENTITY;

-- Insert child rows using the captured parent identity
INSERT INTO dbo.OrderDetails (OrderID, ProductName, Quantity)
VALUES (@NewOrderID, 'Widget A', 10);

INSERT INTO dbo.OrderDetails (OrderID, ProductName, Quantity)
VALUES (@NewOrderID, 'Widget B', 5);

-- Verify the results
SELECT o.OrderID, o.CustomerName, d.ProductName, d.Quantity
FROM dbo.Orders o
INNER JOIN dbo.OrderDetails d ON o.OrderID = d.OrderID
WHERE o.OrderID = @NewOrderID;
GO

Note

In production code, use SCOPE_IDENTITY() instead of @@IDENTITY for this pattern. If a trigger fires on the Orders table and performs an insert into another table with an identity column, @@IDENTITY returns the trigger's identity value instead of the Orders identity value. SCOPE_IDENTITY() returns only the identity value from the current scope.

C. Understand the difference between @@IDENTITY and SCOPE_IDENTITY

The following example shows how @@IDENTITY and SCOPE_IDENTITY() can return different values when triggers are involved.

CREATE TABLE dbo.Products (
    ProductID int IDENTITY(1, 1) PRIMARY KEY,
    ProductName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.ProductAudit (
    AuditID int IDENTITY(1000, 1) PRIMARY KEY,
    ProductID int NOT NULL,
    AuditAction nvarchar(50) NOT NULL,
    AuditDate datetime NOT NULL DEFAULT GETDATE()
);
GO

-- Create a trigger that inserts into ProductAudit
CREATE TRIGGER trg_ProductInsert
ON dbo.Products
AFTER INSERT
AS
BEGIN
    INSERT INTO dbo.ProductAudit (ProductID, AuditAction)
    SELECT ProductID, 'INSERT'
    FROM inserted;
END;
GO

-- Insert a product and compare identity values
INSERT INTO dbo.Products (ProductName) VALUES ('Test Product');

SELECT @@IDENTITY AS [@@IDENTITY],
       SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO

In this example, SCOPE_IDENTITY() returns the ProductID from the Products table (current scope), while @@IDENTITY returns the AuditID from the ProductAudit table (trigger scope). For most application scenarios, SCOPE_IDENTITY() is the safer choice because it isn't affected by trigger activity.

Related content

  • Last updated on