¿Es verdad que las variables tipo tabla están en memoria RAM?

Dec 26, 2021 | Performance, SQL 2017, SQL 2019, SQL Server, TSQL | 0 comments

¿Las variables tipo Tabla usan la TEMPDB?

Este post tiene como objetivo demostrar uno de los mitos que escucho muy frecuentemente entre programadores y DBA en donde afirman que las variables tipo tabla son mejores que las tablas temporales porque no usan la tempdb y se almacenan en memoria RAM.

Te dejo primero este video explicativo donde te cuento sobre este mito y demuestro que no es así, pero también te cuento como podes realmente usar variables tipo tabla que si residan en memoria RAM.

Link código de ejemplo

Ejemplo 1

En el primer ejemplo declaramos una variable tipo tabla y verificamos en la Tempdb si la usa o no.

USE ADVENTUREWORKS2019 
-------------------------------------------------
--   PRUEBA 1 
-------------------------------------------------
DECLARE @T AS TABLE ([NAME]  VARCHAR(255),
                     [OBJECT_ID] INT,
					 [SCHEMA_ID]  INT,
					 [TYPE_DESC] VARCHAR(255),
					 [CREATE_DATE] DATETIME
					 )
					 
INSERT INTO @T 
SELECT TOP 100 
       NAME,
       OBJECT_ID,
	   SCHEMA_ID,
	   TYPE_DESC,
	   CREATE_DATE
FROM SYS.OBJECTS 

-- MIRAMOS TEMPDB

SELECT
        NAME,
        PS.INDEX_ID,
        PS.ROW_COUNT,
		(PS.USED_PAGE_COUNT * 8.00)/1024.00 AS ESPACIO_MB,
	    CREATE_DATE,
        MODIFY_DATE
    FROM TEMPDB.SYS.OBJECTS AS SO
    JOIN TEMPDB.SYS.DM_DB_PARTITION_STATS PS ON SO.OBJECT_ID = PS.OBJECT_ID
    WHERE
        NAME LIKE N'#%'
        AND ROW_COUNT > 0
GO

La siguiente imagen muestra el resultado de nuestra prueba

Aquí podemos observar que la variable tipo tabla USA la tempdb.

Variables tipo tabla que si usan la memoria

Si deseamos usar variables tipo tabla que realmente usen la RAM y no la Tempdb debemos utilizar memory-optimized table type

Veamos un ejemplo:

USE AdventureWorks2019 
CREATE TYPE dbo.inMemoryTableType  
  AS TABLE 
  (
    [NAME]  VARCHAR(255) INDEX IX1,
    [OBJECT_ID] INT,
	[SCHEMA_ID]  INT,
	[TYPE_DESC] VARCHAR(255),
    [CREATE_DATE] DATETIME
  
  )
  WITH (MEMORY_OPTIMIZED = ON);  
GO
DECLARE @T AS  dbo.inMemoryTableType  
INSERT INTO @T
SELECT TOP 100
       O.NAME,
       O.OBJECT_ID,
	   O.SCHEMA_ID,
	   O.TYPE_DESC,
	   O.CREATE_DATE
FROM SYS.OBJECTS O 

-- MIRAMOS TEMPDB

SELECT
        NAME,
        PS.INDEX_ID,
        PS.ROW_COUNT,
		(PS.USED_PAGE_COUNT * 8.00)/1024.00 AS ESPACIO_MB,
	    CREATE_DATE,
        MODIFY_DATE
    FROM TEMPDB.SYS.OBJECTS AS SO
    JOIN TEMPDB.SYS.DM_DB_PARTITION_STATS PS ON SO.OBJECT_ID = PS.OBJECT_ID
    WHERE
        NAME LIKE N'#%'
        AND ROW_COUNT > 0

Conclusiones

Las variables tipo tabla usan la TEMPDB al igual que las tablas temporales, entonces ¿Cuál es la diferencia entre ambas? pues de funcionalidad , aquí les paso el siguiente cuadro.

Some feature differences between table variable and #temporary tables

Acerca del autor

Maximiliano Accotto

Con mas de 20 años de experiencia y trayectoria trabajando con bases de datos SQL Server y BI.

Fui Microsoft MVP desde el 2005 al 2019 y soy orador frecuente para distintos eventos de Microsoft y comunidades técnicas.

Me especializado en temas de tuning, administracion, performance, diseño y BI en el mundo Microsoft ya sea para ambientes locales como nube.

 

Asesoramiento

¿Necesitas resolver problemas de SQL Server o Micosoft BI?

De ser así te puedo ayudar con mi asesoramiento totalmente personalizado

0 Comments