適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウスMicrosoft Fabric プレビューの SQL データベース
変数は、 DECLARE ステートメントを使用してバッチまたはプロシージャの本体で宣言され、 SET または SELECT ステートメントを使用して値が割り当てられます。 このステートメントでは、他のカーソル関連のステートメントで使用できるカーソル変数を宣言できます。 宣言後、宣言の一部として値が指定されていない限り、すべての変数は NULLとして初期化されます。
Syntax
SQL Server と Azure SQL Database での構文は次のとおりです。
DECLARE
{
{ @local_variable [AS] data_type [ = value ] }
| { @cursor_variable_name CURSOR }
| { @table_variable_name [AS] <table_type_definition> }
} [ , ...n ]
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ , ...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ , ...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ , ... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ , ...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ , ...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ , ... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ , ... n ] )
}
[ WITH ( <index_option> [ , ... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
Azure Synapse Analytics、Parallel Data Warehouse、Microsoft Fabric での構文は次のとおりです。
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ , ...n ]
Arguments
@ local_variable
変数の名前。 変数名は、アット マーク (@) で始める必要があります。 ローカル変数名は、識別子の規則に従っている必要があります。
data_type
システム提供の共通言語ランタイム (CLR) ユーザー定義テーブル型または別名データ型。 変数のデータ型を text、ntext、image にすることはできません。
システム データ型の詳細については、「データ 型」を参照してください。 CLR ユーザー定義型または別名データ型の詳細については、「 CREATE TYPE」を参照してください。
= 価値
インラインで値を変数に代入します。 値には定数または式を指定できますが、変数宣言の型と同じであるか、その型に暗黙的に変換できる必要があります。 詳細については、「 式」を参照してください。
@ cursor_variable_name
カーソル変数の名前を指定します。 カーソル変数名はアット マーク (@) で始まり、識別子の規則に従っている必要があります。
CURSOR
変数がローカルのカーソル変数であることを指定します。
@ table_variable_name
table 型の変数の名前。 変数名はアット マーク (@) で始まり、識別子の規則に従っている必要があります。
<table_type_definition>
table データ型を定義します。 テーブルの定義には、列の定義、名前、データ型、制約が含まれます。 使用できる制約の種類は、
PRIMARY KEY、UNIQUE、NULL、およびCHECKのみです。 別名データ型にルールや既定の定義がバインドされている場合、別名データ型を列のスカラー データ型として使用することはできません。
<table_type_definition>
CREATE TABLEでテーブルを定義するために使用される情報のサブセット。 これには要素と必須の定義が含まれます。 詳細については、 CREATE TABLE を参照してください。
n
複数の変数を指定し、値を割り当てることができることを示すプレースホルダー。 テーブル変数を宣言するときは、
DECLAREステートメントで宣言されている唯一の変数がテーブル変数である必要があります。
column_name
テーブル内の列の名前。
scalar_data_type
列がスカラー データ型であることを指定します。
computed_column_expression
計算列の値を定義する式。 これは、同じテーブル内の他の列を使用して式から計算されます。 たとえば、計算列には定義
cost AS price * qtyを含めることができます。 式には、非計算列名、定数、組み込み関数、変数、または 1 つ以上の演算子で接続されたこれらのオプションの任意の組み合わせを指定できます。 この式はサブクエリまたはユーザー定義関数にはできません。 この式は CLR ユーザー定義型を参照できません。
[ COLLATE collation_name ]
列の照合順序を指定します。 collation_name には、Windows 照合順序名または SQL 照合順序名のいずれかを指定できます。 char、 varchar、 text、 nchar、 nvarchar、および ntext データ型の列にのみ適用できます。 指定しないと、列がユーザー定義データ型である場合はユーザー定義データ型の照合順序、または現在のデータベースの照合順序が割り当てられます。
Windows 照合順序名と SQL 照合順序名の詳細については、「 COLLATE」を参照してください。
DEFAULT
挿入の際に明示的な値を指定しない場合に、列に入力される値を指定します。
DEFAULT 定義は、 timestamp または IDENTITY プロパティで定義された列を除き、任意の列に適用できます。
DEFAULT 定義は、テーブルが削除されると削除されます。 文字列などの定数値のみ。 SYSTEM_USER()などのシステム関数、または NULL を既定として使用できます。 以前のバージョンの SQL Server との互換性を維持するために、制約名を DEFAULTに割り当てることができます。
constant_expression
列の既定値として使用される定数、
NULL、またはシステム関数。
IDENTITY
新しい列が ID 列であることを指定します。 テーブルに行が新しく追加されると、SQL Server では列に一意な増分値が設定されます。 ID 列は、テーブルの一意の行識別子として機能するために、 PRIMARY KEY 制約で一般的に使用されます。
IDENTITY プロパティは、tinyint、smallint、int、decimal(p,0)、または numeric(p,0) 列に割り当てることができます。 ID 列は 1 つのテーブルにつき 1 つだけ作成できます。 バインドされた既定値と DEFAULT 制約は、ID 列では使用できません。 seed と increment は、両方を指定するか、どちらも指定しないでください。 どちらも指定しないときの既定値は (1,1) です。
seed
テーブルに読み込まれる先頭行で使用される値。
increment
既に読み込まれている前の行の ID 値に加算される増分値。
ROWGUIDCOL
新しい列が行グローバル一意識別子列であることを指定します。
ROWGUIDCOL列として指定できるのは、テーブルごとに 1 つの uniqueidentifier 列だけです。
ROWGUIDCOL プロパティは、uniqueidentifier 列にのみ割り当てることができます。
NULL |NULL でない
変数で NULL 値が許可されるかどうかを示します。 既定値は NULLです。
主キー
特定の 1 つ以上の一意なインデックスによって列にエンティティの整合性を強制する制約。 テーブルごとに作成できる PRIMARY KEY 制約は 1 つだけです。
UNIQUE
一意なインデックスによって、特定の 1 つ以上の列に対してエンティティの整合性を設定する制約。 テーブルには複数の UNIQUE 制約を含めることができます。
クラスタ化 |非クラスタ化
PRIMARY KEY制約またはUNIQUE制約に対してクラスター化インデックスまたは非クラスター化インデックスが作成されることを示します。
PRIMARY KEY 制約では CLUSTEREDが使用され、 UNIQUE 制約では NONCLUSTEREDが使用されます。
CLUSTERED は、1 つの制約にのみ指定できます。
UNIQUE制約にCLUSTEREDが指定されていて、PRIMARY KEY制約も指定されている場合、PRIMARY KEYはNONCLUSTEREDを使用します。
CHECK
1 つ以上の列に入力できる値を制限することによってドメインの整合性を設定する制約。
logical_expression
TRUEまたはFALSEを返す論理式。
<index_option>
1 つ以上のインデックス オプションを指定します。 table 変数でインデックスを明示的に作成することはできません。table 変数では統計が保持されません。 SQL Server 2014 (12.x) では、テーブル定義を使用して特定のインデックスの種類をインラインで作成できる構文が導入されました。 この構文を使用すると、テーブル定義の一部としてテーブル変数にインデックスを作成できます。 場合によっては、インデックスの完全なサポートと統計を提供する一時テーブルを代わりに使用することで、パフォーマンスが向上することがあります。
これらのオプションの詳細な説明については、CREATE TABLE に関する記事をご覧ください。
テーブル変数と行の推定
table 変数には配布の統計情報がありません。 多くの場合、オプティマイザーは、テーブル変数に 0 行または 1 行が含まれているという前提でクエリ プランを作成します。 詳細については、テーブルのデータ型の「制限事項と制約事項」を参照してください。
このため、多数の行 (100 行を超える行) を使用する可能性がある場合は、テーブル変数を慎重に使用する必要があります。 次の代替手段を検討してください。
行数が (100 より大きい) 可能性がある場合、一時テーブルはテーブル変数よりも優れたソリューションになる可能性があります。
テーブル変数を他のテーブルと結合するクエリの場合は、
RECOMPILEヒントを使用します。これにより、オプティマイザーはテーブル変数の正しいカーディナリティを使用します。Azure SQL Database および SQL Server 2019 (15.x) 以降では、テーブル変数遅延コンパイル機能によって、実際のテーブル変数の行数に基づくカーディナリティ推定が伝達され、実行プランを最適化するためのより正確な行数が提供されます。 詳細については、SQL データベースでのインテリジェントなクエリ処理に関する記事を参照してください。
Remarks
変数は、多くの場合、 WHILE、 LOOP、または IF...ELSE ブロックのカウンターとしてバッチまたはプロシージャで使用されます。
変数は式の内部だけで使用できます。オブジェクト名やキーワードの代わりに使用することはできません。 動的 SQL ステートメントを作成するには、 EXECUTEを使用します。
ローカル変数のスコープは、その変数が宣言されるバッチです。
テーブル変数は、必ずしもメモリ常駐ではありません。 メモリ不足の場合、テーブル変数に属するページは tempdb に移動します。
テーブル変数でインライン インデックスを定義できます。
現在カーソルが割り当てられているカーソル変数は、次のステートメントの中でソースとして参照できます。
-
CLOSE陳述 -
DEALLOCATE陳述 -
FETCH陳述 -
OPEN陳述 - positioned
DELETEステートメントまたはUPDATEステートメント -
SET CURSORvariable ステートメント (右側)
どのステートメントの場合も、参照されるカーソル変数は存在するが、現在カーソルが割り当てられていない場合は、SQL Server でエラーが発生します。 参照されているカーソル変数が存在しない場合、SQL Server では、宣言されていない別の型の変数に対して発生するエラーと同じエラーが発生します。
カーソル変数には、次の特徴があります。
カーソルの種類または別のカーソル変数の対象になります。 詳細については、「SET @local_variable」を参照してください。
カーソル変数に現在カーソルが割り当てられない場合は、
EXECUTEステートメントで出力カーソル パラメーターのターゲットとして参照できます。カーソルへのポインターとして扱われます。
Examples
この記事のコード サンプルでは、microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできるAdventureWorks2022またはAdventureWorksDW2022サンプル データベースを使用します。
A. DECLARE を使用する
次の例では、 @find という名前のローカル変数を使用して、 Manで始まるすべてのファミリ名の連絡先情報を取得します。
USE AdventureWorks2022;
GO
DECLARE @find AS VARCHAR (30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName,
p.FirstName,
ph.PhoneNumber
FROM Person.Person AS p
INNER JOIN Person.PersonPhone AS ph
ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
結果セットは次のとおりです。
LastName FirstName Phone
------------------- ----------------------- -------------------------
Manchepalli Ajay 1 (11) 500 555-0174
Manek Parul 1 (11) 500 555-0146
Manzanares Tomas 1 (11) 500 555-0178
B. 2 つの変数で DECLARE を使用する
次の例では、北米販売区域に勤務しており、年間売上高が $2,000,000 以上である Adventure Works Cycles 販売担当者の名前を取得します。
USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group AS NVARCHAR (50), @Sales AS MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName,
LastName,
SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group
AND SalesYTD >= @Sales;
C. テーブル型の変数を宣言する
次の例では、UPDATE ステートメントのOUTPUT句で指定された値を格納するtable変数を作成します。 この後に、SELECT 内の値、および @MyTableVar テーブルの更新操作の結果を返す 2 つの Employee ステートメントが続きます。
INSERTED.ModifiedDate 列の結果は、ModifiedDate テーブルの Employee 列の値と異なります。 これは、AFTER UPDATE の値を現在の日付に更新する ModifiedDate トリガーが、Employee テーブルで定義されるためです。 ただし、OUTPUT が返す列には、トリガーが起動される前の値が反映されています。 詳細については、 OUTPUT 句を参照してください。
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID,
OldVacationHours,
NewVacationHours,
ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID,
VacationHours,
ModifiedDate
FROM HumanResources.Employee;
GO
D. インライン インデックスを使用してテーブル型の変数を宣言する
次の例では、1 つのクラスター化インライン インデックスと 2 つの非クラスター化インライン インデックスを持つ table 変数を作成します。
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID));
GO
次のクエリは、前のクエリで作成されたインデックスに関する情報を返します。
SELECT * FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. ユーザー定義テーブル型の変数を宣言する
次の例では、@LocationTVP というテーブル値パラメーターまたはテーブル変数を作成します。 この手順では、 LocationTableTypeと呼ばれる、対応するユーザー定義テーブル型が必要です。
ユーザー定義テーブル型を作成する方法の詳細については、「 CREATE TYPE」を参照してください。 テーブル値パラメーターの詳細については、「テーブル値パラメーターの 使用 (データベース エンジン)」を参照してください。
DECLARE @LocationTVP AS LocationTableType;
例: Azure Synapse Analytics、Analytics Platform System (PDW)
F. DECLARE を使用する
次の例では、 @find という名前のローカル変数を使用して、 Waltで始まるすべてのファミリ名の連絡先情報を取得します。
-- Uses AdventureWorks
DECLARE @find AS VARCHAR (30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName,
FirstName,
Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. 2 つの変数で DECLARE を使用する
次の例では、変数を使用して、 DimEmployee テーブル内の従業員の姓と家族名を指定します。
DECLARE @lastName AS VARCHAR (30),
@firstName AS VARCHAR (30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName,
FirstName,
Phone
FROM DimEmployee
WHERE LastName LIKE @lastName
AND FirstName LIKE @firstName;