一人アドカレ
| 日 | ポスト |
|---|---|
| 1 | 一時テーブルが存在していたら削除する |
| 2 | 何度も使う中間テーブルに名前をつけてクエリを見やすくする |
| 3 | 共通テーブル式と一時テーブルの参照可能な範囲の違い |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
| 15 | |
| 16 | |
| 17 | |
| 18 | |
| 19 | |
| 20 | |
| 21 | |
| 22 | |
| 23 | |
| 24 | |
| 25 |
共通テーブル式と一時テーブルの参照可能な範囲の違い
課題
WITH句による共通テーブル式と一時テーブルの参照可能な範囲を調べる
準備
実行環境
- SQL Server 2017 Standard
- 2008 R2 Express, 2012 Standardでも確認した
- SQL Server Management Studio 2017
結果
共通テーブル式
-- WITH句による共通テーブル式はその後に続くSELECT文でのみ参照が可能.
WITH [commonTableName] AS (
SELECT [hoge] AS [column1]
, [fuga] AS [column2]
FROM [someTable]
WHERE [piyo] = 'some-text' )
SELECT [column1]
, [column2]
FROM [commonTableName];
-- WITH句が記載されていないステートメントで参照しようとしてもエラーとなる.
SELECT *
FROM [commonTableName];
一時テーブル
/* 一時テーブル名はテーブル名の先頭にシャープがつく.
* 一時テーブルは同じセッションの中であり、かつ作成したあとであれば
* CREATE TABLE文前でも参照が可能.
* (Management Studio上でエラーの赤波線が出たり、クエリ読む人が混乱したりするので避けたい)
*/
SELECT *
FROM [#temporaryTableName];
-- 一時テーブルの作成.
CREATE TABLE [#temporaryTableName] (
[id] INT NOT NULL
, [name] VARCHAR(15) NULL );
INSERT [#temporaryTableName] ( [id], [name] )
VALUES ( 1, 'kapibarasan' )
, ( 2, 'howaitosan' )
, ( 3, 'kapijii' );
-- CREATE TABLE文のあとなので問題なく参照可能
-- （Management Studioでもエラー表示されないためスッキリ）.
SELECT *
FROM [#temporaryTableName];
クエリ中のコメントに書いてあるので重複となりますが、共通テーブル式はWITH句に続く文中でのみ参照可能であるのに対し、一時テーブルは同一セッション中であれば複数の文で参照可能です。
この特徴から私は、ある一文でのみ中間テーブルが必要な場合は共通テーブル式を使い、複数の文中で同じ内容の中間テーブルが必要な時は一時テーブルを使っています。なお、一時テーブルを使う際は同時に『一時テーブルが存在していたら削除する』で記載した内容も利用し、いちいち一時テーブルを削除する手間を省いています。
注意
ここでいう一時テーブルはローカル一時テーブルです。
Transact-SQLにはグローバル一時テーブルというのもあります。
私は全然使いませんので何も言える立場にありませんので、こちらをご確認ください。
感想
自分の中の中間テーブルの使い方について言葉にできたのでスッキリ。一度言葉にできると今後の方針もブレずに済みそう。