CTE (WITH 문)과 TEMP 테이블 비교

CTE (Common Table Expression)와 TEMP 테이블 비교

SQL Server에서 데이터를 조작하고 관리하는 데 있어 CTE (Common Table Expression)와 TEMP 테이블은 중요한 도구입니다. 이 글에서는 CTE와 TEMP 테이블의 정의, 사용 사례, 장단점 등을 비교하고 예시를 통해 이해를 돕겠습니다.

CTE (Common Table Expression)란?

정의

CTE는 SQL Server에서 특정 쿼리 내에서 일시적으로 이름이 부여된 결과 집합입니다. WITH 키워드를 사용하여 정의되며, 주로 복잡한 쿼리를 단순화하거나 재귀 쿼리를 작성할 때 사용됩니다.

예제

우선 비교를 위해 실습용Employee 테이블과 데이터를 생성합니다.

-- 실습용 테이블 생성
CREATE TABLE Employee (
    EmployeeID INT,
    Name VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);
-- 실습용 직원 데이터 추가
INSERT INTO Employee (EmployeeID, Name, DepartmentID, Salary)
VALUES (101, 'John Doe', 1, 60000),
       (102, 'Jane Smith', 2, 45000),
       (103, 'Alice Johnson', 1, 55000),
       (104, 'Bob Brown', 3, 70000),
       (105, 'Charlie Davis', 2, 48000);

다음은 간단한 CTE 예제입니다. Employee 테이블에서 부서별 평균 급여를 계산한 후, 평균 급여가 $50,000 이상인 부서만 선택합니다.

-- CTE 정의 및 사용
WITH DeptAvgSalary AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employee
    GROUP BY DepartmentID
)
SELECT DepartmentID, AvgSalary
FROM DeptAvgSalary
WHERE AvgSalary >= 50000;

TEMP 테이블이란?

정의

TEMP 테이블은 데이터베이스에 일시적으로 저장되는 테이블입니다. CREATE TABLE 구문을 사용하여 생성하며, 일반적으로 # 또는 ## 접두사를 사용합니다.(자세한 내용은 참고사항 항목을 확인하세요.) TEMP 테이블은 명시적으로 삭제되거나 세션이 종료될 때까지 유지됩니다.

예제

다음은 TEMP 테이블을 사용하는 예제입니다. Employee 테이블에서 부서별 평균 급여를 계산하고, 평균 급여가 $50,000 이상인 부서만 선택합니다.

-- TEMP 테이블 생성
CREATE TABLE #TempDeptAvgSalary (
    DepartmentID INT,
    AvgSalary DECIMAL(10, 2)
);

-- TEMP 테이블에 데이터 삽입
INSERT INTO #TempDeptAvgSalary
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY DepartmentID;

-- TEMP 테이블에서 조건에 맞는 데이터 조회
SELECT DepartmentID, AvgSalary
FROM #TempDeptAvgSalary
WHERE AvgSalary >= 50000;

-- TEMP 테이블 삭제
DROP TABLE #TempDeptAvgSalary;

반응형

주요 차이점

범위와 생명주기

• CTE: 특정 쿼리 실행 동안만 유효합니다. 쿼리가 종료되면 CTE도 사라집니다.
• TEMP 테이블: 명시적으로 삭제되거나 세션이 종료될 때까지 유지됩니다. 여러 번의 쿼리 실행 동안 데이터를 유지할 수 있습니다.

사용 사례

• CTE: 복잡한 쿼리를 간결하게 작성하거나 재귀 쿼리를 작성할 때 유용합니다. 예를 들어, 계층적 데이터를 처리할 때 사용됩니다.
• TEMP 테이블: 중간 결과를 저장하고 여러 번 참조해야 할 때, 또는 큰 데이터 집합을 여러 번 조작해야 할 때 유용합니다.

성능

• CTE: 쿼리가 복잡하거나 데이터가 많을 경우 성능이 저하될 수 있습니다. 특히, 큰 데이터 세트를 처리할 때는 TEMP 테이블보다 느릴 수 있습니다.
• TEMP 테이블: 물리적으로 데이터를 저장하므로 반복적인 데이터 접근 시 더 나은 성능을 제공합니다.

구문 및 사용 방법

• CTE: WITH 키워드를 사용하여 정의하며, 다음 SELECT 문 내에서만 참조됩니다.
• TEMP 테이블: 일반 테이블과 동일하게 정의되며, 여러 쿼리에서 참조할 수 있습니다.

장단점 비교

특징	CTE	TEMP 테이블
범위	특정 쿼리 내	세션 내 (명시적 삭제 또는 세션 종료 시)
사용 사례	복잡한 쿼리 단순화, 재귀 쿼리 작성	중간 결과 저장 및 다중 참조
성능	큰 데이터 처리 시 성능 저하 가능	반복적 접근 시 더 나은 성능
정의 방법	WITH 키워드를 사용한 쿼리 내 선언	CREATE TABLE을 사용한 명시적 선언
장점	쿼리 가독성 향상, 간편한 재귀 쿼리 작성	더 나은 성능, 다중 참조 가능
단점	복잡한 쿼리에서 성능 저하 가능	추가 스토리지 필요, 명시적 관리 필요

결론

CTE와 TEMP 테이블은 각각 고유한 장단점을 가지고 있으며, 사용 목적에 따라 적절히 선택하여 사용하는 것이 중요합니다. CTE는 쿼리 가독성을 높이고 간편하게 복잡한 쿼리를 작성하는 데 유용하며, TEMP 테이블은 큰 데이터 집합을 처리하거나 중간 결과를 저장하고 여러 번 참조해야 할 때 유용합니다. 두 도구 모두 SQL Server에서 효율적인 데이터 조작을 위해 필수적인 도구입니다.

각각의 예제를 실제 사용 사례에 맞게 적용해 보면서 CTE와 TEMP 테이블의 차이점을 직접 체험해 보시기 바랍니다. 이를 통해 더 나은 데이터 처리 전략을 수립할 수 있을 것입니다.

참고사항 - #와 ##의 차이 (단일 해시 TEMP 테이블, 이중 해시 TEMP 테이블)

특징	로컬 TEMP 테이블 (#TempTable)	글로벌 TEMP 테이블 (##TempTable)
범위	현재 세션에서만 유효	모든 세션에서 유효
생명주기	현재 세션이 종료되면 삭제	모든 세션이 종료될 때까지 유지
접근성	동일한 세션 내에서만 접근 가능	모든 세션에서 접근 가능