Grimpi IT Blog

abril 23, 2008

Tablas temporales versus Variables de Tabla

Filed under: Engine, SQL Server — Etiquetas: — grimpi @ 5:40 pm

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)

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

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.

10 comentarios »

  1. gracias, información desde mi punto de vista muy clara

    Comentario por Jorge — mayo 8, 2009 @ 10:40 pm

  2. Exelente articulo, me aclarado las dudas que tenia sobre el uso de las tablas temporales y variables de tipo tabla.

    Comentario por Lester Herrera — octubre 19, 2009 @ 8:07 pm

  3. Excelente artículo, corto, claro y conciso. Felicitaciones

    Comentario por Carlos — marzo 19, 2010 @ 4:39 pm

  4. Gracias por la informacion me fue muy util..

    Comentario por Darwin — septiembre 10, 2010 @ 7:50 pm

  5. Buenísimo. Muchas gracias.

    Comentario por Jakub — noviembre 19, 2010 @ 8:28 pm

  6. Estimado. Gracias por la información.
    Ahora quisiera hacerle una consulta
    Estoy creando un sp en Sql 2005
    Creo la variable de tabla, le inserto registros, hasta ahi todo bien
    Cuando agrego al Store el siguiente código

    DELETE Clientes
    FROM Clientes, @TablaTemporal
    WHERE clientes.nrocliente = @TablaTemporal.nrocliente

    me aparece un mensaje de error ‘Debe declarar la variable escalar “@TablaTemporal”‘

    Alguna sugerencia?
    Mil gracias

    Comentario por Luis — diciembre 6, 2010 @ 5:11 pm

    • Puedes probar directamente la sentencia sin el from

      DELETE Clientes
      WHERE clientes.nrocliente = @TablaTemporal.nrocliente

      Comentario por Guillermo — diciembre 7, 2010 @ 2:06 pm

      • Puedes probar directamente la sentencia sin el from

        DELETE Clientes
        WHERE clientes.nrocliente = @TablaTemporal.nrocliente

        si lo que quieres es un match
        pues en el caso de la variable tabla
        tu declaras la como se llamara dicha variable
        DECLARE @TablaTemporal TABLE (Tt_nrocliente int)

        DELETE
        FROM Clientes, @TablaTemporal
        WHERE clientes.nrocliente = Tt_nrocliente

        Comentario por rudyhdz — abril 14, 2011 @ 4:52 pm

  7. Reblogged this on dbasqlserver.

    Comentario por ownerdba — junio 27, 2012 @ 10:14 pm

  8. A mi tambien me gusto el articulo… gracias

    Comentario por Katita — mayo 23, 2014 @ 4:53 pm


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: