Azure Synapse Analytics の専用 SQL プール内の一時テーブル

Tip

Microsoft Fabric Data Warehouse は、将来のアーキテクチャ、組み込みの AI、および新機能を備えた、Data Lake 基盤上のエンタープライズ規模のリレーショナルウェアハウスです。データウェアハウスを初めて使用する場合は、Fabric Data Warehouseから始めます。既存の dedicated SQL プールワークロードは、Fabric にアップグレードして、データサイエンス、リアルタイム分析、レポートの新機能にアクセスできます。

Fabric無料試用版を開始します。
Fabric Data Warehouse 用マイグレーションアシスタント

この記事では、一時テーブルを使用するための基本的なガイダンスについて説明し、セッションレベルの一時テーブルの原則について説明します。

この記事の情報を使用すると、コードをモジュール化し、再利用性とメンテナンスの容易さの両方を向上させることができます。

一時テーブルとは

特に、中間結果が一時的なものである変換中にデータを処理する場合に、一時テーブルが役立ちます。専用 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 プールを使用したテーブルの設計に関する記事を参照してください。

フィードバック

このページはお役に立ちましたか?

Last updated on 2026-05-03