Временные таблицы в выделенном пуле SQL в Azure Synapse Analytics

Статья
2025-04-05

В этой статье содержатся основные рекомендации по использованию временных таблиц и выделены принципы временных таблиц уровня сеанса.

Используя сведения в этой статье, вы можете модульизировать код, повышая удобство использования и удобство обслуживания.

Что такое временные таблицы?

Временные таблицы полезны при обработке данных, особенно во время преобразования, когда промежуточные результаты являются временными. В выделенном пуле SQL временные таблицы существуют на уровне сеанса.

Временные таблицы видны только сеансу, в котором они были созданы, и автоматически удаляются при закрытии этого сеанса.

Временные таблицы обеспечивают преимущество производительности, так как их результаты записываются в локальный, а не удаленный хранилище.

Временные таблицы в выделенном пуле SQL

В выделенном ресурсе пула SQL временные таблицы обеспечивают преимущество производительности, так как их результаты записываются в локальный, а не в удаленное хранилище.

Создание временной таблицы

Временные таблицы создаются путем добавления префикса # к имени вашей таблицы. Рассмотрим пример.

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
)

Временные таблицы также можно создать с CTAS, используя точно такой же подход.

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]
)
;

Примечание.

CTAS является мощной командой и имеет дополнительное преимущество эффективного использования пространства журнала транзакций.

Удалите временные таблицы

При создании нового сеанса временные таблицы не должны существовать.

Если вы вызываете ту же хранимую процедуру, которая создает временный объект с тем же именем, чтобы убедиться, что ваши CREATE TABLE инструкции успешно выполнены, можно использовать простую проверку существования с помощью DROP, как в следующем примере:

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

Для согласованности кода рекомендуется использовать этот шаблон как для таблиц, так и для временных таблиц. Рекомендуется использовать DROP TABLE также для удаления временных таблиц после завершения работы с ними в коде.

В разработке хранимых процедур обычно можно увидеть команды удаления объектов, сгруппированные в конце процедуры, чтобы обеспечить удаление этих объектов.

DROP TABLE #stats_ddl

Модульность кода

Так как временные таблицы можно увидеть в любом месте сеанса пользователя, эту возможность можно использовать для модульного анализа кода приложения.

Например, следующая хранимая процедура создает DDL для обновления всей статистики в базе данных по имени статистики:

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

На этом этапе единственным действием является создание хранимой процедуры, создающей временную таблицу с операторами DDL, #stats_ddl.

Эта хранимая процедура удаляет существующую #stats_ddl, чтобы избежать ошибок при повторном выполнении в течение одного сеанса.

Однако, так как в конце хранимой процедуры нет DROP TABLE , когда хранимая процедура завершается, она оставляет созданную таблицу, чтобы ее можно было прочитать вне хранимой процедуры.

В выделенном пуле SQL, в отличие от других баз данных SQL Server, можно использовать временную таблицу за пределами созданной процедуры. Временные таблицы для выделенного пула SQL можно использовать в любом месте сеанса. Эта функция может привести к более модульной и управляемой коде, как показано в следующем примере:

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;

Ограничения временной таблицы

Выделенный пул SQL накладывает несколько ограничений при реализации временных таблиц. В настоящее время поддерживаются только временные таблицы с областью сеанса. Глобальные временные таблицы не поддерживаются.

Кроме того, представления нельзя создавать во временных таблицах. Временные таблицы можно создавать только с хешированием или циклическим распределением. Реплицированное распределение временных таблиц не поддерживается.

Дальнейшие действия

Дополнительные сведения о разработке таблиц см. в статье " Проектирование таблиц с помощью выделенного пула SQL ".