[MSSQL] Temporary 테이블 vs. Table 데이터 타입

Temporary 테이블 vs. Table 데이터 타입

김정선 (데브피아)

2002/04/17

SQL Server를 설치하고 나면, 기본적으로 제공되는 시스템 DB 중에 tempdb, 즉 Temporary DB가 있다. Temporary DB는 이름에서 풍기는 뉘앙스처럼 임시 데이터를 저장하기 위한 DB이다. 기본적으로 이 DB에는 테이블과 뷰를 포함한 시스템 테이블을 제외하고는 아무것도 없다. 그리고 일반적으로 알고 있듯이, SQL Server를 재시작하거나 서비스를 재시작하는 경우 초기 상태의 DB로 클리어된다 (실제로는 model 데이터베이스를 복사한다). 이같은 이유 때문에 tempdb에 중요하거나 영구적인 데이터를 저장해서는 안된다.

tempdb는 임시 오브젝트들을 만들어두고 재사용하기 위한 용도로 이용된다

참고. 임시 오브젝트에는 임시 테이블, 임시 프로시저 두 가지 타입이 있으며 오브젝트 이름 앞에 #(로컬) 또는 ##(전역)을 붙이는 것이 규칙이다.

임시 오브젝트 중에는 특히 임시 테이블을 만들어 사용하는 경우가 많다 (SQL Server는 잠금(Lock) 기능과 관련해 ##가 붙은 전역 임시 테이블을 사용하는 것을 볼 수가 있다)

이번에 살펴보고자 하는 내용은 임시 테이블에 대한 전반적인 내용과 SQL Server 2000에 새로 추가된 테이블 데이터 타입에 대한 것이다.

SQL은 집합과 관계 연산을 위한 언어이다. 즉, 집합에 대한 또 다른 집합 연산 그리고 경우에 따라 관계 연산을 순차적으로 적용해 결국 원하는 하나의 결과 집합을 산출하는 것이다. 아무리 복잡하고 어려운 결과물도 하나의 SQL문으로 해결한다.

고난도의 SQL 작성 능력을 갖추기 전에는 다양한 상황에서 요구되는 결과를 만들어 내기 위해, 저장 프로시저나 트리거내에서 절차적 프로그램을 수행하거나 커서를 돌리거나 심지어 클라이언트 애플리케이션 내에서 코드로 해결하기도 한다. 그 와중에 곧잘 임시 테이블을 사용하게 된다.

따라서 임시 테이블은 데이터 처리시 중간 단계의 결과를 저장하고, 그것을 기준으로 다음 단계에서 계속 데이터를 처리하기 위해 이용된다. 따라서 임시 테이블은 스테이징 테이블(Staging Table)이라고도 부른다. 하지만 임시 테이블보다 더 효율적인 방법을 이용해 많은 일을 해결할 수 있다는 점을 간과해서는 안 된다.

맛보기로 간단한 임시 테이블을 하나 작성해 보자.

USE Northwind

CREATE TABLE dbo.#LTable (

A INT IDENTITY(1,1)

, B INT

)

INSERT INTO dbo.#LTable VALUES (1)

SELECT * FROM dbo.#LTable

임시 테이블을 사용하는 것은 시스템의 추가 오버헤드를 발생시킨다. 하지만 SQL 활용 능력의 높고 낮음을 떠나 이것이 필요할 때도 있다.

1. 여러 단계를 거쳐 동일한 데이터에 반복작업을 해결할 때

2. 복잡한 쿼리를 단순화 시킬때

3. 서버상의 커서(Cursor) 사용으로 인한 부하를 줄이고자 할 때

임시 테이블 작성법

임시 테이블을 작성하는 방법은 일반 테이블 작성법과 동일하다. create table 또는 select into 구문을 이용하면 된다. 다른 점이 있다면, 테이블 이름 앞에 # 이나 ## 을 붙이고, 데이터가 tempdb에 저장된다는 것이다.

실제 예를 통해 두 오브젝트의 차이점을 보자.

로컬(Local) 임시 테이블 만들기

하나의 # 문자로 시작하는 테이블을 로컬 임시 테이블이라고 한다. 로컬(Local)의 의미는 해당 테이블을 만든 세션에 지역적이다라는 의미이다. 즉, 일반적으로 얘기하는 스코프(Scope, 참조범위)를 나타내는 것이다. 특정 세션에서 만든 로컬 임시 테이블은 다른 세션에서 참조할 수가 없다.

임시 테이블은 현재 데이터베이스에 만들어지는 것이 아니라는 것을 다시 한 번 상기시키기 바란다. 또한 여러분이 지정한 이름 그대로 만들어지는 것도 아니다. 아래 예제를 보자

USE Northwind

SELECT OrderID, OrderDate INTO #OrderInfo

