Comparación Detallada entre SCOPE_IDENTITY() y @@IDENTITY para Optimizar tus Consultas

En el mundo de SQL Server, las funciones SCOPE_IDENTITY() y @@IDENTITY desempeñan roles cruciales al recuperar valores de identidad generados durante la inserción de registros en una tabla. Aunque ambas funciones están diseñadas para propósitos similares, existen diferencias clave que es vital comprender.

1. Alcance de la Función:

• SCOPE_IDENTITY(): Se limita al alcance de la rutina actual, lo que significa que devuelve el último valor de identidad generado dentro del mismo ámbito o bloque de instrucciones T-SQL que realizó la inserción.
• @@IDENTITY: Es más amplio en alcance y puede devolver el último valor de identidad generado en cualquier ámbito, incluso si es diferente al ámbito actual. Esto lo hace más propenso a errores si se ejecutan múltiples operaciones de inserción simultáneas en diferentes ámbitos.

2. Manejo de Desencadenadores (Triggers):

• SCOPE_IDENTITY(): Es más seguro en entornos que involucran desencadenadores, ya que solo devuelve el valor de identidad generado en el ámbito actual, evitando conflictos causados por desencadenadores en otros ámbitos.
• @@IDENTITY: Puede ser afectado por desencadenadores en otros ámbitos, ya que puede devolver el valor de identidad generado más recientemente, incluso si fue causado por una operación en un ámbito diferente.

3. Funciones de Tabla:

• SCOPE_IDENTITY(): Es preferible cuando se trabaja con funciones de tabla, ya que se comporta de manera más predecible y devuelve el valor de identidad de la tabla específica en la que se realizó la inserción.
• @@IDENTITY: Puede devolver el valor de identidad de cualquier tabla, lo que puede resultar en comportamientos inesperados en el caso de operaciones simultáneas en múltiples tablas.

Ambas funciones devuelven el id de la última fila insertada en columnas de identidad, a continuación se describen en que consisten:

* @@identity: Devuelve el valor de identidad de una tabla en la sesión actual. Si se inserta una fila en la tabla A, se inserta una fila en la tabla B, y seleccione @@IDENTITY, obtendrá el valor de identidad insertado en la Tabla B.

create table tableA (

                               ID1 int identity(1,1),

                               Detalle varchar(100)

                )

create table tableB (

                               ID1 int identity(20,1),

                               Detalle varchar(100)

                )

Insert Into tableA Values ('Detalle1')

Insert Into tableB Values ('Detalle2')

Select @@IDENTITY       -- resultado 20

Ahora si realizamos la inserción en una tabla pero ésta tiene un trigger que inserta registros en una segunda tabla, la función retornara el Id de la última tabla y no de la que inicialmente estabas insertando.

* SCOPE_IDENTITY():  Retorna el último valor de identidad generado para cualquier tabla, no sólo en la sesión actual sino también en el ámbito actual (es un modulo como procedimiento almacenado, trigger, funciones). Es la mejor opción para solucionar un escenario como lo expuesto anteriormente, ya que devuelve así sea que la tabla donde se inserte tenga trigger, el último Id del último Scope utilizado.

En resumen, SCOPE_IDENTITY() es más preciso y seguro en situaciones donde el alcance debe ser estrictamente controlado, mientras que @@IDENTITY es más versátil pero potencialmente propenso a errores en entornos más complejos. La elección entre ambas dependerá de las necesidades específicas de la aplicación y del control del alcance requerido.