[SQL] ¿Por qué utilizar Tablas Temporales Locales, Globales o Variables de Tabla?.


Saludos amigos en esta ocasión leyendo me encontré con un tema muy interesante acerca de las tablas temporales en SQL, les dejo lo que traduje del tema con una explicación y ejemplo de como crear tablas temporales locales, globales y variables de tabla, dentro del Motor de Base de Datos de SQL Server 2008.

Tablas Temporales.

Las tablas temporales son consideradas tablas regulares, y estas se almacenan automáticamente en la base de datos de tempdb.

Las tablas temporales se pueden usar en los siguientes escenarios:

Como alternativa a los cursores: por ejemplo, en lugar de utilizar un cursor de Transact-SQL para recorrer un conjunto de resultados y realizar tareas basadas en cada fila, en su lugar puede utilizar una tabla temporal. Usando un bucle WHILE, puede recorrer cada fila de la tabla y realizar la acción de la fila especificada, posteriormente debe eliminar la fila de la tabla temporal.

Como almacenamiento incremental de conjuntos de resultados: Por ejemplo, imaginemos que tiene una sola consulta SELECT que realiza una combinación “JOIN” con diez tablas. A veces las consultas con varias combinaciones “JOINS” pueden funcionar de manera incorrecta. Una técnica para intentar es la de fraccionar o fragmentar una consulta grande en consultas más pequeñas. Si usamos tablas temporales, podemos crear conjuntos de resultados intermedios basados en consultas de menor tamaño, en lugar de intentar ejecutar una consulta única que sea demasiado grande y de combinaciones múltiples “multi-joined”.

Como temporal en la sobrecarga baja de búsqueda de tabla: Por ejemplo, imagine que usted está utilizando una consulta que tarda varios segundos en ejecutarse, pero sólo muestra un pequeño conjunto de resultados, el cual desea utilizar en varias áreas de su procedimiento almacenado, pero cada vez que se llama se incurre en el tiempo de ejecución de la consulta general. Para resolver esto, puede ejecutar la consulta una sola vez en el procedimiento, llenando una tabla temporal, de esta manera se puede hacer referencia a la tabla temporal en varios lugares en su código, sin incurrir en una sobrecarga de resultados adicional.

Existen dos tipos de tablas temporales: Globales y Locales.

Las tablas temporales Locales se escriben anteponiendo el símbolo # y tablas temporales Globales con el doble símbolo ##.

Tablas temporales Locales.

Las tablas temporales Locales están disponibles para usarse por cada conexión actual del usuario que los cree.

Varias conexiones pueden crear una tabla temporal con mismo nombre, esto solo para para tablas temporales Locales sin causar conflictos. La representación interna de la tabla local tiene un nombre único, para no estar en conflicto con otras tablas temporales con el mismo nombre creado por otras conexiones en la tempdb.

Las tablas temporales locales son eliminadas con el comando DROP o se eliminan automáticamente de memoria cuando se cierra la conexión del usuario.

Creando la tabla temporal Local:

CREATE TABLE #ProductosResumen
( idProducto int NOT NULL PRIMARY KEY,
  nombre varchar(75) NULL,
  precio smallmoney NULL
);

Insertando datos en la tabla temporal global.

INSERT #ProductosResumen (idProducto, nombre , precio)
SELECT id,nombre, precio FROM dbo.DEMO_PRODUCTO
ORDER BY nombre;

Seleccionando los datos de la tabla temporal.

--Seleccionando los datos de la tabla temporal
SELECT * FROM #ProductosResumen; 

-- Resumen de precios de la tabla temporal
SELECT AVG(precio) FROM #ProductosResumen;

 

Resultado.

image

Eliminando la tabla.

-- Eliminando la tabla temporal
DROP TABLE #ProductosResumen;

Nota: Para que el ejemplo funcione deben de ejecutar en un solo bloque de instrucciones todo el código que coloque anteriormente. Para este caso yo llene mi tabla temporal con datos de una tabla que ya tenia creada en la base de datos, ustedes pueden utilizar un insert normal.

Tablas Temporales Globales.

Las tablas temporales Globales tienen un alcance diferente al de las tablas temporales Locales. Una vez que una conexión crea una tabla temporal Global, cualquier usuario con permisos adecuados sobre la base de datos puede acceder a la tabla. A diferencia de tablas temporales Locales, no se pueden crear versiones simultáneas de una tabla temporal Global, ya que esto generará un conflicto de nombres.

Las tablas temporales Globales de eliminan explícitamente de SQL Server ejecutando DROP TABLE. También se eliminan automáticamente después de que se cierra la conexión que la creo, la tabla temporal Global no es referenciada por otras conexiones, pero es muy raro ver que se utilicen tablas temporales Globales en bases de datos en producción.

