Временные таблицы в Synapse SQL

Статья
2025-04-02

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

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

Временные таблицы

Временные таблицы полезны при обработке данных, особенно во время преобразования, когда промежуточные результаты являются временными. При использовании Synapse 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

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

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

В отличие от других баз данных SQL Server, Synapse SQL позволяет использовать временную таблицу за пределами созданной процедуры. Временные таблицы, созданные с помощью выделенного пула 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

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

Например, невозможно присоединить временную таблицу к данным из файлов в хранилище. Максимальное число временных таблиц — 100, а их максимальный общий размер — 100 МБ.

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

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

Поделиться через

Временные таблицы в Synapse SQL