[MSSQL] 임시테이블(#temp), 테이블변수(@table)

 

 

MSSQL에서 중간 데이터를 잠깐 담아두고 사용하는 임시테이블(#temp)과 테이블변수(@table) 중 어떤 상황에 어떤걸 사용하는 게 좋을 지 고민되는 상황이 있다. 둘 다 데이터를 임시로 저장한다는 점은 같지만, 동작 방식에서 차이가 있다. 상황에 따라 성능 차이가 발생할 수 있기때문에 오늘은 두 가지의 차이를 살펴보고 어떤 상황에 어떤  것을 사용하는 게 좋을지 알아보도록 하자.

임시테이블 (#temp)

• 이름 앞에 [ # ] 을 붙여서 생성
• tempdb에 실제 테이블처럼 생성되며, 세션이 종료되면 자동 삭제
• 인덱스 생성 가능
• ROLLBACK 가능

-- 임시테이블 생성 및 데이터 삽입
CREATE TABLE #temp_fruit
(
    fruit_nm    VARCHAR(20),
    fruit_qty   INT
)

INSERT INTO #temp_fruit VALUES ('사과', 10)
INSERT INTO #temp_fruit VALUES ('배',   5)
INSERT INTO #temp_fruit VALUES ('메론', 3)

-- 인덱스 생성 처리 가능
CREATE INDEX IX_TEMP_FRUIT ON #temp_fruit (fruit_nm)

SELECT * FROM #temp_fruit

 

#temp_fruit 결과 조회

테이블변수 (@table)

• 이름 앞에 [ @ ] 을 붙여서 DECLARE 로 선언
• 기본적으로 메모리에 올라가며, 데이터가 많아지면 tempdb를 사용하기도 함
• 인덱스 생성 불가 
• ROLLBACK  의 영향을 받지 않음 

-- 테이블변수 선언 및 데이터 삽입
DECLARE @table_fruit TABLE
(
    fruit_nm    VARCHAR(20),
    fruit_qty   INT
)

INSERT INTO @table_fruit VALUES ('사과', 10)
INSERT INTO @table_fruit VALUES ('배',   5)
INSERT INTO @table_fruit VALUES ('메론', 3)

SELECT * FROM @table_fruit

@table_fruit 결과 조회

 

우선, 각 세션에서 select 절만을 선택하여 조회를 처리하면 임시테이블(#temp) 는 조회가 가능하나, 테이블변수(@table)은 조회가 처리되지 않는다. 테이블 변수는 DECLARE 로 선언한 부분까지 항상 함께 조회처리를 해야지만 조회가 가능하다.

또, 둘의 가장 중요한 차이 중 하나가 트랜잭션 처리다.

 

BEGIN TRAN
UPDATE #temp_fruit
   SET fruit_qty = 50
 WHERE fruit_nm = '사과'
ROLLBACK

/* 사과를 50개로 업데이트 하다가 ROLLBACK 을 하면 ROLLBACK 이 처리되어 최초 생성인 10개로 표기 */
SELECT * FROM #temp_fruit

사과가 10개

-- 테이블변수 선언 및 데이터 삽입
DECLARE @table_fruit TABLE
(
    fruit_nm    VARCHAR(20),
    fruit_qty   INT
)

INSERT INTO @table_fruit VALUES ('사과', 10)
INSERT INTO @table_fruit VALUES ('배',   5)
INSERT INTO @table_fruit VALUES ('메론', 3)

BEGIN TRAN
UPDATE @table_fruit
   SET fruit_qty = 50
 WHERE fruit_nm = '사과'
ROLLBACK

/* 사과를 50개로 업데이트 하다가 ROLLBACK 을 해도 ROLLBACK이 되지않음 */
SELECT * FROM @table_fruit

사과가 50개

 

보이는것처럼 테이블변수(@table) 은 ROLLBACK이 처리되지 않는다. 트랙잭션 안에서 중간 결과를 보존해야 하는 경우라면 테이블변수(@table)이 유리하고, 반대로 트랜잭션과 함께 데이터가 처리되어야한다면 임시테이블(#temp)를 써야 한다.

 

항목	임시테이블(#temp)	테이블변수(@table)
저장위치	tempdb	메모리 ( 초과 시 tempdb )
인덱스 생성여부	가능	불가
ROLLBACK	가능	불가
통계정보	생성됨	생성안됨
적합한 데이터 양	대용량	소량
생명주기	세션 종료 시 삭제	배치 종료 시 소멸

 

이처럼 임시테이블(#tmep)은 인덱스 설정도 가능하고, 통계정보 생성도 가능하기에 데이터가 많을수록 유리하다. 반면에 테이블변수(@table )는 인덱스 생성이 불가능하고, 통계정보가 없어서 데이터가 많아질수록 실행계획이 비효율적으로 수립될 수 있기때문에 대량의 데이터를 처리하기에는 적합하지 않다.

 

실무에서 기준을 잡는다면 임시테이블(#temp)을 기본으로 쓰고, 소량의 데이터를 잠깐 담아두고 처리하거나 트랙잭션 롤백과 무관하게 데이터를 유지해야하는 특수한 경우에만 테이블변수(@table) 을 사용하면 편리하게 이용할 수 있다.

 

[MSSQL] 임시테이블 활용하기 ( TempTable )