DECLARE @local_variable (Transact-SQL)

[アーティクル]
05/23/2023

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric Warehouse Microsoft Fabric SQL Database

変数は、バッチやプロシージャの中で DECLARE ステートメントを使用して宣言し、SET ステートメントまたは SELECT ステートメントを使用して値を割り当てます。このステートメントでは、他のカーソル関連のステートメントで使用できるカーソル変数を宣言できます。宣言の一部として値を指定しない場合、宣言の後、すべての変数は NULL として初期化されます。

Transact-SQL 構文表記規則

構文

SQL Server と Azure SQL Database での構文は次のとおりです。

DECLARE
{
  { @local_variable [AS] data_type [ = value ] }
  | { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }

<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 ]

引数

@local_variable

変数の名前。変数名は、アットマーク (@) で始める必要があります。ローカル変数名は、識別子の規則に従っている必要があります。

data_type
システム提供の共通言語ランタイム (CLR) ユーザー定義テーブル型または別名データ型。変数のデータ型を text、ntext、image にすることはできません。

システムデータ型の詳細については、「データ型 (Transact-SQL)」を参照してください。 CLR ユーザー定義型または別名データ型の詳細については、「CREATE TYPE (Transact-SQL)」を参照してください。

= value
インラインで値を変数に代入します。値には定数または式を指定できますが、変数宣言の型と同じであるか、その型に暗黙的に変換できる必要があります。詳細については、「式 (Transact-SQL)」を参照してください。

@cursor_variable_name

カーソル変数の名前を指定します。カーソル変数名はアットマーク (@) で始まり、識別子の規則に従っている必要があります。

CURSOR
変数がローカルのカーソル変数であることを指定します。

@table_variable_name
table 型の変数の名前。変数名はアットマーク (@) で始まり、識別子の規則に従っている必要があります。

<table_type_definition>
table データ型を定義します。テーブルの定義には、列の定義、名前、データ型、制約が含まれます。指定できる制約の種類は、PRIMARY KEY、UNIQUE、NULL、CHECK のみです。別名データ型にルールや既定の定義がバインドされている場合、別名データ型を列のスカラーデータ型として使用することはできません。

<table_type_definition>

CREATE TABLE でテーブルを定義するために使用する情報のサブセット。これには要素と必須の定義が含まれます。詳細については、「 CREATE TABLE (Transact-SQL)」を参照してください。

n
複数の変数を指定し、値を割り当てることができることを示すプレースホルダー。 table 変数を宣言する場合、1 つの DECLARE ステートメント内に table 変数以外の変数を定義することはできません。

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 (Transact-SQL) に関するページを参照してください。

DEFAULT

挿入の際に明示的な値を指定しない場合に、列に入力される値を指定します。 DEFAULT 定義は、timestamp として定義された列または IDENTITY プロパティを持つ列以外のすべての列に適用できます。テーブルが削除されると、DEFAULT 定義は削除されます。既定値として使用できるのは、文字列などの定数値、SYSTEM_USER() などのシステム関数、または NULL だけです。 SQL Server の旧バージョンとの互換性を保つため、DEFAULT に制約名を割り当てることができます。

constant_expression
列の既定値として使用される定数、NULL、またはシステム関数。

IDENTITY

新しい列が ID 列であることを指定します。テーブルに行が新しく追加されると、SQL Server では列に一意な増分値が設定されます。 ID 列は、通常、PRIMARY KEY 制約と共に使用され、テーブルの一意な行識別子 (ROWID) の役割を果たします。 IDENTITY プロパティは、tinyint、smallint、int、decimal(p,0) 、numeric(p,0) のいずれかの列に割り当てることができます。 ID 列は 1 つのテーブルにつき 1 つだけ作成できます。バインドされた既定値および DEFAULT 制約を ID 列と共に使用することはできません。 seed と increment は、両方を指定するか、どちらも指定しないでください。どちらも指定しないときの既定値は (1,1) です。

seed
テーブルに読み込まれる先頭行で使用される値。

increment
既に読み込まれている前の行の ID 値に加算される増分値。

ROWGUIDCOL

新しい列が行グローバル一意識別子列であることを指定します。 1 つのテーブルにつき、1 つの uniqueidentifier 列だけを ROWGUIDCOL 列に指定できます。 ROWGUIDCOL プロパティは uniqueidentifier 列にだけ割り当てることができます。

NULL | NOT NULL

変数で NULL 値が許可されるかどうかを示します。既定値は NULL です。

PRIMARY KEY

特定の 1 つ以上の一意なインデックスによって列にエンティティの整合性を強制する制約。 PRIMARY KEY 制約は、1 つのテーブルにつき 1 つだけ作成できます。

UNIQUE

一意なインデックスによって、特定の 1 つ以上の列に対してエンティティの整合性を設定する制約。 1 つのテーブルには複数の UNIQUE 制約を指定できます。

CLUSTERED | NONCLUSTERED

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 SQL Server 2014 (12.x) 以降では、特定のインデックスの種類をテーブル定義にインライン作成できる新しい構文が導入されました。この新しい構文を使うと、テーブル定義の一部としてテーブル変数にインデックスを作成できます。場合によっては、完全なインデックスのサポートと統計を提供する一時テーブルを使用した方が、パフォーマンスが向上する場合があります。

これらのオプションの詳細な説明については、CREATE TABLE に関する記事をご覧ください。

テーブル変数と行の推定

table 変数には配布の統計情報がありません。多くの場合、オプティマイザーは、テーブル変数がゼロ行または 1 行であることを前提としてクエリプランを構築します。詳細については、テーブルのデータ型の「制限事項と制約事項」を参照してください。

