¿Cuál es la diferencia entre Scope Identity(), Identity(), @@Identity y Ident Current()?

Buena pregunta.

• @@IDENTITY: devuelve el último valor de identidad generado en su conexión SQL (SPID). La mayoría de las veces será lo que quieras, pero a veces no lo es (como cuando se dispara un disparador en respuesta a un INSERT, y el disparador ejecuta otra instrucción INSERT).

• SCOPE_IDENTITY(): devuelve el último valor de identidad generado en el ámbito actual (es decir, procedimiento almacenado, disparador, función, etc.).

• IDENT_CURRENT(): devuelve la última identidad valor para una tabla específica. No use esto para obtener el valor de identidad de un INSERT, está sujeto a condiciones de carrera (es decir, múltiples conexiones insertando filas en la misma tabla).

• IDENTITY(): se usa cuando se declara una columna en una tabla como columna de identidad.

Para más referencias, ver: http://msdn.microsoft.com/en-us/library/ms187342.aspx .

Para resumir: si está insertando filas, y desea saber el valor de la columna de identidad para el row que acaba de insertar, siempre use SCOPE_IDENTITY().

Si entiende la diferencia entre ámbito y sesión, entonces será muy fácil entender estos métodos.

Una entrada de blog muy agradable de Adam Anderson describe esta diferencia:

Session significa la conexión actual que está ejecutando el comando.

Scope significa el contexto inmediato de un comando. Cada llamada a un procedimiento almacenado se ejecuta en su propio ámbito, y las llamadas anidadas se ejecutan en un ámbito anidado dentro de la llamada alcance del procedimiento. Del mismo modo, un comando SQL ejecutado desde una aplicación o SSMS se ejecuta en su propio ámbito, y si ese comando dispara cualquier disparador, cada disparador se ejecuta dentro de su propio ámbito anidado.

Así, las diferencias entre los tres métodos de recuperación de identidad son las siguientes:

@@identity devuelve el último valor de identidad generado en esta sesión pero cualquier ámbito.

scope_identity() devuelve el último valor de identidad generado en this session and this scope.

ident_current() devuelve el último valor de identidad generado para una tabla en particular en cualquier sesión y cualquier ámbito.

Ámbito significa el contexto de código que ejecuta la instrucción INSERT SCOPE_IDENTITY(), en oposición al ámbito global de @@IDENTITY.

CREATE TABLE Foo(
  ID INT IDENTITY(1,1),
  Dummy VARCHAR(100)
)

CREATE TABLE FooLog(
  ID INT IDENTITY(2,2),
  LogText VARCHAR(100)
)
go
CREATE TRIGGER InsertFoo ON Foo AFTER INSERT AS
BEGIN
  INSERT INTO FooLog (LogText) VALUES ('inserted Foo')
  INSERT INTO FooLog (LogText) SELECT Dummy FROM inserted
END

INSERT INTO Foo (Dummy) VALUES ('x')
SELECT SCOPE_IDENTITY(), @@IDENTITY 

Da resultados diferentes.

Debido al error mencionado por @David Freitas y debido a la incompatibilidad con la nueva función de Secuencia que se introdujo en 2012, recomendaría mantenerse alejado de los tres. En su lugar, puede usar la cláusula OUTPUT para obtener el valor de identidad insertado. La otra ventaja es que la SALIDA incluso funciona si ha insertado más de una fila.

Para detalles y ejemplos ver aquí: Crisis de identidad

Para aclarar el problema con @@Identity:

Por ejemplo, si inserta una tabla y esa tabla tiene disparadores haciendo inserciones, @@Identity devolverá el id de la inserción en el disparador (a log_id o algo), mientras que scope_identity() devolverá el id de la inserción en la tabla original.

Así que si no tienes ningún disparador, scope_identity() y @@identity devolverán el mismo valor. Si tienes desencadenantes, necesitas pensar en qué valor te gustaría.

Aquí hay otra buena explicación de el libro :

En cuanto a la diferencia entre SCOPE_IDENTITY y @@IDENTITY, supongamos que tiene un procedimiento almacenado P1 con tres sentencias:
- Una INSERCIÓN que genera un nuevo valor de identidad
- Una llamada a un procedimiento almacenado P2 que también tiene una instrucción INSERT que genera un nuevo valor de identidad
- Una declaración que consulta las funciones SCOPE_IDENTITY y @ @ IDENTITY La función SCOPE_IDENTITY devolverá el valor generado por P1 (misma sesión y ámbito). La función @ @ IDENTITY devolverá el valor generado por P2 (misma sesión independientemente del ámbito).

Scope Identity: Identidad del último registro añadido dentro del procedimiento almacenado que se está ejecutando.

@@Identity: Identidad del último registro agregado dentro del lote de consulta, o como resultado de la consulta, por ejemplo, un procedimiento que realiza una inserción, el entonces dispara un disparador que luego inserta un registro devolverá la identidad del registro insertado desde el disparador.

IdentCurrent: La última identidad asignada a la tabla.