¿Sabías que las variables tipo tabla ignoran las transacciones?

Lo hacen, son variables y no se ven afectadas por las transacciones, debemos tener cuidado al usarlas junto con las transacciones, ya que puede conducir a malos resultados.

Aquí hay un escenario bastante común que he visto en producción:

Inserte datos en una tabla, use OUTPUT para capturar los registros que se insertaron, para finalmente hacer algo extraño con esos registros ... Lo sé, hacemos cosas raras con SQL Server.

Si no está familiarizado con OUTPUT, aquí está una de mis anteriores publicaciones donde hablo de ello:¿Cómo capturar los valores identity recién generados después de una inserción de varias filas?

El Laboratorio

1. El siguiente script es muy simple, vea a continuación.

2. Crear una tabla y una tabla variable.

3. Abrir una transacción.

4. Inserte algunos datos y capture dichos datos con OUTPUT.

5. Revertir la transacción

6. Mostrar los datos.

--\\ Drop the table
DROP TABLE IF EXISTS MyNewTable;

--\\ Create main table
CREATE TABLE MyNewTable (	ROW_ID INT IDENTITY(1,1) NOT NULL
						,	myValue_1 INT NULL
						,	myValue_2 INT NULL);

--\\DECLARE  @ Table
DECLARE  @MyAudit TABLE (	ROW_ID INT NOT NULL
						,	myValue_1 INT NULL
						,	myValue_2 INT NULL);

--\\Begin Transaction
BEGIN TRANSACTION;
--\\ INSERT DATA
INSERT INTO MyNewTable

	--\\ Output the data 
	OUTPUT INSERTED.ROW_ID
		,  INSERTED.myValue_1
		,  INSERTED.myValue_2 
	INTO @MyAudit

	-- \\ This generated the insertion.
	SELECT TOP 4
			myValue_1= ROW_NUMBER() OVER (ORDER BY a.name)% 4
		,	myValue_1= ROW_NUMBER() OVER (ORDER BY a.name)% 10
	FROM sys.all_objects a
	CROSS JOIN sys.messages c;

--\\Rollback
ROLLBACK;

--\\Show the data inserted in main table (remember transaction was rolled-back)
SELECT *
FROM MyNewTable;

--\\Show the data that was captured with Output
SELECT *
FROM @MyAudit;

El script anterior regresara los siguientes resultados:

La tabla principal no devuelve nada debido al RollBack, pero la variable tipo tabla no se vio afectada por las transacciones,.

Si tiene alguna pregunta o sugerencia, hágamelo saber en los comentarios.