MS SQL-파생 테이블 정리 & 공통 테이블 식(CTE) in SQL Server

테이블 원본 말고 나올 수 있는 여러 가지 테이블

Views, Table Variable, 로컬 임시 테이블, 글로벌 임시 테이블을 비교해 보자

 

뷰 Views,

 

Create view vWEmployeeCount
as
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
Group by DeptName, DepartmentId

Select DeptName, Total Employees from vWEmployeeCount
where TotalEmployees >= 2

메모 : 뷰는 데이터베이스에 저장된다. 그리고 다른 쿼리나 저장된 프로시저에 적용 가능하다. 그러나 뷰가 오직 한 곳을 위해 사용되었다면 CTE나 파생테이블이나 임시테이블과 테이블 변수 처럼 쉽게 삭제가 가능하다. 

Note : Views get saved in the database, and can be available to other queries and stored procedures. However, if this view is only used at this one place, it can be easily eliminated using other options, like CTE, Derived Tables, Temp Tables, Table Variable etc. 

 

 

임시 테이블 Temp Table

Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
into #TempEmployeeCount
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
group by DeptName, DepartmentId

Select DeptName, TotalEmployees
From #TempEmployeeCount
where TotalEmployees >= 2

Drop Table #TempEmployeeCount

메모 : 임시 테이블은 임시 데이터베이스에 저장된다. 지역 임시 테이블은 오직 현재 세션에서만 볼 수 있다. 그리고 중첩된 프로시저 간 호출에 불러질 수 있다. 전역 임시 테이블은 다른 세션에서도 표시되며 연결된 마지막 테이블이 닫힐 때 사라진다.

Note :  Temporary tables are stored in TempDB. Local temporary tables are visible only in the current session, and can be shared between nested stored procedure calls. Global temporary tables are visible to other sessions and are destroyed, when the last connection referencing the table is closed.

 

 

 

테이블 변수 Table Variable

Declare @tblEmployeeCount table*(DeptName nvarchar(20), DepartmentId int, TotalEmployees int)

Insert @tblEmployeeCount
Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
from tblEmployee
join tblDepartment
on tbEmployee.DepartmentId = tblDepartment.DeptId
Group by DeptName, DepartmentId

Select DeptName, TotalEmployees
From @tblEmployeeCount
where TotalEmployees >= 2

메모 : 임시 테이블처럼 테이블 변수도 임시 데이터베이스에 저장된다. 테이블 변수는 일괄 처리나, 저장된 프로시저나 선언된 스테이트먼트 블럭과 같은 범위를 가진다.

Note : Just like TempTables, a table variable is also created in TempDB. The scope of a table variable is the batch, stored procedure, or statement block in which it is declared. They can be passed as parameters between procedures.

 

파생 테이블 Derived Tables 

Select DeptN;ame, TotalEmployees
from
		(
        	Select DeptName, DepartmentId, COUNT(*) as TotalEmployees
            from tblEmployee
            join tblDepartment
            on tblEmployee.DepartmentId = tblDepartment.DeptId
            group by DeptName, DepartmentId
        )
as EmployeeCount
where TotalEmployees >= 2


--EmployeeCount is the derived table

메모 : 파생테이블은 오직 현재 쿼리의 내용에만 사용 가능하다.

Note : Derived tables are available only in the context of the current query.

 

 

공통 테이블 식 CTE (Common Table Expression) 

CTE는 복잡한 쿼리를 나눠서 할 수 있다. 단순 명료하게 진행 할 수 있다. 

With EmployeeCount(DeptName, DepartmentId, TotalEmployees)
as(
	Select DeptName, DepartmentId, Count(*) as TotalEmployees
    from tblEmployee
    join tblDepartment
    on tblEmployee.DepartmentId = tblDepartment.DeptId
    group by DeptName, DepartmentId
)

Select DeptName, TotalEmployees
from EmployeeCount
Where TotalEmployees >= 2

요구되는 테이블은 2명이상 있는 부서별 인원 수 이다. 

먼저,

부서별로 인원수를 집계하여 EmployeeCount라는 CTE를 만든다

 

만든 CTE가지고 where조건을 주어 쿼리문을 완성 시킨다.

 

그리고 with구문과 select사이에 바로 쓰지않고 다른 select가 들어가 있다면 Cte가 참조 되지 않는다.

 

 

메모 : 공통 테이블 식(CTE)는 단일 SELECT, INSERT, UPDATE, DELETE  또는 CREATE VIEW 문 하나의 실행 범위 내에 만들어진 임시 결과로 생각하면 된다. 공통 테이블 식은 객체로써 저장되지 않고 쿼리 기간 동안만 존재 한다는 것이 파생 테이블과 비슷하다.

Note : A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

 

 

 

 

참고 : 

docs.microsoft.com/ko-kr/archive/msdn-magazine/2007/october/data-points-common-table-expressions