다음을 통해 공유

Table-Valued 매개 변수 사용(데이터베이스 엔진)

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

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

Transact-SQL은 입력 데이터의 복사본을 만들지 않기 위해 참조로 루틴에 테이블 반환 매개 변수를 전달합니다. 테이블 반환 매개 변수를 사용하여 Transact-SQL 루틴을 만들고 실행한 다음, 모든 관리 언어의 Transact-SQL 코드, 관리되는 클라이언트 및 기본 클라이언트에서 해당 루틴을 호출할 수 있습니다.

이 주제에서 다루는 내용:

혜택

제한 사항

Table-Valued 매개 변수 및 BULK INSERT 작업

예시

혜택

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

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

  • 클라이언트에서 데이터를 처음으로 가져올 때는 잠금을 획득하지 마십시오.

  • 간단한 프로그래밍 모델을 제공합니다.

  • 단일 루틴에 복잡한 비즈니스 논리를 포함할 수 있습니다.

  • 서버에 대한 왕복을 줄입니다.

  • 다른 카디널리티의 테이블 구조가 있을 수 있습니다.

  • 강력하게 형식화되어 있습니다.

  • 클라이언트에서 정렬 순서와 고유 키를 지정할 수 있습니다.

  • 저장 프로시저에 사용될 때 임시 테이블처럼 캐시됩니다. SQL Server 2012부터는 매개 변수가 있는 쿼리에 대해 테이블 반환 매개 변수도 캐시됩니다.

제한 사항

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

  • SQL Server는 테이블 반환 매개 변수의 열에 대한 통계를 유지 관리하지 않습니다.

  • 테이블 값 매개 변수는 Transact-SQL 루틴에 입력 READONLY 매개 변수로 전달되어야 합니다. 루틴 본문의 테이블 반환 매개 변수에 대해서는 UPDATE, DELETE 또는 INSERT와 같은 DML 작업을 수행할 수 없습니다.

  • SELECT INTO 또는 INSERT EXEC 문에서 테이블 값 매개 변수를 대상으로 사용할 수 없습니다. 테이블 반환 매개 변수는 SELECT INTO의 FROM 절 또는 INSERT EXEC 문자열 또는 저장 프로시저에 있을 수 있습니다.

Table-Valued 매개 변수와 BULK INSERT 작업 비교

테이블 반환 매개 변수 사용 방법은 집합 기반 변수를 사용하는 다른 방법과 비슷합니다. 그러나 대규모 데이터 집합의 경우 테이블 반환 매개 변수를 더 빠르게 자주 사용할 수 있습니다. 테이블 반환 매개 변수보다 시작 비용이 큰 대량 작업에 비해 테이블 반환 매개 변수는 1000개 미만의 행을 삽입하는 데 적합합니다.

테이블 값 매개변수가 재사용되면 임시 테이블 캐싱의 이점을 누릴 수 있습니다. 이 테이블 캐싱을 사용하면 동등한 BULK INSERT 작업보다 확장성이 향상됩니다. 작은 행 삽입 작업을 사용하면 BULK INSERT 작업 또는 테이블 반환 매개 변수 대신 매개 변수 목록 또는 일괄 처리된 문을 사용하여 작은 성능 이점을 얻을 수 있습니다. 그러나 이러한 방법은 프로그래밍에 덜 편리하며 행이 증가함에 따라 성능이 빠르게 저하됩니다.

테이블 값 매개 변수는 동등한 매개 변수 배열 구현보다 등거나 더 잘 수행됩니다.

예시

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

USE AdventureWorks2012;  
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 dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.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 AdventureWorks2012.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)
sys.types(Transact-SQL)
sys.parameters(Transact-SQL)
sys.parameter_type_usages(Transact-SQL)
CREATE PROCEDURE(Transact-SQL)
함수 생성하기(Transact-SQL)

  • Last updated on