[DB] MS SQL Procedure에서 Table구조를 저장하는 방법

Stored Procedure에서 종종 테이블 형태의 데이터를 임시로 저장해야 할 필요가 있습니다. MS SQL에서는 이를 위해 총 4가지 방법이 제공되고 있습니다.

비고	Local Temporary Table	Global Temporary Table	Permanent Table	Table Variable
문법	CREATE TABLE #tableName { Id INT } Table명은 116자 제한	CREATE TABLE ##tableName { Id INT } Table명은 116자 제한	CREATE TABLE tableName { Id INT } Table명은 128자 제한	DECLARE @tableName TABLE ( Id BIGINT, Name NVARCHAR(50) ) Table명은 128자 제한
범위	현재 Procedure 또는 Session, 하위 Procedure, Session	모든 세션	모든 세션	해당 Scope (Procedure, Function 등)
삭제	1. DROP TABLE 2. SCOPE 또는 Session이 종료되는 시점에 자동삭제	1. DROP TABLE 2. 현재 Session이 종료되고 참조하는 작업이 모두 종료되었을 때 자동삭제	DROP TABLE	Scope를 벗어나면 자동삭제
외부 TRANSACTION, ROLLBACK의 영향	예	예	예	아니요
LOCKING	최소한의 잠금 (현재 사용자만 Table에 유일하게 접근할 수 있기 때문)	Permanent Table보다 약간 작은 잠금	예	아니요
LOGGING	최소한의 로깅	Permanent Table보다 약간작은 LOGGING	예	최소
크기	제한없음	제한없음	제한없음	제한없음
수정 시 재컴파일	예	예		최소
저장	tempdb	tempdb		tempdb

Local Temporary Tables

Temporary Table은 System Database의 Tempdb에 생성되며 DISK에 저장됩니다. Table 이름 앞에 ‘#’를 붙입니다. 현재 Session, Procedure 내에서만 참조되고 범위를 벗어나면 자동으로 삭제됩니다. 또한 DROP TABLE를 통해 명시적으로 삭제할 수 도 있습니다.

--생성
CREATE TABLE #people
( 
    id BIGINT
)
--삭제
DROP TABLE #people

Local Temporary Table은 Session과 Procedure, 하위 Procedure에서 공유됩니다.

CREATE PROCEDURE dbo.NestedProcedure
AS SELECT NestedProcedure = id FROM #t;
GO

CREATE PROCEDURE dbo.RootProcedure
AS SELECT RootProcedure = id FROM #t;
EXEC NestedProcedure;
GO

CREATE TABLE #t(id INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
SELECT id  FROM #t;
GO

EXEC RootProcedure;
GO

[결과]

하위 Procedure에서 상위 Local Temporary Table명과 동일한 Local Temporary Table를 생성할 수 있습니다. 이 경우 상위 Local Temporary Table은 가려지게 됩니다. 단, 이 경우에는 반드시 상위 Local Temporary Table명과 하위 Local Temporary Table의 이름, 구조, 열이름 등 모두 일치해야 하며 그렇지 않으면 오류가 발생합니다.

CREATE PROCEDURE dbo.NestedProcedure
AS 
	CREATE TABLE #t(id INT PRIMARY KEY);
	INSERT INTO #t VALUES (2);
	SELECT NestedProcedure = id FROM #t;
GO

CREATE PROCEDURE dbo.RootProcedure
AS 
	CREATE TABLE #t(id INT PRIMARY KEY);
	INSERT INTO #t VALUES (1);
	SELECT RootProcedure = id FROM #t;
EXEC NestedProcedure;
GO

CREATE TABLE #t(id INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
SELECT id  FROM #t;
GO

EXEC RootProcedure;
GO

[결과]

[하위 Procedure에서 구조를 다르게 가져가는 경우]

여러 Session에 의해 동일한 Local Temporary Table Name이 사용될 수 있으나, 그 경우 MS SQL에서는 숫자 접미사를 Table명에 사용하여 각 Table을 구분합니다. 따라서 특정 이름의 Local Temporary Table이 존재하는지 확인하기 위해서는 다음과 같이 OBJECT_ID()를 사용해야 합니다.

-- TABLE 존재확인
IF OBJECT_ID('tempdb..#t') IS NOT NULL 
    SELECT '#t 존재함.' 
ELSE 
    SELECT '#t 존재하지 않음.'

-- 실제 TABLE명 확인
SELECT OBJECT_NAME(OBJECT_ID('tempdb..#t')) 

[결과]

TRANSACTION에 포함된 후 ROLLBACK 또는 COMMIT을 통해 TRANSACTION을 종료하지 않고 않고 취소하면 전체 tempdb에 LOCK이 걸리므로 유의해야 합니다.