Es importante considerar cuando una tabla va o debe ser compartida a través de conexiones, se debe crear una tabla real, en lugar de una tabla temporal Global. No obstante, SQL Server ofrece esto como una opción.

Creando la tabla temporal Global:

--Creando la tabla temporal Global
CREATE TABLE ##Roles
( idRol int NOT NULL PRIMARY KEY,
  nombre varchar(30) NULL,
  activo bit NULL
);

Insertando y seleccionando datos de la tabla temporal global:

-- Insertando datos en la tabla temporal global
INSERT INTO ##Roles
VALUES(1,'Administrador',1),
      (2,'Supervisor',1),
      (3,'Programador',0)

-- Seleccionando los datos de la tabla temporal global
SELECT * FROM ##Roles;

Resultado:

image 

Eliminando la tabla temporal global desde la conexión original que la creo.

-- Eliminando la tabla temporal global
DROP TABLE    ##Roles;

Variables de Tabla.

Por otro lado tenemos las Variables de Tabla que son un tipo de datos que puede ser utilizados en un lote Transact-SQL (Batch), procedimiento almacenado o función; estas variables de tabla son creado y definidas de forma similar a una tabla, sólo con un alcance de vida estrictamente definido. Las Variables de tabla suelen ser buenos reemplazos de tablas temporales siempre y cuando el conjunto de datos es pequeño.

Razones para usar las variables de tabla:

  • Duración o alcance. La duración de la variable de tabla sólo vive durante la ejecución del lote, función, o procedimiento almacenado.
  • Tiempos de bloqueo más cortos. Por el estrecho alcance o tiempo de vida.
  • Menos re compilaciones cuando se usa en los procedimientos almacenados.

Como se menciono anteriormente, hay inconvenientes para utilizar las variables de tabla. El rendimiento de las variable de tabla se ve afectado cuando el resultado es demasiado grande o cuando los datos de la columna de cardinalidad son fundamentales para la optimización del proceso de consulta.

La sintaxis para crear una variable de tabla es similar a la de crear una tabla normal, se utiliza la palabra clave DECLARE y el nombre de tabla, anteponiendo el símbolo @:

DECLARE @TableName TABLE

(column_name <data_type> [ NULL | NOT NULL ] [ ,…n ] )

Creando una variable de tabla:

-- Creando la variable de tipo tabla.
DECLARE @EstatusUsuarios TABLE
( idEstatus int NOT NULL PRIMARY KEY,
  nombre varchar(30) NULL
)

Insertando y seleccionando datos de la variable tabla:

-- Insertando en la variable de tipo tabla.
INSERT INTO @EstatusUsuarios VALUES (1,'Activo'),
                                    (2,'Inactivo'),
                                    (3,'Bloqueado')

-- Consultando datos de la variable de tipo tabla.
SELECT * FROM @EstatusUsuarios;

Al terminar la ejecución del batch o bloque de instrucciones se eliminara la variable tabla, o si colocamos una instrucción GO automáticamente se eliminara y no la podremos utilizar como los muestro en el siguiente ejempló, recuerde que los ejemplo para claridad los coloque separados, pero los debe de ejecutar completos como lo hago abajo para el caso de la variables de tabla:

-- Creando la variable de tipo tabla.
DECLARE @EstatusUsuarios TABLE
( idEstatus int NOT NULL PRIMARY KEY,
  nombre varchar(30) NULL
)

-- Insertando en la variable de tipo tabla.
INSERT INTO @EstatusUsuarios VALUES (1,'Activo'),
                                    (2,'Inactivo'),
                                    (3,'Bloqueado')

-- Consultando datos de la variable de tipo tabla.
SELECT * FROM @EstatusUsuarios;

-- Al ejecutarse el go o el bloque de instrucciones
--la variable tabla se eliminara auitomaticamente.
Go

SELECT * FROM @EstatusUsuarios;

Como podrán notar en el primer select nos regreso:

image

Y para el segundo select:

image Esto es por que como lo comente anteriormente el tiempo de vida de la variable tabla termina al ejecutarse el bloque de instrucciones, en este caso termino con el GO.

Cuando encontremos problemas de rendimiento, debemos de asegurarnos de probar todas las soluciones y alternativas, y no necesariamente asumir que una de las opciones (tablas Temporales) son menos deseables que otras (variables de tabla).

Espero les sea de ayuda, se que esta un poco largo el Post, pero creo vale la pena leerlo.

 

