테이블 변수의 활용 - SQL 프로그래밍 배우기 (Learn SQL Programming)

SQL 활용 및 팁

특정프로그램 접속 금지
특정IP로부터 접근 허용
데이타베이스 삭제
IDENTITY 컬럼 활용
파워쉘 TCP포트 변경
SET NOEXEC 활용
TSQL 파일 체크섬 산출
편리한 MERGE문 활용
데드락 분석의 기초 (1)
단순한 데드락 사례 (2)
실전 데드락 사례 (3)
테이블 변수의 활용
백업/복원 진척 확인
Recursive CTE 사용
최대값 산출 SELECT
WHERE-조건별 다른쿼리
레코드에 일련번호 붙이기
SQL 2012 시퀀스 사용법
ROW 데이타 하나로 결합
ROW 데이타를 CSV로 결합
대량 레코드 분할 실행
OBJECT_ID() 함수 활용

특정프로그램 접속 금지

특정IP로부터 접근 허용

데이타베이스 삭제

IDENTITY 컬럼 활용

파워쉘 TCP포트 변경

SET NOEXEC 활용

TSQL 파일 체크섬 산출

편리한 MERGE문 활용

데드락 분석의 기초 (1)

단순한 데드락 사례 (2)

실전 데드락 사례 (3)

테이블 변수의 활용

백업/복원 진척 확인

Recursive CTE 사용

최대값 산출 SELECT

WHERE-조건별 다른쿼리

레코드에 일련번호 붙이기

SQL 2012 시퀀스 사용법

ROW 데이타 하나로 결합

ROW 데이타를 CSV로 결합

대량 레코드 분할 실행

OBJECT_ID() 함수 활용

테이블 변수 (Table Variable)

일반적으로 DECLARE로 선언되는 변수는 정수나 문자열등의 단일 데이타를 저장한다. 만약 여러 데이타를 묶어 하나의 변수로 나타낼 경우, 테이블 변수를 사용할 수 있다. 특히, 테이블의 데이타를 임시 메모리에 저장하고 싶을 때, 테이블 변수를 자주 사용한다. 아래 예제는 집계용 테이블 변수를 선언하고 여기에 데이타를 추가한 후, 출력해 보이는 예이다.

예제

-- 집계를 위한 테이블 변수
DECLARE @Products TABLE 
(
  ProductID int, 
  Total int
)

-- 집계하여 테이블 변수에 추가
INSERT INTO @Products (ProductID, Total)
  SELECT ProductID, SUM(Sales)
  FROM [Orders]
  GROUP BY ProductID

SELECT * FROM @Products
WHERE Total > 10000;

테이블 변수 제약점

테이블 변수는 DECLARE로 선언된 다른 로컬 변수들과 같이 해당 배치(Batch) 혹은 저장 프로시져내로 Scope가 제한된다.<br> - 해당 배치를 벗어나거나 프로시져가 끝나면 사용할 수 없게 되며, 입출력 파라미터로 전달될 수도 없다.<br> - Dynamic SQL문 안에 테이블변수를 사용하고 이를 EXECUTE로 실행한다면 이는 현재 배치와 별도의 Context를 가지므로 테이블 변수를 인식할 수 없다.<br> 테이블 변수의 제약점들을 극복하기 위해 흔히 임시테이블 (Temporary Table)이나 Table Type (SQL 2008 이상) 등을 사용할 수 있다. 아래 첫번째 부분은 임시테이블을 사용하여 해당 세션안에서 계속 임시 테이블이 유지되는 시나리오에 사용할 수 있는 예제를 보여준다. 두번째 부분은 테이블 타입을 정의하고 이 타입의 로컬변수(@tab)을 지정한 후, 이를 sp_executesql 의 3번째 파라미터로 테이블 타입 변수를 전달하는 예를 보여준다. SQL 2008부터 테이블 타입 변수를 파라미터로 전달될 수 있는데, 읽기 전용이므로 SELECT를 사용할 때만 유용하다.

예제

-- ((( 임시 테이블 사용할 경우 )))
-- 임시 테이블 생성
CREATE TABLE #MyData (ID INT, VALUE INT)    

-- Dynamic SQL 안에서 #MyData 사용
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'INSERT #MyData VALUES (1, 1)'
EXEC(@SQL)

-- EXEC 실행 후 임시테이블 엑세스
SELECT * FROM #MyData


-- ((( 테이블 타입 사용할 경우 )))
-- 테이블 타입 정의
CREATE TYPE MyTableType AS TABLE
(
  ID INT,
  VALUE INT
)
GO
 
-- 테이블 타입 변수
DECLARE @TAB AS MyTableType;

-- 테이블 변수 사용 Dynamic SQL 생성
DECLARE @SQL NVARCHAR(MAX)
SET @sql = 'SELECT * FROM @TAB'

-- 테이블 변수를 파라미터로 전달
EXEC sp_executesql @SQL, N'@TAB MyTableType READONLY', @TAB = @TAB;

본 웹사이트는 광고를 포함하고 있습니다. 광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.