아티클
09/27/2010

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

테이블 반환 매개 변수는 SQL Server 2008의 새 매개 변수 형식입니다. 테이블 반환 매개 변수는 사용자 정의 테이블 형식을 사용하여 선언됩니다. 테이블 반환 매개 변수를 사용하면 임시 테이블이나 많은 매개 변수를 만들지 않고도 저장 프로시저 또는 함수와 같은 Transact-SQL 문이나 루틴에 여러 행의 데이터를 보낼 수 있습니다.

테이블 반환 매개 변수는 OLE DB 및 ODBC의 매개 변수 배열과 유사하지만 보다 유연하고 Transact-SQL과 보다 긴밀하게 통합합니다. 또한 테이블 반환 매개 변수는 집합 기반 작업에 사용할 수 있다는 이점이 있습니다.

[!참고]

Transact-SQL은 입력 데이터의 복사본을 만들지 않기 위해 참조로 루틴에 테이블 반환 매개 변수를 전달합니다.

테이블 반환 매개 변수를 사용하여 Transact-SQL 루틴을 만들고 실행한 다음 모든 관리 언어의 Transact-SQL 코드, 관리되는 클라이언트 및 기본 클라이언트에서 해당 루틴을 호출할 수 있습니다.

Transact-SQL에서 테이블 반환 매개 변수 만들기 및 사용

테이블 반환 매개 변수의 두 가지 주요 구성 요소는 SQL Server 형식 및 해당 형식을 참조하는 매개 변수입니다. 테이블 반환 매개 변수를 만들고 사용하려면 다음 단계를 수행합니다.

테이블 형식을 만들고 테이블 구조를 정의합니다.

SQL Server 형식을 만드는 방법은 사용자 정의 테이블 형식을 참조하십시오. 테이블 구조를 정의하는 방법은 CREATE TABLE(Transact-SQL)을 참조하십시오.
테이블 형식의 매개 변수를 포함하는 루틴을 선언합니다. SQL Server 루틴에 대한 자세한 내용은 CREATE PROCEDURE(Transact-SQL) 및 CREATE FUNCTION(Transact-SQL)을 참조하십시오.
테이블 형식의 변수를 선언하고 테이블 형식을 참조합니다. 변수를 선언하는 방법은 DECLARE @local\_variable(Transact-SQL)을 참조하십시오.
INSERT 문을 사용하여 테이블 변수를 채웁니다. 데이터를 삽입하는 방법은 INSERT 및 SELECT를 사용하여 행 추가를 참조하십시오.
테이블 변수를 만들고 채운 후에는 변수를 루틴에 전달할 수 있습니다.

루틴이 범위를 벗어나면 테이블 반환 매개 변수를 더 이상 사용할 수 없습니다. 형식 정의는 삭제될 때까지 유지됩니다.

SQL Server Native Client에서 테이블 반환 매개 변수를 사용하려면 테이블 반환 매개 변수(SQL Server Native Client)를 참조하십시오.

ADO.NET에서 테이블 반환 매개 변수를 사용하려면 ADO.NET 설명서를 참조하십시오.

이점

테이블 반환 매개 변수는 매개 변수 목록을 전달하는 임시 테이블 또는 다른 방법보다 유연하며 경우에 따라 보다 뛰어난 성능을 제공합니다. 테이블 반환 매개 변수에서 제공하는 이점은 다음과 같습니다.

클라이언트의 데이터를 처음 채울 때 잠글 필요가 없습니다.
간단한 프로그래밍 모델을 제공합니다.
단일 루틴에 복잡한 비즈니스 논리를 포함할 수 있습니다.
서버 왕복을 줄입니다.
카디널리티가 다른 테이블 구조를 가질 수 있습니다.
강력한 형식입니다.
클라이언트가 정렬 순서 및 고유 키를 지정할 수 있습니다.

제한 사항

테이블 반환 매개 변수에는 다음과 같은 제한 사항이 있습니다.