このため、多数の行 (100 行を超える行) を使用する可能性がある場合は、テーブル変数を慎重に使用する必要があります。次の代替手段を検討してください。

行数が多くなる (100 行を超える) 可能性がある場合は、テーブル変数ではなく一時テーブルの方が適したソリューションになる場合があります。
テーブル変数を他のテーブルに結合するクエリの場合は、RECOMPILE ヒントを使用します。このヒントを使用すると、オプティマイザーがテーブル変数に適切なカーディナリティを使用するようになります。
Azure SQL Database と SQL Server 2019 (15.x) 以降では、テーブル変数の遅延コンパイル機能により、実際のテーブル変数の行数に基づくカーディナリティ推定値が伝達され、実行プランを最適化するためのより正確な行数が提供されます。詳細については、SQL データベースでのインテリジェントなクエリ処理に関する記事を参照してください。

解説

変数は、バッチやプロシージャの中で、WHILE、LOOP、または IF...ELSE ブロックのカウンターとして使用される場合があります。

変数は式の内部だけで使用できます。オブジェクト名やキーワードの代わりに使用することはできません。動的 SQL ステートメントを作成するには、EXECUTE を使用します。

ローカル変数のスコープは、その変数が宣言されるバッチです。

テーブル変数は、必ずしもメモリ常駐ではありません。メモリ不足の場合、テーブル変数に属するページは tempdb に移動します。

テーブル変数でインラインインデックスを定義できます。

現在カーソルが割り当てられているカーソル変数は、次のステートメントの中でソースとして参照できます。

CLOSE ステートメント
DEALLOCATE ステートメント
FETCH ステートメント
OPEN ステートメント
位置指定の DELETE または UPDATE ステートメント
SET CURSOR 変数ステートメント (右側)

どのステートメントの場合も、参照されるカーソル変数は存在するが、現在カーソルが割り当てられていない場合は、SQL Server でエラーが発生します。参照されているカーソル変数が存在しない場合、SQL Server では、宣言されていない別の型の変数に対して発生するエラーと同じエラーが発生します。

カーソル変数には、次の特徴があります。

カーソルの種類または別のカーソル変数の対象になります。詳細については、「SET @local_variable (Transact-SQL)」を参照してください。
カーソル変数に現在カーソルが割り当てられていない場合、EXECUTE ステートメント内の出力カーソルパラメーターの対象として参照できます。
カーソルへのポインターとして扱われます。

例

A. DECLARE を使用する

次の例では、@find という名前のローカル変数を使って、Man で始まるすべての姓の連絡先情報を取得します。

USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
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

(3 row(s) affected)

B. DECLARE で 2 つの変数を使用する

次の例では、北米販売区域に勤務しており、年間売上高が $2,000,000 以上である Adventure Works Cycles 販売担当者の名前を取得します。

USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales 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. table 型の変数を宣言する

次の例では、UPDATE ステートメントの OUTPUT 句で指定される値を格納する table 変数を作成します。この後に、SELECT 内の値、および @MyTableVar テーブルの更新操作の結果を返す 2 つの Employee ステートメントが続きます。 INSERTED.ModifiedDate 列の結果は、Employee テーブルの ModifiedDate 列の値と異なります。これは、AFTER UPDATE の値を現在の日付に更新する ModifiedDate トリガーが、Employee テーブルで定義されるためです。ただし、OUTPUT が返す列には、トリガーが起動される前の値が反映されています。詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。

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 (Transact-SQL)」を参照してください。テーブル値パラメーターの詳細については、「テーブル値パラメーターの使用 (データベースエンジン)」を参照してください。

DECLARE @LocationTVP
AS LocationTableType;

例: Azure Synapse Analytics、Analytics Platform System (PDW)

F. DECLARE を使用する

次の例では、@find という名前のローカル変数を使って、Walt で始まるすべての姓の連絡先情報を取得します。

-- Uses AdventureWorks

DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;

G. DECLARE で 2 つの変数を使用する

次の例では、変数を使用し、DimEmployee テーブルにある従業員の名と姓を指定します。

-- Uses AdventureWorks

DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);

SET @lastName = 'Walt%';
SET @firstName = 'Bryan';

SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;

次の方法で共有

DECLARE @local_variable (Transact-SQL)

構文

引数

@local_variable

@cursor_variable_name

<table_type_definition>

column_name

[ COLLATE collation_name ]

DEFAULT

IDENTITY

ROWGUIDCOL

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

<index_option>

テーブル変数と行の推定

解説

例

A. DECLARE を使用する

B. DECLARE で 2 つの変数を使用する

C. table 型の変数を宣言する

D. インラインインデックスを使用してテーブル型の変数を宣言する

E. ユーザー定義テーブル型の変数を宣言する

例: Azure Synapse Analytics、Analytics Platform System (PDW)

F. DECLARE を使用する

G. DECLARE で 2 つの変数を使用する

関連項目

フィードバック

その他のリソース

次の方法で共有

DECLARE @local_variable (Transact-SQL)

構文

引数

@local_variable

@cursor_variable_name

<table_type_definition>

column_name

[ COLLATE collation_name ]

DEFAULT

IDENTITY

ROWGUIDCOL

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

<index_option>

テーブル変数と行の推定

解説

例

A. DECLARE を使用する

B. DECLARE で 2 つの変数を使用する

C. table 型の変数を宣言する

D. インライン インデックスを使用してテーブル型の変数を宣言する

E. ユーザー定義テーブル型の変数を宣言する

例: Azure Synapse Analytics、Analytics Platform System (PDW)

F. DECLARE を使用する

G. DECLARE で 2 つの変数を使用する

関連項目

フィードバック

その他のリソース

D. インラインインデックスを使用してテーブル型の変数を宣言する