테이블-값 매개 변수 사용(데이터베이스 엔진)
적용 대상:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric 내의 SQL Database
테이블 값 매개 변수는 사용자 정의 테이블 유형을 통해 선언됩니다. 테이블 반환 매개 변수를 사용하면 임시 테이블이나 많은 매개 변수를 만들지 않고도 저장 프로시저 또는 함수와 같은 Transact-SQL 문이나 루틴에 여러 행의 데이터를 보낼 수 있습니다.
테이블 반환 매개 변수는 OLE DB 및 ODBC의 매개 변수 배열과 유사하지만 보다 유연하고 Transact-SQL과 보다 긴밀하게 통합합니다. 또한 테이블 값 매개변수는 집합 기반 작업에 사용할 수 있는 장점이 있습니다.
Transact-SQL은 입력 데이터의 복사본을 만들지 않기 위해 참조로 루틴에 테이블 반환 매개 변수를 전달합니다. 테이블 반환 매개 변수를 사용하여 Transact-SQL 루틴을 만들고 실행한 다음, 모든 관리 언어의 Transact-SQL 코드, 관리되는 클라이언트 및 기본 클라이언트에서 해당 루틴을 호출할 수 있습니다.
이점
테이블 값 매개 변수는 다른 매개 변수와 마찬가지로 저장 프로시저, 함수 또는 동적 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문의 대상으로 사용할 수 없습니다. 테이블 반환 매개 변수는FROM의SELECT INTO절 또는INSERT EXEC문자열이나 저장 프로시저에 있을 수 있습니다.
테이블 반환 매개 변수와 BULK INSERT 작업 비교
테이블 반환 매개 변수 사용 방법은 집합 기반 변수를 사용하는 다른 방법과 비슷합니다. 그러나 대규모 데이터 집합의 경우 테이블 반환 매개 변수를 더 빠르게 자주 사용할 수 있습니다. 테이블 반환 매개 변수보다 시작 비용이 더 높은 대량 작업에 비해 테이블 반환 매개 변수는 행을 1,000개 미만 삽입하는 작업에 적합합니다.
테이블 값 매개변수가 재사용되면 임시 테이블 캐싱의 이점을 누릴 수 있습니다. 이 테이블 캐싱을 사용하면 동등한 대량 삽입 작업보다 확장성이 향상됩니다. 작은 행 삽입 작업은 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;
예상 결과 집합은 다음과 같습니다.
(181 rows affected)