SQL Server는 테이블 반환 매개 변수의 열에 대한 통계를 유지 관리하지 않습니다.
테이블 반환 매개 변수는 Transact-SQL 루틴에 입력 READONLY 매개 변수로 전달되어야 합니다. 루틴 본문의 테이블 반환 매개 변수에 대해서는 UPDATE, DELETE 또는 INSERT와 같은 DML 작업을 수행할 수 없습니다.
테이블 반환 매개 변수를 SELECT INTO 또는 INSERT EXEC 문의 대상으로 사용할 수 없습니다. 테이블 반환 매개 변수는 SELECT INTO의 FROM 절 또는 INSERT EXEC 문자열이나 저장 프로시저에 사용할 수 있습니다.

범위

테이블 반환 매개 변수의 범위는 다른 매개 변수와 똑같이 저장 프로시저, 함수 또는 동적 Transact-SQL 텍스트입니다. 마찬가지로 테이블 형식 변수의 범위는 DECLARE 문을 사용하여 만든 다른 모든 지역 변수의 범위와 같습니다. 동적 Transact-SQL 문 내에서 테이블 반환 변수를 선언하고 이 변수를 저장 프로시저 및 함수에 테이블 반환 매개 변수로 전달할 수 있습니다.

보안

테이블 반환 매개 변수에 대한 권한은 Transact-SQL 키워드인 CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION 및 REVOKE를 사용하여 SQL Server의 개체 보안 모델을 따릅니다.

카탈로그 뷰

테이블 반환 매개 변수에 대한 정보를 얻으려면 카탈로그 뷰 sys.parameters(Transact-SQL), sys.types(Transact-SQL) 및 sys.table_types(Transact-SQL)를 쿼리합니다.

테이블 반환 매개 변수와 BULK INSERT 작업 비교

테이블 반환 매개 변수를 사용하는 방법은 집합 기반 변수를 사용하는 다른 방법과 유사하지만 큰 데이터 집합의 경우 테이블 반환 매개 변수를 사용하는 것이 훨씬 빠른 경우가 많습니다. 테이블 반환 매개 변수보다 더 많은 시작 비용이 드는 대량 작업과 비교해볼 때 테이블 반환 매개 변수는 1,000개 미만의 행을 삽입할 때 더 효과적입니다.

다시 사용되는 테이블 반환 매개 변수는 임시 테이블 캐싱을 활용합니다. 이 테이블 캐싱은 이와 동일한 BULK INSERT 작업보다 뛰어난 확장성을 제공합니다. 작은 행 삽입 작업을 사용하는 경우에는 BULK INSERT 작업 또는 테이블 반환 매개 변수 대신 매개 변수 목록 또는 일괄 처리된 문을 사용하여 작은 성능 이점을 얻을 수도 있습니다. 그러나 이러한 메서드는 프로그래밍하기가 다소 불편하고 행이 늘어남에 따라 성능이 빠르게 저하됩니다.

테이블 반환 매개 변수는 동일한 매개 변수 배열 구현 이상의 성능을 제공합니다.

다음 표에서는 삽입 작업의 속도를 기반으로 사용할 기술을 보여 줍니다.

데이터 원본	서버 논리	행 수	가장 적합한 기술
서버의 형식이 지정된 데이터 파일	직접 삽입	< 1000	BULK INSERT
서버의 형식이 지정된 데이터 파일	직접 삽입	> 1000	BULK INSERT
서버의 형식이 지정된 데이터 파일	복잡성	< 1000	테이블 반환 매개 변수
서버의 형식이 지정된 데이터 파일	복잡성	> 1000	BULK INSERT
원격 클라이언트 프로세스	직접 삽입	< 1000	테이블 반환 매개 변수
원격 클라이언트 프로세스	직접 삽입	> 1000	BULK INSERT
원격 클라이언트 프로세스	복잡성	< 1000	테이블 반환 매개 변수
원격 클라이언트 프로세스	복잡성	> 1000	테이블 반환 매개 변수

예

다음 예에서는 Transact-SQL을 사용하며 테이블 반환 매개 변수 형식을 만들고, 해당 형식을 참조하는 변수를 선언하고, 매개 변수 목록을 채운 다음 저장 프로시저에 값을 전달하는 방법을 보여 줍니다.

USE AdventureWorks;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

참고 항목

참조

CREATE TYPE(Transact-SQL)

DECLARE @local\_variable(Transact-SQL)

다음을 통해 공유