この記事では、一時テーブルを使用するための基本的なガイダンスについて説明し、セッション レベルの一時テーブルの原則について説明します。
この記事の情報を使用すると、コードをモジュール化し、再利用性とメンテナンスの容易さの両方を向上させることができます。
一時テーブルとは
特に、中間結果が一時的なものである変換中にデータを処理する場合に、一時テーブルが役立ちます。 専用 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 プールを使用したテーブルの設計 に関する記事を参照してください。