SQL Server 임시 테이블 특성
- Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016
임시 테이블은 tempdb 데이터베이스에 저장되었다가 더 이상 사용되지 않을 때 자동으로 삭제 된다. 그 외 기능은 일반 테이블과 비슷하다. 임시 테이블에는 전역과 로컬 두 유형이 있으며 로컬은 # 기호로 시작하며 전역은 ## 기호로 시작한다.
로컬 임시테이블은 현재 사용자의 연결에만 표시되고 사용자가 SQL Server 인스턴스와의 연결을 종료하면 삭제 된다.
전역은 테이블 생성 후 모든 사용자에게 표시되고 테이블을 참조하는 모든 사용자가 SQL Server인스턴스와 연결을 종료하면 삭제 된다. 즉 테이블을 생성한 사용자가 SQL Server와 연결을 끊어도 다른 사용자가 해당 테이블을 사용하고 있으면 다른 사용자의 사용이 종료될 때까지 기다렸다가 삭제된다.
실습을 통해서 전역 임시테이블의 특성에 대해서 살펴보자. 전역 임시 테이블은 테이블 이름 앞에 ## 기호로 식별되며 tempdb 데이터베이스에 저장된다.
CREATE TABLE ##tmpTest ( Value INT ) |
테이블 생성 후 전역 임시 테이블은 모든 사용자와 모든 연결에서 볼 수 있다. 전역 임시테이블들은 수동으로 drop table 명령으로도 삭제 할 수 있다. 작성자 세션이 종료되어도 다른 세션에서 해당 테이블이 활성세션에 참조 되어 있는 경우 SQL Server 기준에서 마지막 T-SQL 문이 완료 될 때까지 삭제되지 않으며 참조 세션 문을 실행하는 동안 테이블은 새 연결에 사용할 수 없다.
SSMS에서 새쿼리 창을 실행하여 아래 스크립트(쿼리1)를 실행한다. 스크립트는 임시 테이블을 생성하고 데이터를 삽입한다.
--Query 1
IF (OBJECT_ID('tempdb..##tmpTest') IS NOT NULL) DROP TABLE ##tmpTest
CREATE TABLE ##tmpTest ( Value INT )
DECLARE @i INT =1
WHILE (@i <= 1000000) --Value 1000000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading the server BEGIN
INSERT INTO ##tmpTest(Value) VALUES(@i)
SET @i=@i+1 END |
새 쿼리 창을 열고 아래 스크립트(쿼리2)를 실행 한다. 정상적으로 값이 조회되는 것을 확인할 수 있다.
--Query 2
SELECT TOP 1 * FROM ##tmpTest |
새 쿼리창을 실행하여 아래 스크립트(쿼리3)를 실행 한다. 그리고 쿼리1의 세션을 종료 한다.
--Query 3
SELECT * FROM ##tmpTest |
쿼리3이 실행되는 동안 쿼리2를 조회해 보면 정상적으로 값이 조회되는 것을 확인할 수 있다. 하지만 쿼리3의 작업이 완료되면 전역 임시테이블은 삭제되어 쿼리2에서 오류가 발생하는 것을 확인할 수 있다.
반복문의 경우 어떻게 작동하는지 알아본다. 쿼리 1을 실행하고 아래 스크립트(쿼리4)를 실행한다. 쿼리4가 진행 되는 동안 쿼리1의 세션을 종료 한다.
DECLARE @i INT =0, @val INT =0
WHILE (@i <= 1000000) BEGIN
SELECT @val=Value FROM ##tmpTest WHERE Value=@i
SET @i=@i+1
SELECT @val END |
쿼리4의 경우 하나의 T-SQL 명령문을 사용하지만 While 구문을 실행하면서 반복해서 테이블을 참조하는데 임시테이블이 삭제 후 참조하려고 했기 때문에 오류가 발생하였다.
이처럼 로컬 임시 테이블과 전역 임시 테이블은 비슷하지만 다른 특성이 있어 잘 파악하고 사용하여야 하며 쿼리를 개발 할 때 While 같은 경우 한 세션에서 실행되지만 임시 테이블이 삭제된 경우 반복 과정에서 오류가 날 수 있다는 점을 주의해야 한다.
[참고 자료]
- 테이블 속성
https://technet.microsoft.com/ko-kr/library/ms177399(v=sql.105).aspx
- Temp table 객체 생성시 세션간 충돌하지 않는 이유 : http://sqlmvp.kr/220400045796
