テーブル型変数の利用 - SQL Server

結果セットを格納できる、テーブル型変数について紹介します。

概要

SQL Serverでは、結果のレコードセットを格納できるテーブル型変数を宣言して利用できます。
この記事では、テーブル型変数の利用方法を紹介します。

書式

テーブル変数の宣言は次の書式です。

記述例

DECLARE @myitem TABLE(
 id int
 ,name varchar(16)
 ,price int
)

テーブル変数の操作

作成したテーブル変数のレコードの挿入や更新は通常のテーブルと同じ操作で実行できます。
以下の記事を参照してください。

• レコードの挿入
• レコードの更新
• レコードの取得
• レコードの削除

例1

次のSQL文を実行します。

DECLARE @myitem TABLE(
 id int IDENTITY(1,1) NOT NULL
 ,name varchar(16)
 ,price int
)

INSERT INTO @myitem (name,price) VALUES('しろくまアイス',330)
INSERT INTO @myitem (name,price) VALUES('かるがもサブレ',160)

SELECT * FROM @myitem

解説

DECLARE でテーブル変数型の変数@myitemを宣言します。

DECLARE @myitem TABLE(
 id int IDENTITY(1,1) NOT NULL
 ,name varchar(16)
 ,price int
)

INSERT文を実行し、@myitem 変数にレコードを挿入します。

INSERT INTO @myitem (name,price) VALUES('しろくまアイス',330)
INSERT INTO @myitem (name,price) VALUES('かるがもサブレ',160)

SELECT文で @myitem 変数の内容を表示します。

SELECT * FROM @myitem

実行結果

SQL文を実行すると、以下の結果となります。

id	name	price
1	しろくまアイス	330
2	かるがもサブレ	160

例2

次のテーブルを準備します。

Products テーブル

id	model	name	class	category	price
1	C-XM01	モーダンチェア	ホーム	チェア	56000
2	X-XD05	ラージデスク	オフィス	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	ホーム	チェア	28000
4	O-XX100	ナチュラルオフィス	オフィス	チェア	13800
5	R-D400	ラウンドダイニングテーブル	ホーム	テーブル	128000
6	R7000	ウッドキャビネット	オフィス	その他	32000
7	B-200	リネンベッド	ホーム	ベッド	184500
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
9	W-80	ワーキングチェア	オフィス	チェア	45000
10	EG-10X	エルゴノミクスデスク	オフィス	テーブル	88500
11	OC-908	オーガニックチェア	ホーム	チェア	56000

次のSQL文を実行します。

DECLARE @choice TABLE(
 cid int
)

INSERT INTO @choice (cid) VALUES(4),(6),(8),(10)

SELECT * FROM Products where id IN @choice 

解説

DECLARE でテーブル変数型の変数@choiceを宣言します。

DECLARE @choice TABLE(
 cid int
)

INSERT文を実行し、@choice 変数にレコードを挿入します。

INSERT INTO @choice (cid) VALUES(4),(6),(8),(10)

Productsテーブルから、@choice 変数に挿入した値のidのレコードを取り出します。

SELECT * FROM Products where id IN @choice 

実行結果

SQLを実行すると以下の結果となります。
@choice 変数に設定した id=4, 6, 8, 10 のレコードが選択されます。

id	model	name	class	category	price
4	O-XX100	ナチュラルオフィス	オフィス	チェア	13800
6	R7000	ウッドキャビネット	オフィス	その他	32000
8	B-250	ホワイトダブルベッド	ホーム	ベッド	324850
10	EG-10X	エルゴノミクスデスク	オフィス	テーブル	88500

一時テーブルとの違い

似た機能として、一時テーブルがありますが、テーブル型変数は変数のため、 テーブル型変数を宣言した、バッチ、関数、ストアドプロシージャー内でのみ有効です。バッチや関数、ストアドプロシージャーの終了時にテーブル型変数は破棄されます。
また、テーブル変数では大きなテーブルや複雑なクエリではパフォーマンスが低下する場合があるため、 複雑なテーブルや処理を一時的に使用したい場合は、一時テーブルを使用したほうがパフォーマンスが良くなります。
テーブルのサイズが小さい場合や繰り返し再利用しない場合は、テーブル変数のほうが適しています。