table(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

테이블 은 나중에 처리를 위해 결과 집합을 저장하는 데 사용되는 특수 데이터 형식입니다. table은 주로 테이블 반환 함수의 결과 집합으로 반환되는 행 집합을 임시로 저장하는 데 사용됩니다. 테이블은 주로 테이블 반환 함수의 결과 집합으로 반환되는 행 집합을 임시로 저장하는 데 사용됩니다. 테이블 변수는 함수, 저장 프로시저 및 일괄 처리에 사용할 수 있습니다. table 형식의 변수를 선언하려면 DECLARE @local_variable를 사용합니다.

Transact-SQL 구문 표기 규칙

구문

table_type_definition ::=
    TABLE ( { <column_definition> | <table_constraint> } [ , ...n ] )

<column_definition> ::=
    column_name scalar_data_type
    [ COLLATE <collation_definition> ]
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
    [ ROWGUIDCOL ]
    [ column_constraint ] [ ...n ]

 <column_constraint> ::=
    { [ NULL | NOT NULL ]
    | [ PRIMARY KEY | UNIQUE ]
    | CHECK ( logical_expression )
    }

<table_constraint> ::=
     { { PRIMARY KEY | UNIQUE } ( column_name [ , ...n ] )
     | CHECK ( logical_expression )
     }

인수

table_type_definition

CREATE TABLE에서 테이블을 정의하는 데 사용되는 동일한 정보 하위 집합입니다. 테이블 선언에는 열 정의, 이름, 데이터 형식 및 제약 조건 등이 포함되며 허용되는 제약 조건 유형은 PRIMARY KEY, UNIQUE KEY 및 NULL뿐입니다.

구문에 대한 자세한 내용은 CREATE TABLE(Transact-SQL), CREATE FUNCTION(Transact-SQL) 및 DECLARE @local_variable (Transact-SQL)을 참조하세요.

collation_definition

Microsoft Windows 로캘 및 비교 스타일, Windows 로캘 및 이진 표기법 또는 Microsoft SQL Server 데이터 정렬로 구성된 열의 데이터 정렬입니다. collation_definition을 지정하지 않으면 현재 데이터베이스의 데이터 정렬이 해당 열에 상속됩니다. 또는 CLR(공용 언어 런타임) 사용자 정의 형식으로 열을 정의할 경우 사용자 정의 형식의 데이터 정렬이 해당 열에 상속됩니다.

설명

테이블 다음 예에서와 같이 일괄 처리의 FROM 절에서 이름으로 변수를 참조합니다.

SELECT Employee_ID, Department_ID FROM @MyTableVar;

FROM 절 밖에서는 다음 예에서와 같이 별칭을 사용하여 테이블 변수를 참조해야 합니다.

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

테이블 변수는 변경되지 않는 쿼리 계획이 있는 소규모 쿼리에 대한 임시 테이블과 다시 컴파일 문제가 주요한 경우에 다음과 같은 이점을 제공합니다.

• 테이블 변수는 지역 변수처럼 작동하며 잘 정의된 범위를 가집니다. 이 변수는 해당 변수가 선언된 함수, 저장 프로시저 또는 일괄 처리에 사용할 수 있습니다.

  범위 내에서 일반 테이블처럼 테이블 변수를 사용할 수 있으며 SELECT, INSERT, UPDATE 및 DELETE 문에서 테이블 또는 테이블 식이 사용되는 어디에나 적용할 수 있습니다. 그러나 다음 문에서는 테이블을 사용할 수 없습니다.

SELECT select_list INTO table_variable;

테이블 변수는 변수가 정의된 함수, 저장 프로시저 및 일괄 처리가 끝나면 자동으로 정리됩니다.

• 저장 프로시저에 테이블 변수를 사용하면 성능에 영향을 주는 비용 기반 선택 사항이 없는 경우 임시 테이블을 사용할 때보다 저장 프로시저를 다시 컴파일하는 횟수가 줄어듭니다.

  테이블 변수는 임시 테이블과 함께 발생할 수 있는 CREATE 또는 ALTER 문이 발생할 때 다시 확인이 수행되지 않도록 테이블 변수를 만드는 일괄 처리로 완전히 격리됩니다. 임시 테이블에는 중첩된 저장 프로시저에서 테이블을 참조할 수 있도록 이 다시 확인이 필요합니다. 테이블 변수는 이 단계를 완전히 방지하므로 저장 프로시저는 이미 컴파일된 계획을 사용하여 저장 프로시저를 처리하기 위해 리소스를 저장할 수 있습니다.

• 테이블 변수와 관련된 트랜잭션은 테이블 변수가 업데이트되는 동안만 지속됩니다. 따라서 테이블 변수에는 더 적은 잠금 및 로깅 리소스가 필요합니다.

제한 사항

테이블 변수에는 분포 통계가 없습니다. 다시 컴파일을 트리거하지 않습니다. 대부분의 경우 최적화 프로그램은 테이블 변수에 행이 없다는 가정하에 쿼리 계획을 작성합니다. 이러한 이유로, 많은 수의 행(100개 이상)을 예상하는 경우 테이블 변수를 사용할 때 주의해야 합니다. 이러한 경우 임시 테이블이 좋은 해결책일 수 있습니다. 테이블 변수를 다른 테이블과 조인하는 쿼리의 경우 RECOMPILE 힌트를 사용하여 최적화 프로그램이 테이블 변수에 올바른 카디널리티를 사용하도록 합니다.

SQL Server 최적화 프로그램의 비용 기반 추론에서는 테이블 변수가 지원되지 않습니다. 따라서 효율적인 쿼리 계획을 얻기 위해 비용 기반 선택이 필요한 경우에는 이 변수를 사용하면 안 됩니다. 비용 기반 선택이 필요한 경우에는 임시 테이블이 적절합니다. 이 계획에는 일반적으로 조인, 병렬 처리 결정 및 인덱스 선택 사항 선택을 사용하는 쿼리가 포함됩니다.

테이블 변수를 수정하는 쿼리는 병렬 쿼리 실행 계획을 생성하지 않습니다. 큰 테이블 변수나 복잡한 쿼리의 테이블 변수를 수정하면 성능에 영향을 줄 수 있습니다. 테이블 변수가 수정되는 경우에는 임시 테이블을 대신 사용하는 것이 좋습니다. 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하세요. 수정하지 않고 테이블 변수를 읽는 쿼리는 병렬 처리할 수 있습니다.

중요

데이터베이스 호환성 수준 150은 테이블 변수 지연 컴파일을 도입하여 테이블 변수의 성능을 개선합니다. 자세한 내용은 테이블 변수 지연 컴파일을 참조하세요.

명시적으로 테이블 변수에 대한 인덱스를 만들 수 없으며 테이블 변수에 대한 통계는 유지되지 않습니다. SQL Server 2014(12.x)부터, 테이블 정의와 함께 특정 인덱스 유형을 인라인으로 만들 수 있는 새 구문이 도입되었습니다. 이 새 구문을 사용하여 테이블 정의의 일부로 테이블 변수의 인덱스를 만들 수 있습니다. 경우에 따라 전체 인덱스 지원과 통계를 제공하는 임시 테이블을 대신 사용하여 성능을 향상할 수도 있습니다. 임시 테이블 및 인라인 인덱스 만들기에 대한 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하세요.

테이블 형식 선언 내의 CHECK 제약 조건, DEFAULT 값 및 계산 열은 사용자 정의 함수를 호출할 수 없습니다. 테이블 변수 간의 할당 작업은 지원되지 않습니다. 테이블 변수는 제한된 범위를 가지며 영구 데이터베이스의 일부가 아니므로 트랜잭션 롤백의 영향을 받지 않습니다. 테이블 변수는 생성 후 변경할 수 없습니다.

테이블 변수는 문에서 SELECT ... INTO 절의 INTO 대상으로 사용할 수 없습니다.

EXEC 문 또는 저장 프로시저 외부에서 테이블 변수를 만든 경우 EXEC 문 또는 sp_executesql 저장 프로시저를 사용하여 테이블 변수를 참조하는 동적 SQL Server 쿼리를 sp_executesql 실행할 수 없습니다. 테이블 변수는 로컬 범위에서만 참조할 수 있으므로 EXEC 문과 sp_executesql 저장 프로시저는 테이블 변수의 범위를 벗어나게 됩니다. 그러나 테이블 변수 로컬 범위가 EXEC 문 또는 저장 프로시저에 있으므로 테이블 변수를 만들고 EXEC 문 또는 sp_executesql sp_executesql 저장 프로시저 내에서 모든 처리를 수행할 수 있습니다.

테이블 변수는 메모리 전용 구조가 아닙니다. 테이블 변수는 메모리에 들어갈 수 있는 것보다 많은 데이터를 보유할 수 있으므로 데이터를 저장할 디스크에 위치해야 합니다. 테이블 변수는 임시 테이블과 유사하게 데이터베이스에 tempdb 만들어집니다. 메모리를 사용할 수 있는 경우 메모리(데이터 캐시)에 있는 동안 테이블 변수와 임시 테이블이 모두 만들어지고 처리됩니다.

테이블 변수 및 임시 테이블

테이블 변수와 임시 테이블 중에서 선택하는 것은 다음과 같은 요인에 따라 달라집니다.

• 테이블에 삽입되는 행 수입니다.
• 쿼리가 저장되는 다시 컴파일 횟수입니다.
• 쿼리 유형 및 성능에 대한 인덱스 및 통계에 대한 종속성입니다.

경우에 따라 임시 테이블이 있는 저장 프로시저를 더 작은 저장 프로시저로 분리하여 더 작은 단위로 다시 컴파일하는 것이 도움이 됩니다.

일반적으로 많은 양의 데이터가 있고 테이블을 반복적으로 사용하는 경우를 제외하고 가능한 경우 테이블 변수를 사용합니다. 이 경우 임시 테이블에 인덱스를 만들어 쿼리 성능을 높일 수 있습니다. 그러나 각 시나리오는 다를 수 있습니다. 특정 쿼리 또는 저장 프로시저에 대한 임시 테이블보다 테이블 변수가 더 유용한지 테스트하는 것이 좋습니다.

예제

A. 형식 테이블의 변수 선언

다음 예제에서는 UPDATE 문의 OUTPUT 절에 지정된 값을 저장하는 테이블 변수를 만듭니다. 다음 두 SELECT 문은 테이블의 업데이트 작업 Employee 결과와 값을 @MyTableVar 반환합니다. 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

B. 인라인 테이블 반환 함수 만들기

다음 예에서는 인라인 테이블 반환 함수를 반환합니다. ProductID 열, Name 열, 그리고 대리점에 판매된 각 제품에 대한 대리점별 총 연간 매출의 집계를 YTD Total 열로 반환합니다.

USE AdventureWorks2022;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID,
        P.Name,
        SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD
        ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH
        ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C
        ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID,
        P.Name
);
GO

함수를 호출하려면 이 쿼리를 실행하세요.

SELECT * FROM Sales.ufn_SalesByStore (602);

참고 항목

• COLLATE(Transact-SQL)
• CREATE FUNCTION(Transact-SQL)
• 사용자 정의 함수
• CREATE TABLE(Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• 테이블 반환 매개 변수 사용(데이터베이스 엔진)
• 쿼리 힌트(Transact SQL)