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

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

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 같은 경우 한 세션에서 실행되지만 임시 테이블이 삭제된 경우 반복 과정에서 오류가 날 수 있다는 점을 주의해야 한다.

 

 

[참고 자료]

 

 

No. Subject Author Date Views
2053 Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기 jevida(강성욱) 2017.01.11 3030
2052 Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화 jevida(강성욱) 2017.01.11 1577
2051 시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기 jevida(강성욱) 2017.01.11 2353
2050 In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고 jevida(강성욱) 2017.01.11 1277
2049 Spool 연산자와 추적 플래그 8690 jevida(강성욱) 2017.01.11 1857
2048 .NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 jevida(강성욱) 2017.01.11 1595
2047 함수 통계 정보 확인 (sys.dm_exec_function_stats) jevida(강성욱) 2017.01.11 1568
2046 NULL 데이터가 포함된 데이터 사용 시 주의점 jevida(강성욱) 2017.01.11 2821
2045 통계정보와 실제 데이터 분포 확인하기 jevida(강성욱) 2017.01.11 1669
2044 SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류 jevida(강성욱) 2017.01.11 1457
2043 SSIS 실행 로그 남기기 jevida(강성욱) 2017.01.11 2667
2042 확장이벤트를 사용한 실행 계획 캡처 jevida(강성욱) 2017.01.11 1211
2041 테이블 외래키 트리 확인 및 데이터 삭제하기 jevida(강성욱) 2017.01.11 1770
2040 기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 jevida(강성욱) 2017.01.11 1552
2039 SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 jevida(강성욱) 2017.01.11 1651
» SQL Server 임시 테이블 특성 jevida(강성욱) 2017.01.11 7137
2037 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) jevida(강성욱) 2017.01.11 2153
2036 컬럼스토어 인덱스 성능 (Columnsotre Index Performance) jevida(강성욱) 2017.01.11 3184
2035 In-Memory 최적화 파일 경로 변경 jevida(강성욱) 2017.01.11 1496
2034 SA 계정 이름 변경 및 비활성화 jevida(강성욱) 2017.01.11 2274





XE Login