Cómo obtener el último valor insertado de identidad en SQL Server

La propiedad IDENTITY en SQL Server crea una columna de tipo de identidad. Genera valores de incremento automático en la tabla definiendo valores de inicialización e incremento, funciona de manera muy similar al objeto SEQUENCE en SQL Server y Oracle. Sin embargo, la propiedad IDENTITY depende de la tabla y el objeto SEQUENCE funciona independientemente de la tabla.

A menudo requerimos para obtener el último valor insertado de identidad en el servidor SQL. Se utilizan múltiples funciones y métodos para lograr esto.

SCOPE_IDENTITY
@@IDENTIDAD
IDENT_INSERT
Cláusula OUTPUT

Esta publicación lo guiará a través de diferentes formas de obtener el último valor insertado de identidad. Ayudar a utilizar la práctica más recomendada para evitar romper el código de la base de datos en el futuro.

Aquí hay varios ejemplos para obtener el último valor insertado de identidad en SQL Server. Primero, creemos una tabla Student y Student_History con ID como columna de IDENTIDAD.

USE tempdb;

DROP TABLE IF EXISTS Student;

CREATE TABLE Student
(
    ID        INT IDENTITY(1,1) PRIMARY KEY
   ,Name      VARCHAR(255) NOT NULL
   ,Standard  TINYINT
);
GO

DROP TABLE IF EXISTS Student_History;

CREATE TABLE Student_History
(
    ID        INT IDENTITY(1,1) PRIMARY KEY
   ,Name      VARCHAR(255) NOT NULL
   ,Standard  TINYINT
);
GO

1 Función SCOPE_IDENTITY & @@ IDENTITY en SQL Server

SCOPE_IDENTITY es la función más recomendada para obtener el último valor insertado de identidad en SQL Server. Devolverá un valor en el mismo alcance y en la misma sesión.

Hagamos una inserción simple en la tabla Student y obtengamos el último valor insertado de identidad.

use tempdb;
GO

INSERT INTO Student VALUES ('Subhash', 12);

SELECT SCOPE_IDENTITY() as ScopeIdentity;
SELECT @@IDENTITY as [@@IDENTITY];

SELECT * 
FROM   Student;
GO

SCOPE_IDENTITY - Obtener el último valor generado por identidad SQL Server

El resultado es el esperado. Podemos ver que ambas funciones devuelven el valor correcto "1".

Creemos el siguiente disparador para comprender el diferencia entre la función SCOPE_IDENTITY y @@ IDENTITY en SQL Server.

Use tempdb;
GO

DROP TRIGGER IF EXISTS TRG_INSERT_Student_History;
GO

CREATE TRIGGER TRG_INSERT_Student_History ON Student
AFTER INSERT
AS
BEGIN
        INSERT INTO Student_History VALUES ('Test', 10);
END

En la siguiente consulta, hicimos una inserción en la tabla Student que activa un disparador TRG_INSERT_Student_History. Para insertar un registro de prueba en la tabla StudentHistory que tiene una columna de identidad de identificación.

Use tempdb; 
GO

INSERT INTO Student VALUES ('Bhagat', 12);
GO

/* Identity Value "2" expected, same scope and same session. */
SELECT SCOPE_IDENTITY() as LastIdentityValue;

/* Instead of "2" Identity value from Student table it returns "1" from Student_History table considering any scope and any session because trigger was fired Due to above insert statement. */
SELECT @@IDENTITY as LastIdentityValue;

En el ejemplo anterior, aunque la última inserción de identidad se realizó en la tabla StudentHistory a través de un disparador, las funciones SCOPE_IDENTITY devuelven el valor correcto, es decir, "2" de la tabla Student, mientras que la función @@ IDENTITY devuelve "1" de la tabla StudentHistory considerando cualquier alcance y la misma sesión.

Por lo tanto, se recomienda utilizar la función SCOPE_IDENTITY para obtener el último valor insertado de identidad en SQL Server para evitar cualquier conflicto de obtener un valor de identidad de cualquier ámbito o sesión diferente.

2. Función IDENT_CURRENT ('TableName') en SQL Server

Uso de la función IDENT_CURRENT para obtener el último valor insertado de identidad de una tabla especificada independientemente de su alcance o sesión. Puede ser potencialmente peligroso si emitimos una declaración INSERT e intentamos obtener el último valor de identidad generado por la declaración INSERT que podría no ser cierta en caso de que alguna otra sesión haya ejecutado la declaración INSERT en la misma tabla desde otra sesión, por lo que se considerará más adelante valor. Use esto con precaución.

USE tempdb;
GO

INSERT INTO Student VALUES ('Azad', 6);
SELECT IDENT_CURRENT('Student') as IdentCurrent;

3. Cláusula OUTPUT para obtener el último valor insertado de identidad

Otra forma recomendada de obtener el valor de identidad es la cláusula OUTPUT. Podemos capturar múltiples valores de identidad usando la cláusula OUTPUT que no es posible con la función SCOPE_IDENTITY. Usando esto podemos obtener los últimos valores insertados de la tabla mágica INSERTED.

Se requiere una variable de tabla adicional para capturar los valores y luego usarlos. No podemos usar variables locales para almacenar los valores de la cláusula OUTPUT.

USE tempdb;
GO

DECLARE @StudentIdentValues TABLE
(
	ID INT
);

INSERT INTO Student 
OUTPUT inserted.ID INTO @StudentIdentValues (ID)
VALUES ('Tilak', 10), ('Lakshmibai', 6);

SELECT *
FROM   @StudentIdentValues;

Cláusula OUTPUT - Obtener el último valor generado por identidad SQL Server

Resumen

Se recomienda la cláusula SCOPE_IDENTITY y OUTPUT para obtener los últimos valores insertados de identidad en SQL Server.

Puede leer más publicaciones útiles sobre SQL Server de recetas técnicas.

Más artículos similares a Cómo obtener el último valor insertado de identidad en SQL Server en Database.