Tablas temporales & variables

Existen diferencias importantes al diseñar un procedure con tablas temporales. El tiempo de ejecución puede variar considerablemente si seleccionamos incorrectamente el tipo de tabla a utilizar.

Si creamos una tabla temporal dentro de un mismo batch, sql server debe recompilar el query, ya que desconoce la definición de la tabla en la primera compilación, este no es el caso para una tabla temporal y es una de las razones por la que introdujo en la versión 2005 (para reducir las recompilaciones).

El echo de evitar recompilar un procedure no siempre significa que su tiempo de ejecución va a disminuir, tal vez puede ayudarnos cuando tenemos un sistema altamente transaccional, donde los registros de las tablas no son muy grandes y el procedure de ejecuta varias veces por segundo, en este caso posiblemente funcione mucho mejor con una tabla tipo variable, pero como dije anteriormente "Siempre debemos probar las dos opciones".
Voy a exponer un ejemplo donde en mis pruebas el mismo query con una tabla temporal, se ejecuto en menos de 1 segundo y con una tabla variable 1 minuto 20 segundos. Como pueden ver sus tiempos de ejecución son completamente diferentes.

Primero vamos a crear una tabla e insertar 20,000 registros en ella y anexamos un índice clustered.

CREATE TABLE Prueba(ID int)
DECLARE @i int
SET @i = 0
SET NOCOUNT ON
WHILE @i < 20000
BEGIN
INSERT INTO Prueba (ID) Values (@i)
SET @i = @i + 1
END


CREATE CLUSTERED INDEX IX1 ON dbo.Prueba (ID)

Ahora expongo los dos queries a ejecutar, donde si generamos su plan de ejecución estimado, vamos a poder observar que son iguales. Query 1

DECLARE @Tmp1 TABLE (ID int)
INSERT INTO @Tmp1(ID)
SELECT ID
FROM Prueba
SELECT *
FROM Prueba 
WHERE ID NOT IN (SELECT ID FROM @Tmp1)

Query 2

CREATE TABLE #Tmp1(ID int)
INSERT INTO #Tmp1(ID)
SELECT ID
FROM Prueba
SELECT *
FROM Prueba
WHERE ID NOT IN (SELECT ID FROM #Tmp1)
DROP TABLE #Tmp1

El primer query crea una tabla variable mientras que el segundo una tabla temporal. Si ejecutamos los dos queries habilitando el plan de ejecución actual, podremos observar que cambia completamente el plan para el query con la tabla temporal, y este utiliza un Hash Match en lugar del Nested Loop del query 1. Que podemos concluir con esto, que debemos hacer uso de tablas variables cuando estamos seguros que el plan de ejecución que se genera es el más adecuado y queremos evitar las recompilaciones. Pero siempre es mejor probar de las dos maneras antes de decidirnos por una de ellas.

Algunas de las limitantes de una tabla tipo variable son las siguientes:

-No se puede hacer uso de ellas en esta situación:

INSERT @table EXEC sp_Procedure SELECT * INTO @table FROM Table 

-No se puede truncar -No pueden modificarse una vez que están creadas.
- No podemos agregar un índice a menos que sea por medio de una llave primaria o como constraints unicos.

DECLARE @myTable TABLE 
( 
    CPK1 int, 
    CPK2 int, 
    PRIMARY KEY (CPK1, CPK2) 
)

- No podemos usar funciones definidas por el usuario (UDF) en un check constraint, columna calculada o default constraint.
- No podemos usar tipos de datos de usuario (UDT)
- No podemos eliminar la tabla una vez creada
- No podemos generar una tabla tipo variable al vuelo.

SELECT * INTO @tableVariable 
 
Server: Msg 170, Level 15, State 1, Line 1 
Line 1: Incorrect syntax near '@tableVariable'

- No se crean estadísticas.
- La tabla variable siempre tiene cardinalidad 1, porque no existe en su compilación.
- Las tablas no se ven en procedures anidados

En este ejemplo expuse los problemas de hacer uso de tablas tipo variable, pero en muchos casos como en sistemas altamente transaccionales que contienen pocos registros en las tablas involucradas, el tiempo de ejecución es mucho menor en tablas tipo variable que en tablas temporales.