FROM dbo.Orders

SELECT * FROM #OrderInfo

임시 테이블 찾기

만일 SQL Server 2000 사용자라면 개체 브라이저를 띄우고(F8) tempdb에서 사용자 테이블을 보자. 필요하다면 새로 고침(F5)를 한 번 해 주는 것도 도움이 된다. 7.0 사용자라면 아래 시스템 프로시저를 이용해 확인 할 수 있다.

EXEC sp_Help #OrderInfo -- Northwind에서는 찾을 수 없다.

USE tempdb

EXEC sp_help #OrderInfo -- 이름을 보자

SELECT * FROM tempdb.dbo.sysobjects -- 이렇게 확인 할 수도 있다.

WHERE name LIKE '#OrderInfo%'

방금 만든 #OrderInfo를 볼 수 있을 것이다. 또한 이름이 바뀐 것도 확인 할 수 있을 것이다.

임시 테이블의 이름은 아래와 같은 형식을 가진다.

1. 본래 테이블명 (#OrderInfo)

2. 엄청나게 많은 밑줄, 접미사

3. 12자리에 시스템에서 생성한 숫자, 접미사

이 세가지로 총 128자를 구성하게 된다.

참고. SQL Server에서 오브젝트명은 최대 128자까지 가능하다. 하지만 임시 오브젝트의 경우엔 위와 같은 특징 때문에 #문자를 포함해서 최대 116자 까지만 가능하다.

왜 이렇게 정신없이 이름을 지정하는 것일까? 가장 중요한 이유 중 하나는 동일한 이름의 임시 테이블을 여러 세션에서 만들 수 있어야 되기 때문이다.

로컬 임시 테이블의 참조 범위(Referential Scope, 이하 스코프)

앞에서 언급한 임시 테이블의 스코프를 예제로 확인 해 보자. 로컬 임시 테이블은 현재 세션에서만 참조가 가능하다고 했다. 그럼, 다른 세션에서는 참조할 수가 없다는 것이다.

간단하게 테스트할 수가 있다. 쿼리 분석기에서 새로운 윈도우를 하나 오픈하고(Ctrl-N) SELECT 해 보는 것이다.

USE Northwind

SELECT * FROM #OrderInfo

-- 결과

서버: 메시지 208, 수준 16, 상태 1, 줄 2

개체 이름 '#OrderInfo'이(가) 잘못되었습니다.

다른 세션에서는 #OrderInfo를 참조할 수가 없다는 것을 확인 할 수 있다. 말 그대로 현재 세션이 진행되는 동안 즉, LogIn->LogOff할 때까지의 범위 동안만 참조할 수가 있으며, 다른 세션에서는 참조할 수가 없게 된다.

로컬 임시 테이블은 언제 없어지는가? (Lifetime)

바로 위에서 확인한 내용에 한가지를 더 추가해서 생각해 보자.

로컬 임시 테이블은 다음과 같은 경우에 자동적으로 제거된다.

1. 해당 세션의 연결이 끊어진 경우(LogOff)

2. 저장 프로시저나 트리거에서 만들어졌을 경우

이 경우 해당 프로시저의 종료와 함께 임시 테이블이 제거된다.

확인 해 보자.

*/

상황 1. 현재 윈도우를 닫은 후 다시 연결하고 확인

*/

USE Northwind

SELECT * FROM #OrderInfo

-- 결과

서버: 메시지 208, 수준 16, 상태 1, 줄 1

개체 이름 '#OrderInfo'이(가) 잘못되었습니다.

/*

상황 2. 간단한 저장 프로시저를 작성하고, 테스트

*/

-- DROP PROC dbo.usp_TempPro

CREATE PROC dbo.usp_TempPro

AS

SET NOCOUNT ON

SELECT TOP 10 OrderID, OrderDate INTO #OrderInfo

FROM dbo.Orders

SELECT * FROM #OrderInfo

GO

EXEC dbo.usp_TempPro -- 프로시저 결과 확인

SELECT * FROM #OrderInfo -- 프로시저 종료 후 확인

비록, 저장 프로시저의 종료와 함께 자동으로 사라지긴 하지만, 명시적으로 DROP TABLE 명령을 이용해서 삭제하고 종료할 수도 있다.

전역 임시 테이블(Global Temporary Table)

전역 임시 테이블은 로컬 임시 테이블과는 약간의 차이점을 가진다. 실제 예제용 테이블을 하나 작성한 뒤 확인 해 보자.

테스트를 위해 쿼리 분석기에 추가 윈도우를 열어 두는게 좋다(Ctrl-N).

/*---------------

예제

----------------*/

USE Northwind

CREATE TABLE dbo.##GTable (

A INT

, B INT

)

INSERT INTO dbo.##GTable VALUES (1, 1)