votaPorMiBlogIvan Apóyame votando aquí: http://blogit.ms/TopBloggers.aspx en la categoría SQL Server. Los pasos para votar están aquí te llevara 3 min: Clic Aquí.

    • David
    • 23/09/09

    Hola

    Soy princiopiante en SQL Server, pues siempre programe en FOX, y solo usaba SQL Server como reservorio de datos y todos los proceso los programaba directamente en FOX,pero ahora que Microsoft ha anunciado que lo DESCONTINUA!! pues estoy viendo como pasar muchos de mis procesos a SP de SQL Server y tu articulo me aclaro bastante como evitar conflictos de nombres de tabla en un SP el cual crea tablas y puede ser ejecutado por varios clientes de la base de datos simultaneamente.

    Gracias y sigue asi

    Saludos

    • luis Hdez
    • 15/02/10

    Hola, yo ya habia trabajado con tablas temporales en Oracle pero ahora que tengo que realizar algunos calculos con tablas de SQL server , me aclaraste dudas que ya traia de antes. Esta muy digerible , con ejemplos y conciso tu blog .. Felicidades

    • Edwin
    • 17/03/10

    hola una pregunta, se puede agregar una columna a cualquiera de estas tablas, es decir, un alter table add columna tipo

    saludos

    • Jim
    • 29/03/11

    perfect !

  1. Excelente artículo!
    Pero tengo el siguiente planteamiento, tengo un procedimiento almacenado con una consulta que me devuelve a los mas 5 registros de 6 campos cada registro, necesito recorrer y manipular esos 5 registros que son muy pocos y se ha considerado el uso de Variables tipo Tabla por el numero de registros, pero, este procedimiento almacenado es recursivo y a lo mas tendría 7 niveles de recursividad, ¿Qué sucede con el Scope o tiempo de vida de mi variable tipo tabla?) porque seguramente cuando se realice la primera llamada de recursividad se generará nuevamente la variable tipo tabla para el nivel 1 y así sucesivamente y conforme vaya retornando la variable se irá “eliminando”.

    Es correcto el planteamiento o es recomendable emplear tablas temporales?!

    Gracias!

    • psykrest
    • 12/10/11

    OK para el caso que me planteas no usaria Tablas temporales, mejor implementaria CTE’s, el tiempo de vida de una variable de tabla temporal es por sesion, es decir que vive mientras se este ejecutando el SP, al terminar esta se elimina automaticamente, como tip creo que puedes configurar en el SQL el nivel maximo de recursividad, debes de tener cuidado al usar esto ya que este tipo de queries al no estar bien estructurados le pueden pegar mucho al performance de tu base de datos.

    espero te sirvan de algo mis comentarios.

    Ivan…

    • sebastian
    • 11/01/12

    Muy buen articulo! me sirvio demasiado!
    por primera vez cree una variable de tabla en un sp😀

    • Isaac
    • 12/01/12

    Muy buena exposicion del tema, es la segunda vez que me paso por aqui, la primera como consulta rapida y la segunda para leer detenidamente. me ayudo bastante.

    solo un detalle, me parecia mas claro el termino “variables tipo tabla” cuando lei “viariables de tabla”, supuse una columna calculada en base otros valores y/o circunstancias, algo asi como update con triggers en runtime
    y casi salto de mi asiento a devorar con los ojos la pantalla XP.
    pero eso es solo una opinion.

  2. Que copado… me salvaste la vida master…gracias

    • Irene
    • 10/04/12

    Super post, me ha servido de mucho. Sigue asi. Bendiciones…

  3. Epa colega gracias aprendi unas cosas que no habia implementado que eran las variables de tipo tabla. Saludos

  4. Epa colega gracias no conocia las Variables de Tabla, saludos.

    • CarmenRocio
    • 15/06/12

    Hola muy bueno el blog, pero lo que si quisiera saber es que cantidad de dato puedo llenar a una variable temporal , ya que mencionas que( resultado es demasiado grande) o cuanto registro puede soportar?.

    • xavi
    • 12/07/12

    wuaw que nivel me gusta te felicito compadre tu si que sabes

    • Luis
    • 23/01/13

    Excelente, solo tengo una duda, tengo un SP (internamente tengo tablas temporales) que utilizo en un reporte SSRS que utilizan muchas personas, se puede dar el caso de mas de una persona lo ejecute al mismo momento, por ende el SP se ejecutaria varias veces al mismo instante, esto haria conflicto con que cada SP cree una tabla temporal con el mismo nombre, o no hace conflicto?
    Espero haber podido explicarme bien, Saludos.

    • Manuel Paz
    • 13/03/13

    Gran aporte hermano, gracias por tomarte el tiempo de hacerlo. Muy útil y bien explicado.

  5. Hola soy principiante hice un cursor y pero solo me muestra el ultimo registro del bucle, como hago para que me muestre cada uno?

  6. Gran aporte, confirma que lo que he estado aplicando es lo correcto, hay que tener mucho cuidado cuando se utiliza tablas variables según la cantidad de registros.

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

A %d blogueros les gusta esto: