Compartir por

Tablas temporales en un grupo de SQL dedicado en Azure Synapse Analytics

Este artículo contiene instrucciones esenciales para usar tablas temporales y resalta los principios de las tablas temporales de nivel de sesión.

El uso de la información de este artículo puede ayudarle a modularizar el código, lo que mejora tanto la reutilización como la facilidad de mantenimiento.

¿Qué son las tablas temporales?

Las tablas temporales son útiles al procesar datos, especialmente durante la transformación en las que los resultados intermedios son transitorios. En el grupo de SQL dedicado, existen tablas temporales en el nivel de sesión.

Las tablas temporales solo son visibles para la sesión en la que se crearon y se quitan automáticamente cuando se cierra esa sesión.

Las tablas temporales ofrecen una ventaja de rendimiento porque sus resultados se escriben en el almacenamiento local en lugar de en el almacenamiento remoto.

Tablas temporales en un grupo de SQL dedicado

En el recurso del grupo de SQL dedicado, las tablas temporales ofrecen una ventaja de rendimiento porque sus resultados se escriben en local en lugar de en almacenamiento remoto.

Creación de una tabla temporal

Las tablas temporales se crean mediante el prefijo del nombre de la tabla con un #. Por ejemplo:

CREATE TABLE #stats_ddl
(
    [schema_name]        NVARCHAR(128) NOT NULL
,    [table_name]            NVARCHAR(128) NOT NULL
,    [stats_name]            NVARCHAR(128) NOT NULL
,    [stats_is_filtered]     BIT           NOT NULL
,    [seq_nmbr]              BIGINT        NOT NULL
,    [two_part_name]         NVARCHAR(260) NOT NULL
,    [three_part_name]       NVARCHAR(400) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)

Las tablas temporales también se pueden crear con un CTAS mediante exactamente el mismo enfoque:

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
;

Nota:

CTAS es un comando eficaz y tiene la ventaja adicional de ser eficiente en su uso del espacio del registro de transacciones.

Eliminar tablas temporales

Cuando se crea una nueva sesión, no debe existir ninguna tabla temporal.

Si va a llamar al mismo procedimiento almacenado, lo que crea un archivo temporal con el mismo nombre, para tener la seguridad de que las instrucciones CREATE TABLE se ejecutan correctamente, se puede usar una sencilla comprobación de existencia previa con DROP, como en el ejemplo siguiente:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

Para codificar la coherencia, se recomienda usar este patrón para tablas y tablas temporales. También es recomendable usar DROP TABLE para quitar tablas temporales cuando haya terminado con ellas en el código.

En el desarrollo de procedimientos almacenados, es habitual ver los comandos drop agrupados al final de un procedimiento para asegurarse de que estos objetos se eliminen.

DROP TABLE #stats_ddl

Modularización de código

Dado que las tablas temporales se pueden ver en cualquier lugar de una sesión de usuario, esta funcionalidad se puede aprovechar para ayudarle a modular el código de la aplicación.

Por ejemplo, el siguiente procedimiento almacenado genera DDL para actualizar todas las estadísticas de la base de datos por nombre estadístico:

CREATE PROCEDURE    [dbo].[prc_sqldw_update_stats]
(   @update_type    tinyint -- 1 default 2 fullscan 3 sample 4 resample
    ,@sample_pct     tinyint
)
AS

IF @update_type NOT IN (1,2,3,4)
BEGIN;
    THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
SELECT
    CASE @update_type
    WHEN 1
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
    WHEN 2
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
    WHEN 3
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
    WHEN 4
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
    END AS [update_stats_ddl]
,   [seq_nmbr]
FROM    #stats_ddl
;
GO

En esta fase, la única acción que se ha producido es la creación de un procedimiento almacenado que genera una tabla temporal, #stats_ddl, con instrucciones DDL.

Este procedimiento almacenado quita un existente #stats_ddl para asegurarse de que no se produce un error si se ejecuta más de una vez dentro de una sesión.

Sin embargo, dado que no hay un DROP TABLE al final del procedimiento almacenado, cuando el procedimiento almacenado se completa, deja la tabla creada para que pueda ser leída fuera del procedimiento almacenado.

En el pool de SQL dedicado, a diferencia de otras bases de datos de SQL Server, es posible usar la tabla temporal fuera del procedimiento que la creó. Las tablas temporales del grupo de SQL dedicado se pueden usar en cualquier lugar dentro de la sesión. Esta característica puede dar lugar a código más modular y administrable, como en el ejemplo siguiente:

EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''

WHILE @i <= @t
BEGIN
    SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Limitaciones de tabla temporal

El grupo de SQL dedicado impone un par de limitaciones al implementar tablas temporales. Actualmente, solo se admiten tablas temporales con ámbito de sesión. No se admiten tablas temporales globales.

Además, las vistas no se pueden crear en tablas temporales. Las tablas temporales solo se pueden crear con distribución hash o round robin. No se admite la distribución de tablas temporales replicadas.

Pasos siguientes

Para más información sobre el desarrollo de tablas, consulte el artículo Diseño de tablas mediante un grupo de SQL dedicado .

  • Last updated on