SELECT * FROM dbo.##GTable

우선, 로컬 임시 테이블과의 차이점을 열거 한 뒤에 실제 예제를 통해 하나 하나 확인하도록 하자.

전역 임시 테이블의 특징.

1. ## 문자로 시작한다.

2. 동일한 이름의 임시 테이블을 가질 수 없다.

- 전역 임시 테이블은 다른 접미사가 붙지 않는다. 따라서 CREATE 전에 동일한 이름이 존재해서는 안되며 동일한 이름의 가진 테이블을 두 개 이상 만들 수도 없다.

3. 참조 범위(Scope)

- 모든 세션에서 참조할 수 있다(Global). 그렇게 할 수 있도록 설계돼야 한다.

4. 소멸 시점(Lifetime)이 다르다.

임시 테이블을 생성한 세션이 닫혀야 될 뿐 아니라, 다른 프로세스에서 참조하는 것도 해제되어야 한다.

예제를 통해 두번째 특징부터 살펴보자.

/*

상황 2. 중복된 이름을 가질 수 없다.

*/

-- 두번째 윈도우

CREATE TABLE dbo.##GTable (

A INT

, B INT

)

-- 결과

서버: 메시지 2714, 수준 16, 상태 6, 줄 1

데이터베이스에 '##GTable'(이)라는 개체가 이미 있습니다.

/*

상황 3. 참조 범위

*/

-- 두번째 윈도우

SELECT * FROM dbo.##GTable -- 정상적으로 참조가 된다.

/*

상황 4-1. 소멸 시점

*/

-- ##GTable 을 작성한 윈도우를 종료

-- 다른 윈도우에서 SELECT

SELECT * FROM dbo.##GTable

-- 결과

서버: 메시지 208, 수준 16, 상태 1, 줄 1

개체 이름 '##GTable'이(가) 잘못되었습니다.

/*

상황 4-2. 소멸 시점

*/

-- -1) ##GTable을 다시 작성

-- -2) 두 번째 윈도우에 아래 코드 작성 후 실행

BEGIN TRAN

INSERT INTO ##GTable VALUES (2, 2)

-- -3) ##GTable 작성한 윈도우 다시 종료

-- -4) 두 번째 윈도우에서 SELECT

SELECT * FROM ##GTAble

-- 정상적으로 출력된다.

-- -5) 두 번째 윈도우에서 아래 코드 실행 후 SELECT.

ROLLBACK TRAN

SELECT * FROM ##GTAble

-- 결과

서버: 메시지 208, 수준 16, 상태 1, 줄 1

개체 이름 '##GTAble'이(가) 잘못되었습니다.

이제 전역 임시 테이블에 대한 특징을 어느 정도 파악할 수 있을 것이다.

이제 임시 테이블을 보다 효율적으로 사용하기 위한 몇 가지 사항을 추가로 살펴보자.

인덱스, 제약(Constraints) 작성하기

임시 테이블 역시 물리적인 테이블 구조이기 때문에 일반적인 테이블에 적용하는 인덱스 및 제약을 거의 동일하게 사용할 수가 있다.

(약간의 차이가 있다)

실제로 임시 테이블은 중간 데이터를 저장한 후 여러 단계의 반복적인 데이터 처리를 위해 재사용되기 때문에 인덱스나 제약의 사용은 성능면에서 중요한 포커스가 된다. 또한 이후에 살펴 볼 테이블 데이터 타입이 가지지 못한 장점이기도 하다.

정리해 보면

1. 인덱스를 작성할 수 있다.

2. FK(외래키)를 제외한 나머지 제약을 지정할 수 있다.

- 테이블은 작성이 되지만 경고 메시지와 함께 FK 선언은 제외된다.

3. ALTER TABLE이 가능하다.

4. INSERT INTO, BULK INSERT 문과 함께 사용할 수 있다.

위와 같은 특징을 적절하게 사용하는 것이 성능면에서 도움을 얻을 수 있다.

/*

예제

*/

USE Northwind

-- FK를 제외한 제약 설정이 가능

-- DROP TABLE #Orders

CREATE TABLE #Orders (

OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED

, OrderDate datetime CHECK (OrderDate >= '1900-01-01')

, EmpID int

)

-- 추가 인덱스 작성, 인덱스 이름에도 #을 붙일 수 있다.

CREATE NONCLUSTERED INDEX #Idx ON #Orders (OrderDate)

-- ALTER 작업

ALTER TABLE #Orders

ADD CustomerID int DEFAULT (0)

SELECT * FROM #Orders

-- 완성된 테이블 구조 확인

USE tempdb

EXEC sp_help #Orders

Table 데이터 타입

SQL Server 2000 버전에서는 새로 소개된 몇 가지 데이터 타입이 있다. 바로 bigint(8바이트 정수형), sql_variant(VB의 variant형과 유사) 그리고 table 데이터 타입이다.

