Una duda bastante comun entre los desarrolladores que trabajan con SQL Server, es saber
distinguir entre una tabla temporal y una variable de tabla.
Ambas estructuras presentan varias diferencias importantes, y fueron hechas para resolver distintos problemas, aunque en muchas situaciones, su uso puede superponerse.
Veamos algunas características de las variables de tabla y sus diferencias con las tablas temporales:
1) Contra lo que muchos suponen, el contenido de una variable de tabla no siempre esta completamente en memoria. En caso de que se inserte una alta cantidad de registros a la variable, se almacenará en tempdb. En cambio una tabla temporal siempre se guarda en tempdb.
Además, cuando se crea una variable tabla, es como si se efectuara cualquier otra operación DDL, por lo tanto la información de la metada se guarda en las tablas de catalogo.
Veamos este código:
DECLARE @TablaTemporal TABLE (ID int)
SELECT * FROM tempdb.sys.tables
Si ejecutamos esto, vemos que en la vista de catalogo sys.tables, aparece el objeto #XXXX, que es la variable de tabla que creamos.
2) No hay rollback en variables de tabla. Las operaciones contra un objeto de este estilo generan menos bloqueos y hacen menor uso del log de transacciones.
En caso de que se haga un INSERT/UPDATE/DELETE contra la variable, dentro de una transacción, al hacer ROLLBACK, el contenido seguirá siendo el mismo.
La ventaja de esto, es que obviamente es más rápido y consume menos recursos. La desventaja es que si necesitamos que sea transaccional la operación, no lo podemos hacer con este tipo de estructuras.
Ejemplo:
DECLARE @TablaTemporal TABLE (ID int)
BEGIN TRANSACTION
INSERT INTO @TablaTemporal (ID) VALUES (1)
INSERT INTO @TablaTemporal (ID) VALUES (2)
INSERT INTO @TablaTemporal (ID) VALUES (3)
Como podemos ver, el resultado devuelto en el ultimo SELECT es el mismo que el devuelto dentro de la transacción, por lo tanto, no efectuó el ROLLBACK.
3) No se pueden agregar índices en variables de tabla. Esto es una gran desventaja a la hora de trabajar con un conjunto gran de datos. Sin embargo, existe una manera de tener índices. Para eso, hay que definir una PRIMARY o UNIQUE KEY cuando se crea la tabla, aunque claro, no siempre este workaround nos sirve. No es posible declarar un NON-CLUSTERED index en una variable de tabla.
Con una tabla temporal en cambio, se puede efectuar las mismas operaciones que una tabla común, incluyendo el poder agregar índices.
Ejemplo de como crear una variable de tabla con índices:
DECLARE @TablaTemporal TABLE (ID int, PRIMARY KEY ([ID]))
Si ejecutamos la consulta que esta mas arriba, vamos a ver el índice de la variable de tabla en las vista de catalogo de tempdb.
4) Otro ítem importantes es que un store procedure que haga uso de tablas temporales, puede que se recompile seguido (dependiendo de como varían las estadísticas), mientras que un store que haga uso de variables de tabla no va sufrir recompilaciones. Si el store es ejecutado muy seguido o es lo suficientemente largo para que se note el tiempo de recompilación, puede llegar a ver una diferencia de performance.
De todos modos, en SQL Server 2005 se mejoro la forma en que se precompilan los store procedures que usan tablas temporales, mejorando la performance…
5) Las variables de tabla no usan paralelismo (multiple threads) en su plan de ejecución. En tablas grandes o en alta concurrencia, esto impacta en la performance, haciéndolas mas lentas en comparación con las tablas temporales.
6) Las variables de tabla tampoco recolectan estadísticas. Esto puede ser bueno y malo a la vez. En caso de tener tablas chicas, las estadísticas no son tan necesarias, por lo cual, el no tener que recolectarlas hace las operaciones de escritura mas rápidas.
Pero por otro lado, al no tener estadísticas, el plan de ejecución que genera el SQL puede no ser el más óptimo.
7) El scope de una tabla temporal es la conexión a la base. Todos los objetos de la base, puede ver el contenido de esta tabla, mientras persista la misma conexión.
Mientras que el scope de una variable de tabla es local solo el store procedure en que se la llama, al igual que una variable común.
CONCLUSION:
No se puede decir que las tablas temporales son peores o mejores que las variables de tabla. Siempre hay que evaluar las 2 alternativas.
Sin embargo, podríamos afirmar que, en operaciones con muchos datos, las tablas temporales tienen más herramientas para ofrecer mejor performance, mientras que para procesos chicos, con pocos registros, las variable de tabla son mejores, al tener menos overhead.
SELECT * FROM @TablaTemporal
ROLLBACK TRANSACTION
SELECT * FROM @TablaTemporal
SELECT * FROM tempdb.sys.indexes I INNER JOIN tempdb.sys.tables T
ON I.object_id = t.object_id
grimpi