¿Cuál es la mejor opción para obtener el valor de identidad que acabo de generar a través de una inserción? ¿Cuál es el impacto de estas declaraciones en términos de rendimiento?
SCOPE_IDENTITY()
MAX()
.TOP 1
IdentityColumn FROM TableName ORDER BY IdentityColumn DESC
Utilice SCOPE_IDENTITY()
si está insertando una sola fila y quiere recuperar el ID que se generó.
CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));
INSERT #a(x) VALUES('a');
SELECT SCOPE_IDENTITY();
Resultado:
----
1
Utilice la cláusula OUTPUT
si está insertando varias filas y necesita recuperar el conjunto de IDs que se generaron.
INSERT #a(x)
OUTPUT inserted.identity_column
VALUES('b'),('c');
Resultado:
----
2
3
¿Y por qué esta es la mejor opción más rápida?
Dejando de lado el rendimiento, estas son las únicas que garantizan ser correctas en el nivel de aislamiento por defecto y/o con múltiples usuarios. Incluso si se ignora el aspecto de la corrección, SQL Server mantiene el valor insertado en SCOPE_IDENTITY()
en memoria, así que naturalmente esto será más rápido que ir y ejecutar su propia consulta aislada contra la tabla o contra las tablas del sistema.
Ignorar el aspecto de la corrección es como decirle al cartero que hizo un buen trabajo entregando el correo de hoy - terminó su ruta 10 minutos más rápido que su tiempo promedio, el problema es que ninguno de los correos fue entregado a la casa correcta.
**No utilice ninguna de las siguientes opciones:
@@IDENTITY
- ya que esto no puede ser usado en todos los escenarios, por ejemplo cuando una tabla con una columna de identidad tiene un trigger que también inserta en otra tabla con su propia columna de identidad - obtendrá el valor incorrecto de vuelta.IDENT_CURRENT()
- Entro en detalles sobre esto aquí, y los comentarios también son útiles de leer, pero esencialmente, bajo concurrencia, a menudo obtendrás la respuesta incorrecta.o Máximo 1
- tendrías que proteger las dos sentencias con aislamiento serializable para asegurarte de que el Máximo()que obtienes no es el de otra persona. Esto es mucho más caro que usar simplemente
SCOPE_IDENTITY()`.Estas funciones también fallan cuando se insertan dos o más filas, y se necesitan todos los valores de identidad generados - su única opción allí es la cláusula OUTPUT
.
Aparte del rendimiento, todos tienen significados bastante diferentes.
SCOPE_IDENTITY()
le dará el último valor de identidad insertado en cualquier tabla directamente dentro del ámbito actual (ámbito = lote, procedimiento almacenado, etc. pero no dentro de, digamos, un trigger que fue disparado por el ámbito actual).
IDENT_CURRENT()` le dará el último valor de identidad insertado en una tabla específica desde cualquier ámbito, por cualquier usuario.
La función IDENTITY
le dará el último valor de identidad generado por la sentencia INSERT más reciente para la conexión actual, independientemente de la tabla o el ámbito. (Nota al margen: Access utiliza esta función, y por tanto tiene algunos problemas con los triggers que insertan valores en tablas con columnas de identidad).
Utilizar MAX()
o TOP 1
puede dar resultados totalmente erróneos si la tabla tiene un paso de identidad negativo, o ha tenido filas insertadas con SET IDENTITY_INSERT
en juego. Aquí'hay un script que demuestra todo esto:
CREATE TABLE ReverseIdent (
id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
data char(4)
)
INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')
SELECT * FROM ReverseIdent
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000
SET IDENTITY_INSERT ReverseIdent ON
INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')
SET IDENTITY_INSERT ReverseIdent OFF
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005
Resumen: sigue con SCOPE_IDENTITY()
, IDENT_CURRENT()
, o @@IDENTITY
, y asegúrate de que estás usando el que devuelve lo que realmente necesitas.