Global Temporary Tables

## 접미사를 통해서 생성할 수 있으며 Local Temporary Table과 유사하게 동작하지만, 전체 세션에서 공유할 수 있다는 장점이 있습니다.

--생성
CREATE TABLE ##t
{ Id INT }
--삭제
DROP TABLE ##t

Local Temporary Table과 Global Temporary Table간의 이름 충돌은 없습니다. 반면에, 만약 두 세션이 동일한 이름으로 Global Temporary Table을 생성할 경우, 두 번째 세션에서는 다음과 같은 이름충돌 오류가 발생합니다.

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named '##people' in the database.

이미 Global Temporary Table이 존재하는지 확인하기 위해서는 Local Temporary Table과 마찬가지로 OBJECT_ID를 통해 TABLE이 존재하는지 확인해야 합니다.

-- TABLE 존재확인
IF OBJECT_ID('tempdb..##t') IS NOT NULL 
    SELECT '##t 존재함.' 
ELSE 
    SELECT '##t 존재하지 않음.'

-- 실제 TABLE명 확인
SELECT OBJECT_NAME(OBJECT_ID('tempdb..##t')) 

[결과]

Permanent Table

일반적으로 생성되는 Table입니다. Temporary Table, Table Variable와 달리 삭제하기 전까지는 영속성을 가지고 있습니다.

Permanant Table이 존재하는지 확인하기 위해서는 다음과 같은 방법을 사용합니다.

IF EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME='People') 
        SELECT 'People Table이 존재함.' 
ELSE 
        SELECT 'People Table은 존재하지 않음.' 

Table Variables

Table 변수는 기본적으로 메모리에 생성됩니다. 따라서 Local Temporarry Table에 비해 성능 상에서 약간 우위에 있습니다. (이는Transaction, Locking 또는 Logging을 사용하지 않거나 영향이 적기 때문이기도 합니다.) 하지만 Table 변수도 tempdb에 I/O를 발생시킨다고 하지만 이에 대한 자료가 부족해 I/O발생 시점이나 그로 인한 성능상의 영향에 대해서는 다소 불명확한 편입니다.

--Table변수 생성
DECLARE @t TABLE
(
    Id BIGINT PRIMARY KEY,
    Name NVARCHAR(50)
);

Table변수는 Temporary Table와 달리 자체가 변수로 취급되기 때문에 Scope를 벗어나면 자동으로 제거되며, 명시적으로 변수를 제거하는 방법은 없습니다.

Table 변수는 사용자정의 Function에서 사용할 수 있는 유일하게 DML을 수행할 수 있게 지원 합니다. Temporary Table은 물론 Permenent Table조차 사용자 정의 Function에서는 DML을 수행할 수 없습니다.

CREATE FUNCTION TestFunction() 
RETURNS INT 
AS 
BEGIN 
    CREATE TABLE #t1 (i INT) 
END 
SELECT TestFunction

[결과]

Table변수에 대한 기타 제약사항은 다음과 같습니다.

• SQL Server 2000 이상에서만 동작합니다.
• INSERT SELECT INTO 문을 사용할 수 없습니다.
• Table 변수는 일단 생성되면 DDL을 수행할 수 없습니다.
• Unique, Primary Key 제약조건을 제외하고는 사용자가 명시적으로 Index를 추가할 수 없습니다.
  

  --Table변수에 PK를 지정합니다.
  DECLARE @People TABLE 
  ( 
      Id BIGINT, 
      Name NVARCHAR(50), 
      PRIMARY KEY (Id, Name) 
  )

  [주의] PRIMARY KEY를 지정함에 있어서 CONSTRAINT를 통해 제약조건명을 지정할 수 없습니다

  DECLARE @People TABLE 
  ( 
      Id BIGINT, 
      Name NVARCHAR(50), 
      CONSTRAINT PeoplePK PRIMARY KEY (Id, Name) 
  )

  [결과]
  
• Temporary Table과 달리 하위 Procedure에서는 상위 Procedure에서 선언한 Table 변수를 호출할 수 없습니다.

Table 변수와 Local Temporary Table간의 선택기준은 다음이 있습니다.

1. 반복적으로 호출되어 자주 생성, 해제될 경우 Table 변수가 더욱 유리합니다.
2. Table 크기가 커질 수록 Temporary Table이 더욱 적절합니다.
3. Unique, Primary Key 제약조건 외에 Index를 사용해야하는 컬럼이 있을 경우 Temporary Table을 사용하세요.
4. Table 크기가 매우 방대해질 경우 Temporary Table 보다 Permanent Table 사용고려가 필요합니다.
5. 의사결정은 실제 성능 테스트 결과에 따라 진행하세요.