table(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(공용 언어 런타임) 사용자 정의 형식으로 열을 정의할 경우 사용자 정의 형식의 데이터 정렬이 해당 열에 상속됩니다.

최선의 구현 방법

테이블 변수를 사용하여 많은 양의 데이터(100행 이상)를 저장하지 마십시오. 테이블 변수에 많은 양의 데이터가 포함되어 있는 경우 계획 선택이 최적 상태가 아니거나 안정적이 아닐 수 있습니다. 임시 테이블을 사용하거나 USE PLAN 쿼리 힌트를 사용하도록 이러한 쿼리를 다시 작성하여 최적화 프로그램이 사용자 시나리오에 적합한 기존 쿼리 계획을 사용하게 하십시오.

일반적인 주의

다음 예에서와 같이 일괄 처리의 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 변수는 SQL Server 최적화 프로그램의 비용 기반 추론 모델에서는 사용할 수 없습니다. 따라서 효율적인 쿼리 계획을 얻기 위해 비용 기반 선택이 필요한 경우에는 테이블 변수를 사용하지 않아야 합니다. 비용 기반 선택이 필요한 경우에는 임시 테이블을 사용하는 것이 좋습니다. 일반적으로 조인이 포함된 쿼리, 병렬 처리 의사 결정 및 인덱스 선택 등이 여기에 해당합니다.

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

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

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

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

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

예

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

USE AdventureWorks2008R2;
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. 인라인 테이블 반환 함수 만들기

USE AdventureWorks2008R2;
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);

참고 항목

참조

개념