Un Mundo Bizarro: 14 dic 2010

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.

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 temporalesLocales. 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 ejecutandoDROP 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:

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:

Y para el segundo select:

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.