Did you Know Table Variable Ignore Transactions?

They do, they are variables and are not affected by transactions, we must be careful when using them along with transactions, since it can lead to bad results.

Here is a quite common scenario that I have seen in production environment:

Insert data to a table, use OUTPUT to capture the records that were inserted, to finally do something weird with those records... I know, we do weird stuff with SQL Server.

If you are not familiar with OUTPUT, here is one of my previous post where I explain a little bit about it: SQL-How to capture the newly generated identity values after a multi-row insert?

The lab

Following script is very simple, please see below.

1. Create a table and a variable table

2. Open a transaction.

3. Insert some data and capture such data with OUTPUT.

4. Rollback the transaction

5. Show the data.

--\\ 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;

Script will return the following results:

Main table does not return any rows due to RollBack, but the table variable was not affected by transactions.

If you have any questions or suggestions, please let me know in the comments.

Enjoy!!

¿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.

Los Porcentajes en los planes de Ejecución Mienten y son buenos haciéndolo.

Cada vez que le digo a alguien que el costo porcentual en el plan de ejecución miente y que no deben confiar en él cuando se está haciendo performance tuning a una consulta, recibo diferentes reacciones / preguntas como:

¡Tú eres el que miente!

¿Puedes probarlo?

¿Por qué lo pusieron si esta mal?

Siempre que recibo esta pregunta, les comparto una publicación hecha por mi amigo Forrest [t,b] . Deberías leer esta publicación antes de ponerte a leer mis locuras, el es mucho más inteligente que yo :)

Aqui esta el post: Percentage Non Grata

Ayer, alguien comenzó a hacer muchas preguntas después de leer el post arriba mencionado, esta persona asegura de que los porcentajes estaban mal porque las estadísticas fueron forzadas, etc. Intenté explicar que el punto principal fue probado, y las estadísticas no son perfectas en un entorno de producción, pero no fue suficiente para convencerlo(s), así que decidí compartir mi pantalla y demostrarlo en una sesión en web.

Aquí hay un script para crear nuestro laboratorio, una tabla y una función (UDF).

--\\Create table
CREATE TABLE [dbo].[UserProfile] (UserID INT  PRIMARY KEY CLUSTERED IDENTITY(1,1)
,AboutMe NVARCHAR(1500) NOT NULL
, MyValue1 INT NOT NULL);
GO
--\\ Insert some data :)
INSERT INTO [dbo].[UserProfile](AboutMe,MyValue1)
SELECT TOP (2000000)    AboutMe= REPLICATE( LEFT(CAST(NEWID() AS VARCHAR(520)), 5), 3)
				,   ROW_NUMBER() OVER (ORDER BY a.name)%3
FROM sys.all_objects a
CROSS JOIN sys.messages c
UNION ALL
SELECT TOP (2000000)    AboutMe= REPLICATE( LEFT(CAST(NEWID() AS VARCHAR(520)), 5), 3)
				,   3
FROM sys.all_objects a
CROSS JOIN sys.messages c;
GO
--\\Create and index to help our query.
CREATE INDEX IX_Value1 ON [dbo].[UserProfile] (MyValue1) WITH FILLFACTOR=100;
GO
--\\ Create scalar function.
CREATE OR ALTER FUNCTION dbo.Total(@MyValue1 INT)  
RETURNS BIGINT  
WITH SCHEMABINDING
AS   
BEGIN    
    DECLARE @Total BIGINT;        
	SELECT @Total=COUNT_BIG(*) 
	FROM [dbo].[UserProfile]
	WHERE MyValue1=@MyValue1;

    RETURN @Total;    
END;
GO

Una vez que la tabla esté lista, ejecutaremos las siguientes consultas, asegúrese de incluir el Plan de ejecución (Actual Execution Plan).

Nota: El nivel de compatibilidad se establecio en 140 (SQL 2017)

--\\ Compare the function and raw query
--\\ Include Actual Execution Plan (Ctrl + M)

SELECT Total=COUNT_BIG(*) 
FROM [dbo].[UserProfile]
WHERE MyValue1=3;

SELECT dbo.Total(3) as Total;

Ambas consultas son idénticas, la misma lógica, los mismos resultados y el mismo número de lecturas, la única diferencia es la función (UDF). ¿Qué pasa con los porcentajes? Serán los mismos?. Por favor vea a continuación ...

SQL Server no puede expandir la lógica detrás de la función (UDF), por lo tanto, los porcentajes están mintiendo

Soy consciente de SQL Server 2019 y Froid, pero seamos honestos, no vamos a actualizar pronto y Froid no va a arreglar todos las funciones (UDF).

Las consultas anteriores no fueron manipuladas, entonces, ¿qué sucede si confundimos un poco a SQL Server?

Ejecutemos estas tres consultas:

SELECT  A.MyValue1
	   ,A.AboutMe
FROM [dbo].[UserProfile] A  
WHERE A.MyValue1=3
ORDER BY A.MyValue1
OPTION(FAST 10); --\\ Give me the first 10 rows as fast as you can.

SELECT TOP (10) A.MyValue1
              , A.AboutMe
FROM [dbo].[UserProfile] A
WHERE A.MyValue1=3
ORDER BY A.MyValue1; --\\ Let SQL server handle.


SELECT TOP (10) A.MyValue1
              , B.AboutMe 
FROM [dbo].[UserProfile] A 
		INNER LOOP JOIN [dbo].[UserProfile] B --\\Forcing Loop Join and selference, expecting SQL Server seeks using cluster and noncluster indexes.
				ON A.UserID= B.UserID
WHERE A.MyValue1=3
ORDER BY A.MyValue1;

1. Consulta #1, regresa 2,000,000 filas, pero yo le pedí a SQL Server que regresara las primeras 10 filas lo más rápido posible (Query Hint).

2. Consulta #2, regresa 10 filas, una consulta muy simple y sin trucos.

3. Consulta #3, regresa 10 filas, he forzado a que se genere un Nested Loop Join con una referencia a si mismo.

Como podras anticipar, los porcentajes mienten nuevamente... Vea más abajo porfavor.

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