table(Transact-SQL)

  • 아티클

나중에 처리하기 위해 결과 집합을 저장하는 데 사용할 수 있는 특별한 데이터 형식입니다. table은 주로 테이블 반환 함수의 결과 집합으로 반환되는 행 집합을 임시로 저장하는 데 사용됩니다. 함수와 변수는 table 형식으로 선언할 수 있습니다. 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 절에서 이름으로 table 변수를 참조할 수 있습니다.

SELECT Employee_ID, Department_ID FROM @MyTableVar;

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

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

table 변수는 변경되지 않는 쿼리 계획이 있는 소규모 쿼리의 경우와 다시 컴파일 기능이 중요한 경우에 다음과 같은 이점을 제공합니다.

  • table 변수는 지역 변수처럼 작동하며 잘 정의된 범위를 가집니다. 범위는 변수가 선언된 함수, 저장 프로시저 또는 일괄 처리입니다.

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

    SELECT select_list INTO table_variable;

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

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

  • table 변수와 관련된 트랜잭션은 table 변수가 업데이트되는 동안만 지속됩니다. 따라서 table 변수를 사용하면 리소스의 잠금과 로깅에 대한 요구가 줄어듭니다.

제한 사항

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

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

table 변수를 수정하는 쿼리는 병렬 쿼리 실행 계획을 생성하지 않습니다. 매우 큰 table 변수나 복잡한 쿼리의 table 변수를 수정하면 성능에 영향을 줄 수 있습니다. 이런 상황에서는 임시 테이블을 대신 사용해 보십시오. 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하십시오. 수정하지 않고 table 변수를 읽는 쿼리는 병렬 처리할 수 있습니다.

명시적으로 table 변수에 대한 인덱스를 만들 수 없으며 table 변수에 대한 통계는 유지되지 않습니다. 경우에 따라서는 인덱스와 통계를 지원하는 임시 테이블을 대신 사용하여 성능을 향상시킬 수도 있습니다. 임시 테이블에 대한 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하십시오.

table 형식 선언 내의 CHECK 제약 조건, DEFAULT 값 및 계산 열은 사용자 정의 함수를 호출할 수 없습니다.

table 변수 간의 할당 작업은 지원되지 않습니다.

table 변수는 제한된 범위를 가지며 영구 데이터베이스의 일부가 아니므로 트랜잭션 롤백의 영향을 받지 않습니다.

테이블 변수는 생성 후 변경할 수 없습니다.

예

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

다음 예에서는 UPDATE 문의 OUTPUT 절에 지정된 값을 저장하는 table 변수를 만듭니다. 각각 @MyTableVar의 값과 Employee 테이블의 업데이트 작업 결과를 반환하는 두 개의 SELECT 문이 이어집니다. INSERTED.ModifiedDate 열의 결과 값은 Employee 테이블의 ModifiedDate 열 값과 다릅니다. 그 이유는 ModifiedDate 값을 현재 날짜로 업데이트하는 AFTER UPDATE 트리거가 Employee 테이블에 정의되어 있기 때문입니다. 그러나 OUTPUT에서 반환된 열은 트리거가 실행되기 전의 데이터를 반영합니다. 자세한 내용은 OUTPUT 절(Transact-SQL)을 참조하십시오.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

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

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

USE AdventureWorks2012;
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 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    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)

개념

사용자 정의 함수

테이블 반환 매개 변수 사용(데이터베이스 엔진)