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

아티클
03/22/2024

적용 대상: SQL Server Azure SQL Database Azure Synapse Analytics Analytics Platform System(PDW)

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

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

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

항목 내용:

혜택

제한 사항

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

예제

이점

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

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

클라이언트의 데이터를 처음 채울 때 잠글 필요가 없습니다.
간단한 프로그래밍 모델을 제공합니다.
단일 루틴에 복잡한 비즈니스 논리를 포함할 수 있습니다.
서버에 대한 왕복을 줄입니다.
다른 카디널리티의 테이블 구조가 있을 수 있습니다.
강력한 형식입니다.
클라이언트에서 정렬 순서와 고유 키를 지정할 수 있습니다.
저장 프로시저에 사용될 때 임시 테이블처럼 캐시됩니다. SQL Server 2012(11.x)부터 테이블-반환 매개 변수는 또한 매개 변수가 있는 쿼리에 대해 캐시됩니다.

사용 권한

사용자 정의 테이블 형식의 인스턴스를 만들거나 테이블 반환 매개 변수를 사용하여 저장 프로시저를 호출하려면 사용자에게 형식 또는 형식이 포함된 스키마나 데이터베이스에 대한 EXECUTE 및 REFERENCES 권한이 있어야 합니다.

제한 사항

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

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

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

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

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

테이블 반환 매개 변수는 동등한 매개 변수 배열 구현보다 균일하게 잘 수행됩니다.

예시

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

/* 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 AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

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

이점

사용 권한

제한 사항

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

예시

참고 항목

피드백

피드백

추가 리소스