테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능

테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능

 

• Version : 2008, 2008R2, 2012

 

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

 

테이블 반환 매개변수는 OLE DB 및 ODBC의 매개변수 열과 유사하지만 보다 유연하고 집합 기반 작업에 사용할 수 있다.

 

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

 

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

 

[테이블 반환 매개 변수의 이점]

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

 

 

[제한 사항]

• SQL Server는 테이블 반환 매개 변수의 열에 대한 통계를 유지 관리하지 않음
• T-SQL 루틴에 입력 READONLY 매개 변수로 전달되어야 함
• 루틴 본문의 테이블 반환 매개 변수에 대해서는 UPDATE, DELETE, INSERT와 같은 DML 작업 수행 할 수 없음
• SELECT INTO 또는 INSERT EXEC 문을 대상으로 사용할 수 없음

 

[SQL Server 2008 과 SQL Server 2012의 캐싱 임시테이블 비교]

테이블 반환 매개 변수(TVP)를 사용하면 SQL Server는 내부적으로 데이터를 저장하기 위해 임시 테이블을 사용한다.

• SQL Server 2005를 시작하면 임시 테이블은 재사용을 위해 캐시 될 수 있다. 캐시는 임시 테이블이 빠른 속도로 만들어 지고 삭제 될 때 발생할 수 있는 시스템 테이블 페이지 래치의 경합을 줄일 수 있다.

 

• SQL Server2008, 2008R2 이후부터는 TVP 저장 프로시저, 임시 테이블 TVP가 캐시 된다. 하지만 매개 변수화된 쿼리와 함께 사용한 TVP경우 캐시되지 않으며 시스템 테이블 래치 경합이 발생 할 수 있다.

 

• SQL Server 2012 에서는 테이블의 테이블 매개 변수화가 있는 쿼리에 대해서도 캐시 된다.

 

아래 두 자료를 비교하여 SQL Server 2008과 SQL Server 2012의 캐시에 따른 임시 테이블의 사용을 살펴 보자. SQL Server 2008의 경우에는 임시테이블의 사용이 높은 것을 확인 할 수 있으며 SQL Server 2012에서는 처음 한번 생성되고 나머지는 0의 사용량을 나타낸다.

 

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/bb510489.aspx

http://blogs.msdn.com/b/psssql/archive/2013/02/26/temp-table-caching-improvement-for-table-valued-parameters-in-sql-server-2012.aspx

 

 

 

2013-07-11 / 강성욱 / http://sqlmvp.kr