SQL Server에서 임시 테이블 활용하기
임시 테이블은 SQL Server에서 효율적으로 데이터를 처리하고 분석하는 데 도움이 되는 훌륭한 도구입니다. 이 글에서는 임시 테이블의 개념과 사용 방법을 설명하고, 임시 테이블을 사용하여 데이터 처리 작업을 개선하는 방법을 살펴봅니다.
- 임시 테이블의 개요
임시 테이블은 일시적으로 사용되는 테이블로, 주로 중간 결과를 저장하거나 데이터 처리 작업을 단순화하기 위해 사용됩니다. 임시 테이블은 두 가지 유형이 있습니다.
1.1. 로컬 임시 테이블: 테이블 이름 앞에 '#' 기호가 붙어있으며, 해당 테이블을 생성한 세션에서만 사용할 수 있습니다. 세션이 종료되면 테이블은 자동으로 삭제됩니다.
1.2. 전역 임시 테이블: 테이블 이름 앞에 '##' 기호가 붙어있으며, 모든 세션에서 사용할 수 있습니다. 마지막 연결이 종료되면 테이블은 자동으로 삭제됩니다.
- 임시 테이블 생성 및 사용 임시 테이블을 만들고 사용하는 방법은 다음과 같습니다.
2.1. 임시 테이블 생성: CREATE TABLE 문을 사용하여 임시 테이블을 만듭니다. 로컬 임시 테이블의 경우 테이블 이름 앞에 '#' 기호를 붙이고, 전역 임시 테이블의 경우 '##' 기호를 붙입니다.
예시:
-- 로컬 임시 테이블 생성
CREATE TABLE #LocalTempTable (ID INT, Name NVARCHAR(50))
-- 전역 임시 테이블 생성
CREATE TABLE ##GlobalTempTable (ID INT, Name NVARCHAR(50))
2.2. 데이터 삽입 및 조회: 임시 테이블에 데이터를 삽입하거나 조회하기 위해 일반적인 INSERT, UPDATE, DELETE, SELECT 문을 사용합니다.
예시:
-- 데이터 삽입
INSERT INTO #LocalTempTable (ID, Name) VALUES (1, '홍길동')
-- 데이터 조회
SELECT * FROM #LocalTempTable
- 임시 테이블의 활용
임시 테이블을 사용하면 다음과 같은 이점이 있습니다.
3.1. 쿼리 성능 향상: 데이터 처리 작업의 중간 결과를 임시 테이블에 저장하여 전체 작업의 성능을 향상시킬 수 있습니다. 예를 들어, 복잡한 조인이나 집계 함수를 사용하는 쿼리에서는 중간 결과를 임시 테이블에 저장한 후 이를 기반으로 추가 작업을 수행할 수 있습니다.
3.2. 코드 가독성 및 유지 관리: 임시 테이블을 사용하면 긴 쿼리를 여러 단계로 분할하여 코드의 가독성과 유지 관리성을 높일 수 있습니다.
3.3. 데이터베이스 객체 간의 상호 작용: 저장 프로시저, 사용자 정의 함수, 트리거 등 다양한 데이터베이스 객체에서 임시 테이블을 사용할 수 있습니다. 이를 통해 데이터베이스 객체 간의 상호 작용을 단순화하고 효율성을 높일 수 있습니다.
- 임시 테이블 사용 시 주의사항
임시 테이블을 사용할 때 다음과 같은 주의사항을 염두에 두어야 합니다.
4.1. 스코프: 로컬 임시 테이블은 해당 테이블을 생성한 세션에서만 사용할 수 있으며, 세션이 종료되면 자동으로 삭제됩니다. 전역 임시 테이블은 모든 세션에서 사용할 수 있지만, 마지막 연결이 종료되면 삭제됩니다.
4.2. 동시성: 동시에 여러 사용자가 동일한 이름의 로컬 임시 테이블을 만들 수 있습니다. SQL Server는 테이블 끝에 임의의 숫자를 추가하여 서로 다른 연결에 대해 동일한 이름을 가진 테이블을 구별합니다.
4.3. 임시 테이블 과도한 사용: 임시 테이블을 과도하게 사용하면 TempDB의 공간을 많이 차지할 수 있으며, 이로 인해 전체 시스템의 성능에 영향을 줄 수 있습니다. 따라서 임시 테이블 사용 시 필요한 경우에만 사용하고, 사용 후에는 명시적으로 DROP TABLE 문을 사용하여 임시 테이블을 삭제하는 것이 좋습니다.
- 전역 임시 테이블 사용
전역 임시 테이블은 로컬 임시 테이블과 달리 여러 세션에서 사용할 수 있습니다. 전역 임시 테이블을 생성하려면 테이블 이름 앞에 '##' 기호를 붙여야 합니다. 전역 임시 테이블은 마지막 연결이 종료될 때 자동으로 삭제됩니다.
-- Create Global temporary table
Create Table ##myGlobalTable (id Int, Name nvarchar(20))
--Insert data into Temporary Tables
Insert into ##myGlobalTable Values (1,'Saurabh');
Insert into ##myGlobalTable Values (2,'Darshan');
Insert into ##myGlobalTable Values (3,'Smiten');
-- Select Data from the Temporary Tables
Select * from ##myGlobalTable
위의 쿼리는 전역 임시 테이블을 생성하고 데이터를 삽입한 후, 전역 임시 테이블에서 데이터를 선택합니다.
- 테이블 변수 사용
테이블 변수는 임시 테이블과 유사한 기능을 제공하지만, 일부 사용 사례에서 성능이 더 좋습니다. 테이블 변수는 DECLARE 문을 사용하여 선언되며, 일반적으로 사용되는 데이터 처리 작업에 적합합니다.
-- Declare table variable
DECLARE @myTableVariable TABLE (id INT, Name NVARCHAR(20))
-- Insert data into table variable
INSERT INTO @myTableVariable VALUES (1, 'Saurabh')
INSERT INTO @myTableVariable VALUES (2, 'Darshan')
INSERT INTO @myTableVariable VALUES (3, 'Smiten')
-- Select data from table variable
SELECT * FROM @myTableVariable
위의 쿼리는 테이블 변수를 선언하고 데이터를 삽입한 후, 테이블 변수에서 데이터를 선택합니다.
- 임시 테이블과 테이블 변수 비교
임시 테이블과 테이블 변수는 모두 일시적인 데이터 저장소로 사용되지만, 몇 가지 중요한 차이점이 있습니다.
7.1. 스코프: 임시 테이블은 세션 전체에서 사용할 수 있으며, 로컬 임시 테이블은 해당 세션에서만 사용할 수 있습니다. 테이블 변수는 선언된 배치 또는 저장 프로시저에서만 사용할 수 있습니다.
7.2. 트랜잭션 로그: 임시 테이블은 TempDB에서 트랜잭션 로그를 기록하므로 롤백이 가능합니다.
- 인덱스 사용
임시 테이블에서 인덱스를 사용하여 쿼리 성능을 향상시킬 수 있습니다. 테이블 변수에는 기본 키 또는 고유한 제약 조건을 사용하여 인덱스를 생성할 수 있지만, 임시 테이블에는 명시적으로 인덱스를 생성할 수 있습니다.
예를 들어, 다음 쿼리에서는 로컬 임시 테이블에 인덱스를 추가하고 사용합니다.
-- Create Local temporary table with index
CREATE TABLE #myTempTableWithIndex (
id INT,
Name NVARCHAR(20)
)
-- Insert data into Temporary Table
INSERT INTO #myTempTableWithIndex VALUES (1, 'Saurabh')
INSERT INTO #myTempTableWithIndex VALUES (2, 'Darshan')
INSERT INTO #myTempTableWithIndex VALUES (3, 'Smiten')
-- Create an index on the temporary table
CREATE NONCLUSTERED INDEX ix_myTempTableWithIndex ON #myTempTableWithIndex (id)
-- Select data from the Temporary Table using the index
SELECT * FROM #myTempTableWithIndex WHERE id = 2
위의 쿼리에서 로컬 임시 테이블에 데이터를 삽입한 후, 인덱스를 생성하고 인덱스를 사용하여 데이터를 선택합니다.
- CTE(Common Table Expression) 사용
CTE는 일시적인 결과 집합을 정의하는 데 사용되는 T-SQL 구문입니다. CTE는 재귀 쿼리를 작성할 수 있으며, 복잡한 쿼리를 여러 단계로 분해하여 가독성과 유지 관리성을 높이는 데 도움이 됩니다. CTE는 임시 테이블이나 테이블 변수와 비슷한 목적을 제공하지만, 더 간결한 구문을 사용합니다.
예를 들어, 다음 쿼리에서는 CTE를 사용하여 데이터를 선택합니다.
-- Define a CTE
WITH myCTE (id, Name) AS (
SELECT 1, 'Saurabh'
UNION ALL
SELECT 2, 'Darshan'
UNION ALL
SELECT 3, 'Smiten'
)
-- Select data from the CTE
SELECT * FROM myCTE WHERE id = 2
위의 쿼리에서 CTE를 정의하고 데이터를 선택합니다. CTE는 선언 즉시 사용되어야 하며, 세션 전체에서 사용할 수 없습니다.