컴퓨터관련
[스크랩] Temporary 테이블 vs. Table 데이터 타입
맘편한넘
2011. 4. 7. 06:14
/*
-----------------------------------------------------------------------
소개
-----------------------------------------------------------------------
SQL Server를 설치하고 나면, 기본적으로 제공되는 시스템 데이터베이스 중
에는 tempdb 가 있다. 바로 Temporary Database이다.
Temporary Database는 이름에서 풍기는 뉘앙스처럼 임시 데이터를 저장하기
위한 용도의 데이터이다. 기본적으로 이 데이터베이스에는 시스템 테이블들
(테이블과 뷰 포함)을 제외하곤 아무것도 없다.
그리고 일반적으로 알고 있듯이, SQL Server를 재 시작하거나 혹은 서비스
를 재 시작하는 경우 초기 상태의 데이터베이스로 클리어된다.
(실제로는 model 데이터베이스를 복사한다)
tempdb에 중요한 또는 영구적인 데이터를 만들어 두어서는 안되는 이유이다.
당연하다. 서비스를 다시 시작하면 데이터는 다 날아간다. 훨~훨...
혹시, 그런 경험을 해 본 적이?
tempdb에는 어떤 데이터를 만들어 둘까? 말 그대로 임시 오브젝트들을 만들
어두고 재 사용하기 위한 용도이다.
<>
임시 오브젝트 중에서도 특히, 임시 테이블을 만들어 사용하는 경우가 많다.
(SQL Server는 잠금(Lock) 기능과 관련해서 ##가 붙은 전역 임시 테이블을
사용하는 것을 볼 수가 있다)
이번에 살펴보고자 하는 내용이 바로 임시 테이블에 대한 전반적인 내용,
그리고 SQL Server 2000에 새로 추가된 테이블 데이터 타입에 대한 것이다.
SQL은 집합과 관계 연산을 위한 언어이다. (물론 현대의 많은 DB 구현이
심한 계층 구조나, 심지어 객체 지향적인 데이터 모델을 요구하긴 하지만)
즉, 집합에 대한 또 다른 집합 연산 그리고 경우에 따라 관계 연산을 순차
적으로 적용 해서 결국 원하는 하나의 결과 집합을 산출하는 것이다.
아무리 복잡하고 어려운 결과물도 하나의 SQL문으로 해결한다!
(한 방(punch) SQL이라고 한다. 어디서 이런 말이 나왔는지 아는 사람은 알
것이다) 물론 이상적인 이야기다 - 그러나, 실제로 최고의 전문가들은 그런
SQL 만들어 낸다-
고난도의 SQL 작성 능력을 갖추기 전에는 다양한 상황에서 요구되는(때론,
너무나 터무니 없는...) 결과를 만들어 내기 위해, 저장 프로시저나 트리거
내에서 절차적 프로그램을 수행하거나 커서를 돌리거나 심지어 클라이언트
애플리케이션안에서 코드로 해결하기도 한다.
그 중심에서 곧 잘 임시 테이블을 사용하게 된다.
결과적으로, 임시 테이블을 쓴다는 것은 데이터 처리 시 중간 단계의 결과
데이터를 저장하고, 그 데이터를 기준으로 이어지는 단계에서 계속 데이터
처리를 하기 위한 용도이다 - 그래서 Staging Table이라고도 부른다 -
많은 경우에 임시 테이블은 더 효율적인 방법을 이용해서 해결할 수 있다는
점을 간과해서는 안 된다. 그럼에도 임시 테이블을 쓰는 이유는?
편하기 때문이 아닐까? (전적으로 필자의 생각!)
게다가, SQL Server 2000에는 테이블 데이터 타입(메모리 테이블) 기능까지
추가되었다. 따라서 기존의(7.0 버전까지) 임시 테이블을 많은 부분들을
테이블 데이터 타입 기능이 대체할 것으로 예상을 했었다-2000이 발표된 후-
테이블 데이터 타입의 기능도 마지막 단계에서 확인을 할 수 있다.
자, 그럼 임시테이블과 테이블 데이터 타입의 기능을 살펴보자.
--------------------------
맞보기
--------------------------
말 만 무수하게 늘어놓았다.
맛보기로 간단한 임시 테이블을 하나 작성해 보자.
(아직도 쿼리 분석기에 윈도우가 열려 있지 않다면, 지금이 기회이다!)
7.0 버전이라도 상관없겠지만, 2000 사용자는 더 편리할 것이다.
참고로, 임시 테이블은 Sybase에 흔적으로 알려져 있다.
*/
/*---------------
맛보기 예제
----------------*/
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) 임시 테이블 만들기
-----------------------------------------------------------------------
하나의 #(sharp) 문자로 시작하는 테이블은 로컬 임시 테이블이라고 한다.
로컬(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자를 구성하게 된다.
<>
왜 이렇게 정신없이 이름을 지정하는 것일까?
가장 중요한 이유 중에 하나는, 동일한 이름의 임시 테이블을 여러 세션에서
만들 수가 있어야 하기 때문이다.
-----------------------------------------------------------------------
로컬 임시 테이블의 참조 범위(Referential Scope, 이하 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
/*
우선, 로컬 임시 테이블과의 차이점을 열거 한 뒤에 실제 예제를
통해 하나 하나 확인하도록 하자.
백견이불여일RUN.
전역 임시 테이블의 특징.
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) 작성하기
-----------------------------------------------------------------------
임시 테이블 역시 물리적인 테이블 구조이기 때문에 일반적인 테이블에
적용하는 인덱스 및 제약을 거의 동일하게 사용할 수가 있다.
(약간의 차이가 있다)
실제로 임시 테이블은 중간 데이터를 저장한 뒤에, 여러 단계의 반복적인
데이터 처리를 위해 재 사용되기 때문에 인덱스나 제약의 사용은 성능면에서
중요한 포커스가 된다.
또한, 이후에 살펴 볼 Table 데이터 타입이 가지지 못한 장점이기도 하다.
정리를 해 보면
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 형에 대한 사용 예는 차후 UDF 강좌에서 자세하게
소개하기로 하겠다.
이제 마무리가 된 듯 하다!, 한 숨 쉬도록 하자.
*/
/*
-----------------------------------------------------------------------
묻고 답하기
-----------------------------------------------------------------------
Q. 전역 임시 테이블을 tempdb에 항상 존재하게 할 수는 없나요?
A. SQL Server에서 제공하는 '자동 실행 저장 프로시저' 설정 기능을 이용
하시면 저장 프로시저에서 전역 임시 테이블을 작성하도록 구성하고
그 저장 프로시저를 SQL Server가 시작할 때 마다 자동으로 실행되도록
구성하실 수 있습니다.
시스템 프로시저, sp_procoption에 대한 도움말 참조하십시오.
Q. tempdb의 내용은 SQL Server가 재 시작될 때마다 다시 만들어진다고
했는데, 특정 오브젝트(UDT같은 것)가 늘 존재하게 할 수는 없나요?
A. model 데이터베이스에다 해당 오브젝트를 작성 해 두시면 됩니다.
tempdb의 초기화는 model를 그대로 복사하기 때문입니다.
Q. 한 저장 프로시저에 로컬 임시 테이블을 작성한 뒤에 또 다른 저장 프로
시저를 호출한 경우, 호출 당한 그 저장 프로시저에서도 이전의 임시 테이
블을 참조할 수 있나요?
A. 예. 참조가 됩니다.
-----------------------------------------------------------------------
마무리
-----------------------------------------------------------------------
'모든 일엔 정도가 있다'는 말을 늘 실감하며 살고 있다.
아마 많은 분들이 그러시리라 생각한다.
간단하게 소개하려던 것이, 한도 끝도 없이 늘어나는 것 같아, 나머지 내용
은 여러분의 손에 맡겨두도록 하겠다!
그리고, 글의 오타나 이상이 있을 수도 있을 것이다. 본인에게 알려 주면 바로
수정해 놓도록 하겠다.
이 글을 읽는 분들이 여력이 된다면,
실제로 임시 테이블을 사용한 경우와 그렇치 않은 경우에 대한 상황을 놓고
비교를 해보면 더 없는 좋은 공부가 될 것이다.
차후에 그런 내용도 소개하도록 하겠다.
설날을 고향에서 보낸 뒤, 서울에 올라와서 곧바로 이 글을 작성했다.
새로운 시작과 함께 할 수 있는 자료가 되었으면 하는 바램이다.
새해 복 많이 받으시길...
*/
잘 읽으셨나요?.. 저 같은 경우는 Temp 테이블을 주로 사용하는데 Table 데이터 타입(이건 쉽게
말해서 변수란 거죠... 변수라고 말하죠..)을 이용하면 더 좋다는 소식을 들어서 제가 개발하던
것에 적용을 하려고 제 sp 를 보는 순간... 훔...
테이블 변수로는 불가능한 부분이 있더군요. 하나의 sp에서 동적쿼리에서 사용되는 값을 그것을
벗어나는 곳에서 사용하려면 결국 temp 테이블을 써야 한다는 것이죠..
말이 넘 복잡한가?... 예를 들죠..
sp 내에서.... 보통 업무를 보면 다양한 검색 조건을 원하기에.. 별 수 없이..
최초 데이터를 얻는 쿼리가 동적으로 생성되야만 할 경우가 있습니다.
create table #tmp_test (
...,
....
)
exec ('insert into #tmp_test select 어쩌구... ')
select #tmp_test ... 어쩌구..
이러한 구조가 저의 경우 많은데 이럴땐 테이블 변수로 대체가 불가능 하게 됩니다.
뭐 이런 경우가 없는 프로젝트가 좋은 프로젝트겠죠?.. ^^
이런 경우 아니라면 이제 테이블 변수를 쓰도록 하죠.. 모두들..
-----------------------------------------------------------------------
소개
-----------------------------------------------------------------------
SQL Server를 설치하고 나면, 기본적으로 제공되는 시스템 데이터베이스 중
에는 tempdb 가 있다. 바로 Temporary Database이다.
Temporary Database는 이름에서 풍기는 뉘앙스처럼 임시 데이터를 저장하기
위한 용도의 데이터이다. 기본적으로 이 데이터베이스에는 시스템 테이블들
(테이블과 뷰 포함)을 제외하곤 아무것도 없다.
그리고 일반적으로 알고 있듯이, SQL Server를 재 시작하거나 혹은 서비스
를 재 시작하는 경우 초기 상태의 데이터베이스로 클리어된다.
(실제로는 model 데이터베이스를 복사한다)
tempdb에 중요한 또는 영구적인 데이터를 만들어 두어서는 안되는 이유이다.
당연하다. 서비스를 다시 시작하면 데이터는 다 날아간다. 훨~훨...
혹시, 그런 경험을 해 본 적이?
tempdb에는 어떤 데이터를 만들어 둘까? 말 그대로 임시 오브젝트들을 만들
어두고 재 사용하기 위한 용도이다.
<>
임시 오브젝트 중에서도 특히, 임시 테이블을 만들어 사용하는 경우가 많다.
(SQL Server는 잠금(Lock) 기능과 관련해서 ##가 붙은 전역 임시 테이블을
사용하는 것을 볼 수가 있다)
이번에 살펴보고자 하는 내용이 바로 임시 테이블에 대한 전반적인 내용,
그리고 SQL Server 2000에 새로 추가된 테이블 데이터 타입에 대한 것이다.
SQL은 집합과 관계 연산을 위한 언어이다. (물론 현대의 많은 DB 구현이
심한 계층 구조나, 심지어 객체 지향적인 데이터 모델을 요구하긴 하지만)
즉, 집합에 대한 또 다른 집합 연산 그리고 경우에 따라 관계 연산을 순차
적으로 적용 해서 결국 원하는 하나의 결과 집합을 산출하는 것이다.
아무리 복잡하고 어려운 결과물도 하나의 SQL문으로 해결한다!
(한 방(punch) SQL이라고 한다. 어디서 이런 말이 나왔는지 아는 사람은 알
것이다) 물론 이상적인 이야기다 - 그러나, 실제로 최고의 전문가들은 그런
SQL 만들어 낸다-
고난도의 SQL 작성 능력을 갖추기 전에는 다양한 상황에서 요구되는(때론,
너무나 터무니 없는...) 결과를 만들어 내기 위해, 저장 프로시저나 트리거
내에서 절차적 프로그램을 수행하거나 커서를 돌리거나 심지어 클라이언트
애플리케이션안에서 코드로 해결하기도 한다.
그 중심에서 곧 잘 임시 테이블을 사용하게 된다.
결과적으로, 임시 테이블을 쓴다는 것은 데이터 처리 시 중간 단계의 결과
데이터를 저장하고, 그 데이터를 기준으로 이어지는 단계에서 계속 데이터
처리를 하기 위한 용도이다 - 그래서 Staging Table이라고도 부른다 -
많은 경우에 임시 테이블은 더 효율적인 방법을 이용해서 해결할 수 있다는
점을 간과해서는 안 된다. 그럼에도 임시 테이블을 쓰는 이유는?
편하기 때문이 아닐까? (전적으로 필자의 생각!)
게다가, SQL Server 2000에는 테이블 데이터 타입(메모리 테이블) 기능까지
추가되었다. 따라서 기존의(7.0 버전까지) 임시 테이블을 많은 부분들을
테이블 데이터 타입 기능이 대체할 것으로 예상을 했었다-2000이 발표된 후-
테이블 데이터 타입의 기능도 마지막 단계에서 확인을 할 수 있다.
자, 그럼 임시테이블과 테이블 데이터 타입의 기능을 살펴보자.
--------------------------
맞보기
--------------------------
말 만 무수하게 늘어놓았다.
맛보기로 간단한 임시 테이블을 하나 작성해 보자.
(아직도 쿼리 분석기에 윈도우가 열려 있지 않다면, 지금이 기회이다!)
7.0 버전이라도 상관없겠지만, 2000 사용자는 더 편리할 것이다.
참고로, 임시 테이블은 Sybase에 흔적으로 알려져 있다.
*/
/*---------------
맛보기 예제
----------------*/
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) 임시 테이블 만들기
-----------------------------------------------------------------------
하나의 #(sharp) 문자로 시작하는 테이블은 로컬 임시 테이블이라고 한다.
로컬(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자를 구성하게 된다.
<>
왜 이렇게 정신없이 이름을 지정하는 것일까?
가장 중요한 이유 중에 하나는, 동일한 이름의 임시 테이블을 여러 세션에서
만들 수가 있어야 하기 때문이다.
-----------------------------------------------------------------------
로컬 임시 테이블의 참조 범위(Referential Scope, 이하 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
/*
우선, 로컬 임시 테이블과의 차이점을 열거 한 뒤에 실제 예제를
통해 하나 하나 확인하도록 하자.
백견이불여일RUN.
전역 임시 테이블의 특징.
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) 작성하기
-----------------------------------------------------------------------
임시 테이블 역시 물리적인 테이블 구조이기 때문에 일반적인 테이블에
적용하는 인덱스 및 제약을 거의 동일하게 사용할 수가 있다.
(약간의 차이가 있다)
실제로 임시 테이블은 중간 데이터를 저장한 뒤에, 여러 단계의 반복적인
데이터 처리를 위해 재 사용되기 때문에 인덱스나 제약의 사용은 성능면에서
중요한 포커스가 된다.
또한, 이후에 살펴 볼 Table 데이터 타입이 가지지 못한 장점이기도 하다.
정리를 해 보면
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 형에 대한 사용 예는 차후 UDF 강좌에서 자세하게
소개하기로 하겠다.
이제 마무리가 된 듯 하다!, 한 숨 쉬도록 하자.
*/
/*
-----------------------------------------------------------------------
묻고 답하기
-----------------------------------------------------------------------
Q. 전역 임시 테이블을 tempdb에 항상 존재하게 할 수는 없나요?
A. SQL Server에서 제공하는 '자동 실행 저장 프로시저' 설정 기능을 이용
하시면 저장 프로시저에서 전역 임시 테이블을 작성하도록 구성하고
그 저장 프로시저를 SQL Server가 시작할 때 마다 자동으로 실행되도록
구성하실 수 있습니다.
시스템 프로시저, sp_procoption에 대한 도움말 참조하십시오.
Q. tempdb의 내용은 SQL Server가 재 시작될 때마다 다시 만들어진다고
했는데, 특정 오브젝트(UDT같은 것)가 늘 존재하게 할 수는 없나요?
A. model 데이터베이스에다 해당 오브젝트를 작성 해 두시면 됩니다.
tempdb의 초기화는 model를 그대로 복사하기 때문입니다.
Q. 한 저장 프로시저에 로컬 임시 테이블을 작성한 뒤에 또 다른 저장 프로
시저를 호출한 경우, 호출 당한 그 저장 프로시저에서도 이전의 임시 테이
블을 참조할 수 있나요?
A. 예. 참조가 됩니다.
-----------------------------------------------------------------------
마무리
-----------------------------------------------------------------------
'모든 일엔 정도가 있다'는 말을 늘 실감하며 살고 있다.
아마 많은 분들이 그러시리라 생각한다.
간단하게 소개하려던 것이, 한도 끝도 없이 늘어나는 것 같아, 나머지 내용
은 여러분의 손에 맡겨두도록 하겠다!
그리고, 글의 오타나 이상이 있을 수도 있을 것이다. 본인에게 알려 주면 바로
수정해 놓도록 하겠다.
이 글을 읽는 분들이 여력이 된다면,
실제로 임시 테이블을 사용한 경우와 그렇치 않은 경우에 대한 상황을 놓고
비교를 해보면 더 없는 좋은 공부가 될 것이다.
차후에 그런 내용도 소개하도록 하겠다.
설날을 고향에서 보낸 뒤, 서울에 올라와서 곧바로 이 글을 작성했다.
새로운 시작과 함께 할 수 있는 자료가 되었으면 하는 바램이다.
새해 복 많이 받으시길...
*/
잘 읽으셨나요?.. 저 같은 경우는 Temp 테이블을 주로 사용하는데 Table 데이터 타입(이건 쉽게
말해서 변수란 거죠... 변수라고 말하죠..)을 이용하면 더 좋다는 소식을 들어서 제가 개발하던
것에 적용을 하려고 제 sp 를 보는 순간... 훔...
테이블 변수로는 불가능한 부분이 있더군요. 하나의 sp에서 동적쿼리에서 사용되는 값을 그것을
벗어나는 곳에서 사용하려면 결국 temp 테이블을 써야 한다는 것이죠..
말이 넘 복잡한가?... 예를 들죠..
sp 내에서.... 보통 업무를 보면 다양한 검색 조건을 원하기에.. 별 수 없이..
최초 데이터를 얻는 쿼리가 동적으로 생성되야만 할 경우가 있습니다.
create table #tmp_test (
...,
....
)
exec ('insert into #tmp_test select 어쩌구... ')
select #tmp_test ... 어쩌구..
이러한 구조가 저의 경우 많은데 이럴땐 테이블 변수로 대체가 불가능 하게 됩니다.
뭐 이런 경우가 없는 프로젝트가 좋은 프로젝트겠죠?.. ^^
이런 경우 아니라면 이제 테이블 변수를 쓰도록 하죠.. 모두들..
출처 : 잡동사니 소스
글쓴이 : MUMMY!! 원글보기
메모 :