데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

테이블 반환 매개변수 사용과 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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1581
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2222
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2512
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1717
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1543
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1872
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4579
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1273
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1191
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1331
» 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1638





XE Login