table 데이터 타입을 기존의 임시 테이블 대신에 사용할 수 있다. 물론 임시 테이블의 모든 기능을 동일하게 지원하지는 않지만, 상대적인 장점 또한 가지고 있다.

table 데이터 타입은 로컬 변수 형태로만 사용이 가능하다. 즉 실제 영구적인 테이블에서 특정 컬럼의 데이터 타입으로 사용하거나 저장 프로시저의 파라미터 타입으로는 사용할 수가 없다.

table 데이터 타입을 반드시 사용하게 되는 영역이 있다. 바로 UDF(사용자 정의 함수)의 세 가지 형식 중 Table-Value 함수 두 가지의 경우이다.

우선, table 데이터 타입에 대한 특징과 제약 사항을 보면

- table 데이터 타입의 특징

1) Primary Key(기본키), UNIQUE(유일키), CHECK 제약을 쓸 수 있다.

2) IDENTITY(식별자) 속성을 지정할 수 있다.

3) SELECT, INSERT, UPDATE, DELETE 문에 사용할 수 있다.

- table 데이터 타입의 제약 사항

1) FK를 사용할 수 없다.

2) ALTER TABLE를 할 수 없다.

3) 추가 인덱스를 선언할 수 없다.

그러나 PK, UNIQUE가 있다.

4) 로컬 변수나, UDF 안에서만 사용된다.

실제 예를 보자.

/*

예제

*/

USE Northwind

-- DROP PROC dbo.usp_TableVar

CREATE PROC dbo.usp_TableVar

AS

DECLARE @T table (

OrderID int identity(1,1) PRIMARY KEY NONCLUSTERED

, OrderDate datetime CHECK (OrderDate >= '1900-01-01')

)

INSERT INTO @T

SELECT TOP 10 OrderDate FROM Orders --OrderID 컬럼은

지정할 필요 없다.

SELECT * FROM @T

UPDATE @T SET OrderDate = OrderDate + 1

DELETE @T WHERE OrderDate > '1996-07-10'

SELECT * FROM @T

GO

EXEC dbo.usp_TableVar

table 데이터 타입이 상대적으로 많은 제약을 가지고 있지만 UDF에서 사용된다는 점과 성능이 향상된다는 장점도 있다.

임시 테이블 대신 table 데이터 타입을 가진 변수를 사용하거나 저장 프로시저에서 매번 임시 테이블을 CREATE, ALTER 그리고 SELECT 하게 되는 경우 해당 저장 프로시저는 매번 재컴파일(Recompile)을 해야되는 오버헤드가 발생된다. table 변수를 통해 이같은 추가 오버헤드를 줄일 수가 있다.

참고. 저장 프로시저의 재컴파일은 시스템의 처리량과 응답 속도라는 두 가지 성능 비교의 상당한 영향력을 미친다. 일반적으로 개발자는 재컴파일이 자주 발생하는 저장 프로시저를 찾아 문제를 해결하는 노력을 기울여야 한다.

UDF에서 table-value 형에 대한 사용 예는 차후에 자세히 소개하겠다.

Q/A

Q. 전역 임시 테이블을 tempdb에 항상 존재하게 할 수 있나?

A. SQL Server에서 제공하는 '자동 실행 저장 프로시저' 설정 기능을 이용하면 저장 프로시저에서 전역 임시 테이블을 작성하도록 구성하고 그 저장 프로시저를 SQL Server가 시작할 때마다 자동으로 실행되도록 구성할 수 있다. 시스템 프로시저와 sp_procoption에 대한 도움말을 참조하라.

Q. tempdb의 내용은 SQL Server가 재 시작될 때마다 다시 만들어진다고 했는데, 특정 오브젝트(UDT같은 것)가 늘 존재하게 할 수 있나?

A. model 데이터베이스에다 해당 오브젝트를 작성 해 두시면 된다. tempdb의 초기화는 model를 그대로 복사하기 때문이다.

Q. 한 저장 프로시저에 로컬 임시 테이블을 작성한 뒤 또 다른 저장 프로시저를 호출한 경우, 호출 받은 저장 프로시저에서도 이전의 임시 테이블을 참조할 수 있나?

A. 참조할 수 있다.

'모든 일엔 정도가 있다'는 말을 늘 실감하며 살고 있다. 아마 많은 분들도 그렇게 생각하리라 본다. 간단하게 소개하려던 것이, 한도 끝도 없이 늘어나는 것 같다. 이 글을 읽는 분들이 여력이 된다면, 실제로 임시 테이블을 사용한 경우와 그렇치 않은 경우에 대한 상황을 놓고 비교를 해보면 더 없는 좋은 공부가 될 것이다. @