DECLARE @local_variable(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics분석 플랫폼 시스템(PDW)Microsoft Fabric 의 SQL 분석 엔드포인트Microsoft Fabric의 웨어하우스Microsoft Fabric 미리 보기의 SQL 데이터베이스

변수는 문과 함께 DECLARE 일괄 처리 또는 프로시저의 본문에 선언되며 a 또는 SELECT 문을 사용하여 값이 SET 할당됩니다. 이 문을 사용하여 커서 변수를 선언하고 다른 커서 관련 문과 함께 사용할 수 있습니다. 선언 후에는 값이 선언의 일부로 제공되지 않는 한 모든 변수가 초기화 NULL됩니다.

Transact-SQL 구문 표기 규칙

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 및 병렬 데이터 웨어하우스 및 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 형식의 변수 이름입니다. 변수 이름은 at 기호(@)로 시작해야 하며 식별자 규칙을 따라야 합니다.

• <table_type_definition>

  table 데이터 형식을 정의합니다. 테이블 선언에는 열 정의, 이름, 데이터 형식 및 제약 조건 등이 포함되며 허용되는 유일한 제약 조건 형식은 PRIMARY KEY, UNIQUE및 NULLCHECK. 규칙 또는 기본 정의가 형식에 바인딩되어 있는 경우 별칭 데이터 형식을 열 스칼라 데이터 형식으로 사용할 수 없습니다.

<table_type_definition>

에서 테이블을 CREATE TABLE정의하는 데 사용되는 정보의 하위 집합입니다. 여러 요소와 주요 정의가 여기에 포함됩니다. 자세한 내용은 CREATE TABLE을 참조하세요.

• n

  여러 변수를 지정하여 값을 할당할 수 있음을 나타내는 자리 표시자입니다. 테이블 변수를 선언할 때 테이블 변수는 문에서 DECLARE 선언되는 유일한 변수여야 합니다.

column_name

테이블의 열 이름입니다.

• scalar_data_type

  열에 스칼라 데이터 형식을 지정합니다.

• computed_column_expression

  계산 열의 값을 정의하는 식입니다. 동일한 테이블의 다른 열을 사용하여 식에서 계산됩니다. 예를 들어 계산 열에는 정의 cost AS price * qty가 있을 수 있습니다. 식은 비컴퓨트 열 이름, 상수, 기본 제공 함수, 변수 또는 하나 이상의 연산자가 연결된 이러한 옵션의 조합일 수 있습니다. 식은 하위 쿼리 또는 사용자 정의 함수가 될 수 없습니다. 식은 CLR 사용자 정의 데이터 형식을 참조할 수 없습니다.

[ COLLATE collation_name ]

열에 대한 데이터 정렬을 지정합니다. collation_name Windows 데이터 정렬 이름 또는 SQL 데이터 정렬 이름일 수 있으며 char, varchar, text, nchar, nvarchar 및 ntext 데이터 형식의 열에만 적용됩니다. 지정하지 않은 경우 열이 사용자 정의 데이터 형식이면 사용자 정의 데이터 형식의 데이터 정렬에 열이 할당되고 그렇지 않은 경우에는 현재 데이터베이스의 데이터 정렬에 할당됩니다.

Windows 및 SQL 데이터 정렬 이름에 대한 자세한 내용은 COLLATE를 참조하세요.

DEFAULT

삽입 중에 값이 명시적으로 지정되지 않은 경우에 열에 대해 제공되는 값을 지정합니다. DEFAULT 정의는 타임스탬프로 정의되거나 속성으로 정의된 열을 제외한 모든 열에 IDENTITY 적용할 수 있습니다. DEFAULT 테이블이 삭제되면 정의가 제거됩니다. 문자열과 같은 상수 값만 와 같은 시스템 함수 또는 SYSTEM_USER()NULL 기본값으로 사용할 수 있습니다. 이전 버전의 SQL Server와의 호환성을 유지하기 위해 제약 조건 이름을 에 할당 DEFAULT할 수 있습니다.

• constant_expression

  열의 기본값으로 사용되는 상수 NULL또는 시스템 함수입니다.

IDENTITY

새 열이 ID 열임을 나타냅니다. 테이블에 새 행이 추가되면 SQL Server는 열에 사용할 고유한 증가값을 제공합니다. ID 열은 일반적으로 제약 조건과 함께 PRIMARY KEY 테이블의 고유한 행 식별자로 사용됩니다. 이 속성은 IDENTITYtinyint, smallint, int, decimal(p,0) 또는 numeric(p,0) 열에 할당할 수 있습니다. ID 열은 테이블당 하나만 만들 수 있습니다. 바인딩된 기본값 및 DEFAULT 제약 조건은 ID 열과 함께 사용할 수 없습니다. 초기값과 증가값을 모두 지정하거나 모두 지정하지 않아야 합니다. 둘 다 지정하지 않은 경우에는 기본값 (1,1)이 사용됩니다.

• seed

  테이블에 로드된 첫 번째 행에 사용되는 값입니다.

• increment

  이전에 로드된 행의 ID 값에 추가되는 증가값입니다.

ROWGUIDCOL

새 열이 행 전역 고유 식별자 열임을 나타냅니다. 테이블당 하나의 uniqueidentifier 열만 열로 ROWGUIDCOL 지정할 수 있습니다. 이 속성은 ROWGUIDCOLuniqueidentifier 열에만 할당할 수 있습니다.

NULL | NOT NULL

변수에 null이 허용되는지 여부를 나타냅니다. 기본값은 NULL입니다.

기본 키

고유 인덱스를 통해 지정된 열 또는 여러 열에 엔터티 무결성을 적용하는 제약 조건입니다. 테이블당 하나의 PRIMARY KEY 제약 조건만 만들 수 있습니다.

UNIQUE

고유 인덱스를 통해 지정된 열 또는 여러 열에 엔터티 무결성을 제공하는 제약 조건입니다. 테이블에는 여러 UNIQUE 제약 조건이 있을 수 있습니다.

CLUSTERED | 비클러스터형

또는 UNIQUE 제약 조건에 대해 클러스터형 또는 비클러스터형 인덱스가 PRIMARY KEY 생성되었음을 나타냅니다. PRIMARY KEY 제약 조건은 사용 CLUSTERED하며 UNIQUE 제약 조건은 .를 사용합니다 NONCLUSTERED.

CLUSTERED 는 하나의 제약 조건에 대해서만 지정할 수 있습니다. 제약 조건에 대해 UNIQUE 지정되고 PRIMARY KEY 제약 조건도 지정된 PRIMARY KEYNONCLUSTERED경우 CLUSTERED .

CHECK

열에 입력 가능한 값을 제한하여 도메인 무결성을 적용하는 제약 조건입니다.

• logical_expression

  반환하거나 FALSE.를 반환 TRUE 하는 논리 식입니다.

<index_option>

하나 이상의 인덱스 옵션을 지정합니다. 명시적으로 테이블 변수에 대한 인덱스를 만들 수 없으며 테이블 변수에 대한 통계는 유지되지 않습니다. SQL Server 2014(12.x)에는 테이블 정의와 함께 인라인으로 특정 인덱스 형식을 만들 수 있는 구문이 도입되었습니다. 이 구문을 사용하여 테이블 정의의 일부로 테이블 변수에 대한 인덱스를 만들 수 있습니다. 경우에 따라 전체 인덱스 지원 및 통계를 제공하는 임시 테이블을 대신 사용하여 성능이 향상될 수 있습니다.

이러한 옵션에 대한 자세한 설명은 CREATE TABLE을 참조하세요.

테이블 변수 및 행 예측

테이블 변수에는 배포 통계가 없습니다. 대부분의 경우 최적화 프로그램은 테이블 변수에 행이 0개 또는 행 1개라는 가정하에 쿼리 계획을 작성합니다. 자세한 내용을 알고 싶다면 테이블 데이터 형식 - 제한 사항을 검토하세요.

이러한 이유로, 많은 수의 행(100개 이상)을 예상하는 경우 테이블 변수를 사용할 때 주의해야 합니다. 다음과 같은 대체 방법을 고려해 보세요.

• 임시 테이블은 행 개수가 100보다 클 수 있는 경우 테이블 변수보다 더 나은 솔루션일 수 있습니다.

• 테이블 변수를 다른 테이블과 조인하는 쿼리의 경우 힌트를 RECOMPILE 사용하여 최적화 프로그램에서 테이블 변수에 올바른 카디널리티를 사용합니다.

• Azure SQL Database에서 SQL Server 2019(15.x)부터 테이블 변수 지연 컴파일 기능은 실제 테이블 변수 행 수를 기반으로 하는 카디널리티 예상치를 전파하여 실행 계획을 최적화하기 위한 보다 정확한 행 수를 제공합니다. 자세한 내용은 SQL 데이터베이스의 지능형 쿼리 처리를 참조하세요.

Remarks

변수는 일괄 처리 또는 프로시저에서 또는 블록에 대한 WHILELOOPIF...ELSE 카운터로 사용되는 경우가 많습니다.

변수는 식에서만 사용할 수 있으며 개체 이름이나 키워드 대신 사용할 수 없습니다. 동적 SQL 문을 생성하려면 .를 사용합니다 EXECUTE.

지역 변수의 범위는 변수가 선언된 일괄 처리입니다.

테이블 변수가 반드시 메모리 상주하지는 않습니다. 메모리가 부족하면 테이블 변수에 속한 페이지를 tempdb로 푸시 아웃할 수 있습니다.

테이블 변수에서 인라인 인덱스를 정의할 수 있습니다.

현재 커서가 할당된 커서 변수는 다음 문의 원본으로 참조될 수 있습니다.

• CLOSE 성명서
• DEALLOCATE 성명서
• FETCH 성명서
• OPEN 성명서
• Positioned DELETE 또는 UPDATE statement
• SET CURSOR variable 문(오른쪽)

위의 모든 문에서, 참조되는 커서 변수가 존재하지만 현재 할당된 커서가 없으면 SQL Server에서 오류가 발생합니다. 또한 참조되는 커서 변수가 없으면 SQL Server에 선언되지 않은 다른 형식의 변수에 동일한 오류가 발생합니다.

커서 변수는 다음과 같습니다.

• 커서 형식이나 다른 커서 변수의 대상이 될 수 있습니다. 자세한 내용은 SET @local_variable를 참조하세요.

• 커서 변수에 현재 할당된 커서가 없는 경우 문에서 EXECUTE 출력 커서 매개 변수의 대상으로 참조할 수 있습니다.

• 커서에 대한 포인터로 간주됩니다.

Examples

이 문서의 코드 샘플은 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있는 데이터베이스 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용합니다AdventureWorks2022.

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. 두 개의 변수와 함께 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. 형식 테이블의 변수 선언

다음 예제에서는 문 절에 지정된 값을 저장하는 변수를 OUTPUTUPDATE 만듭니다table. 각각 SELECT의 값과 @MyTableVar 테이블의 업데이트 작업 결과를 반환하는 두 개의 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. 인라인 인덱스를 사용하여 형식 테이블의 변수 선언

다음 예제에서는 클러스터형 인라인 인덱스와 두 개의 비클러스터형 인라인 인덱스를 사용하여 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 및 분석 플랫폼 시스템(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. 두 개의 변수와 함께 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;

관련 콘텐츠

• EXECUTE(Transact-SQL)
• SQL 데이터베이스 함수란?
• SELECT(Transact-SQL)
• table(Transact-SQL)
• 형식화된 XML과 형식화되지 않은 XML 비교