Las variables tipo tabla son usadas por muchos programadores SQL Server los cuales tienen la idea de que no usan la base Tempdb y se crean en memoria.

En este articulo vamos a demostrar que esto no es cierto y que para crear realmente tablas en memoria debemos usar las funcionalidades Inmemory OLTP.

Analizando la tempdb

Nuestro primer paso sera analizar el comportamiento de la base Tempdb para lo cual lo que haremos es contar la cantidad de entradas en su Log previamente haciendo una operación de checkpoint, para ello usaremos el siguiente código

USE TEMPDB;
GO
CHECKPOINT;
GO
SELECT COUNT(*) [CANTIDAD]
FROM SYS.FN_DBLOG (NULL, NULL);

Al hacer esta operación en mi caso arrojo un resultado de 3 registros

Tablas temporales

Ahora lo que vamos a hacer es crear una simple tabla temporal # para que podamos observar que sucede en nuestra base Tempdb, para ello ejecutaremos el siguiente código TSQL

USE TEMPDB;
GO
DROP TABLE IF EXISTS #R;
GO
CREATE TABLE #R (ID INT IDENTITY, C1 VARCHAR(255));

Ahora le insertaremos algunos registros a nuestra tabla temporal

INSERT INTO #R (C1)
SELECT NAME
FROM MASTER.SYS.COLUMNS
GO 5

Observamos luego cuantos registros hay en el log de la tempdb con el comando que usamos anteriormente

SELECT COUNT(*) [CANTIDAD]
FROM SYS.FN_DBLOG (NULL, NULL);

En mi caso arrojo una cantidad de 763 registros

Si ejecutamos el siguiente código sobre nuestra tempdb podremos observar que se creo un objeto temporal en la misma

SELECT NAME
,CREATE_DATE
,TYPE_DESC
FROM SYS.OBJECTS O
WHERE IS_MS_SHIPPED = 0;

Variables tipo tabla

Ahora vamos a hacer la misma prueba que realizamos anteriormente pero con las variables tipo tabla en lugar de tablas temporales corriendo el siguiente código.

CHECKPOINT;
GO
SELECT COUNT(*) [CANTIDAD]
FROM SYS.FN_DBLOG (NULL, NULL);

Aquí verificamos que la cantidad de registros en la tempdb volvió a 3.

DECLARE @R AS TABLE (ID INT IDENTITY,C1 VARCHAR(255))
INSERT INTO @R (C1)
SELECT NAME
FROM MASTER.SYS.COLUMNS
GO 5
SELECT COUNT(*) [CANTIDAD]
FROM SYS.FN_DBLOG (NULL, NULL);

En mi caso la cantidad de registros al crear la variable tipo tabla paso de 3 a 962 donde queda demostrado que estas si usan la TEMPDB

Para hacer una verificación adicional de que las variables tipo tabla usan nuestra tempdb vamos a ejecutar el siguiente código para observar que se creo también un objeto interno en la base tempdb para nuestra variable

DECLARE @R AS TABLE (ID INT IDENTITY,C1 VARCHAR(255))
INSERT INTO @R (C1)
SELECT NAME
FROM MASTER.SYS.COLUMNS
SELECT NAME
,CREATE_DATE
,TYPE_DESC
FROM SYS.OBJECTS O
WHERE IS_MS_SHIPPED = 0;

¿Como crear tablas temporales reales en memoria?

Para poder realmente crear tablas temporales que usen memoria y sean mucho mas eficientes lo que hay que usar son las distintas funcionalidades in-memory que tiene SQL Server (Tablas, Stores Procedures, types, funciones), en nuestro caso usaremos un Type in memory.

Para ello lo primero que haremos es crearnos un type en una base de datos con capacidades inmemory.

DROP DATABASE IF EXISTS  TESTTEMPTABLE 
CREATE DATABASE TESTTEMPTABLE 
USE [master]
GO
ALTER DATABASE [TESTTEMPTABLE] 
ADD FILEGROUP [inmemoltp] CONTAINS MEMORY_OPTIMIZED_DATA 
GO
ALTER DATABASE TESTTEMPTABLE 
ADD FILE (name = [imoltp_dir], 
          filename= 'e:\tmp\sql\inmem') 
TO FILEGROUP inmemoltp;
GO

-- type in memory

USE TESTTEMPTABLE;
GO
CREATE TYPE dbo.MemTable 
  AS TABLE 
  ( id INT identity primary key nonclustered,
    C1 VARCHAR(255) )
  WITH (MEMORY_OPTIMIZED = ON);  
GO

Ahora haremos un checkpoint en la tempdb para volver a tener nuestros 3 registros

USE tempdb 
GO
CHECKPOINT;
SELECT COUNT(*) [CANTIDAD]
FROM SYS.FN_DBLOG (NULL, NULL);

Nuestro siguiente paso es usar este Type y medir cuantos registros tenemos en la Tempdb

USE TESTTEMPTABLE;
GO
DECLARE @v dbo.MemTable 
INSERT INTO @v (C1)
SELECT NAME
FROM MASTER.SYS.COLUMNS
GO 5
use tempdb 
go
SELECT COUNT(*) [CANTIDAD]
FROM SYS.FN_DBLOG (NULL, NULL);

En mi caso la cantidad de registros en la tempdb fue de tan solo 97 , casi unas 10 veces menos que con tablas o variables temporales.

Conclusiones

Muchos programadores tienen la creencia que las variables tipo tabla son mas eficientes porque se crean en memoria RAM y no usan la Tempdb, en este post lo que pudimos demostrar que esto no es así.

Si revisamos el siguiente link de los BOL (Libros online de SQL Server) vamos a encontrar el siguiente texto:


A traditional table variable represents a table in the tempdb database. For much faster performance you can memory-optimize your table variable.

Share This
%d bloggers like this: