탁이의 넋두리

SQL-99에 정의된 CTE는 파생 테이블의 일종으로 생각할 수 있습니다. 그러나 파생 테이블은 외부 쿼리에서 한 번 이상 참조할 수 없습니다.
이를 위해서 VIEW를 만들 수도 있지만 이는 쿼리 개체를 만들고 데이터베이스에 저장하기를 요구합니다.
CTE는 이와 같은 두 가지 어려움을 해소할 수 있는 방법을 제공합니다. 즉 파생 테이블처럼 쿼리에 직접 테이블의 쿼리를 작성하면서도 VIEW처럼 한 번 이상 참조가 가능한 것입니다.

* CTE의 두 가지 형식
   - 비 재귀적 CTE는 파생 테이블 형식으로 테이블 표현을 정의하고 이를 외부 쿼리에서 한 번 이상 참조할 경우에 유용하게 적용될 수 있습니다.
   - 재귀적 CTE는 순환 관계 모델을 처리할 경우 기본적으로 사용됩니다.

비재귀적 CTE

1. 구문 형식

2. 비재귀적 CTE를 정의하고 사용하기 위한 지침
   - CTE 다음에는 일부 또는 모든 CTE 열을 참조하는 SELECT, INSERT, UPDATE 또는 DELETE 문이 이어져야 합니다. VIEW의 SELECT 문 정의의 일부로 CREATE VIEW 문 내에 CTE를 지정할 수 있습니다.
   - 비재귀적 CTE 내에 여러 개의 CTE 쿼리 정의를 정의할 수 있습니다. UNION ALL, UNION, INTERSECT 또는 EXCEPT 집합 연산자 중 하나를 사용해 이 같은 정의를 결합해야 합니다.
   - CTE는 같은 WITH 절에서 자신 및 이전에 정의한 CTE를 참조할 수 있지만 전방 참조는 허용되지 않습니다.
   - CTE에 둘 이상의 WITH 절을 지정할 수 없습니다. 예를 들어 <CTE_쿼리 정의>가 하위 쿼리를 포함하는 경우 그 하위 쿼리가 또 다른 CTE를 정의하는 중첩 WITH 절을 포함할 수 없습니다.
   - <CTE_쿼리 정의>에는 다음 절을 사용할 수 없습니다.
      * COMPUTE 또는 COMPUTE BY
      * ORDER BY (TOP절을 지정하는 경우 제외)
      * INTO
      * 쿼리 힌트가 있는 OPTION 절
      * FOR XML
      * FOR BROWSE
   - 일괄 처리에 속한 문에 CTE를 사용할 때는 그 전의 문 다음에 반드시 세미콜론을 추가해야 합니다.
   - CTE를 참조하는 쿼리를 사용하여 커서를 정의할 수 있습니다.
   - CTE에서 원격 서버 상의 테이블을 참조할 수 있습니다.
   - CTE를 실행할 때는 쿼리의 VIEW를 참조하는 힌트와 마찬가지로 CTE를 참조하는 힌트가 CTE가 기본 테이블에 액세스할 때 발견되는 다른 힌트와 충돌을 일으킬 수 있습니다. 이러한 충돌이 발생하는 경우 쿼리에서 오류를 반환합니다.

3. 예제

<다음 예에서는 Adventure Works Cycles 의 각 관리자에게 직접 보고하는 직원 수를 보여 줍니다>

재귀적 CTE

1. 구문 형식

2. 재귀적 CTE를 정의하고 사용하기 위한 지침
   - 재귀적 CTE 정의는 적어도 하나의 앵커 멤버와 하나의 재귀 멤버로 두 개의 CTE 쿼리 정의를 포함해야 합니다. 앵커 멤버와 재귀 멤버를 여러 개 정의할 수 있지만 앵커 멤버 쿼리 정의는 모두 첫 번째 재귀 멤버 정의 앞에 와야 합니다. 모든 CTE 쿼리 정의는 CTE 자체를 참조하지 않는 한 앵커 멤버입니다.
   - 앵커 멤버는 UNION ALL, UNION, INTERSECT 또는 EXCEPT 집합 연산자 중 하나를 이용해 결합해야 합니다. UNION ALL은 여러 재귀 멤버를 결합할 때 마지막 앵커 멤버와 첫 번째 재귀 멤버 사이에서 허용되는 유일한 집합 연산자입니다.
   - 앵커 멤버 및 재귀 멤버에 있는 열의 수는 같아야 합니다.
   - 재귀 멤버에 있는 열의 데이터 형식은 앵커 멤버에 있는 해당 열의 데이터 형식과 반드시 같아야 합니다.
   - 재귀 멤버의 FROM 절은 <CTE expression_name>을 한 번만 참조해야 합니다.
   - 다음 항목은 재귀 멤버의 <CTE_쿼리_정의>에서 허용되지 않습니다.
      * SELECT DISTINCT
      * GROUP BY
      * HAVING
      * 스칼라 집계
      * TOP
      * LEFT, RIGHT, OUTER JOIN(INNER JOIN이 허용됨)
      * 하위 쿼리
   - <CTE_쿼리_정의> 내부의 CTE에 대한 재귀적 참조에 적용되는 힌트
   - 재귀적 CTE가 반환하는 모든 열은 참가하는 SELECT 문이 반환하는 열의 Null 허용 여부와는 상관없이 Null을 허용합니다.
   - 잘못 구성된 재귀적 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열에 대해 모두 같은 값을 반환하면 무한 루프가 생성된 것입니다. 무한 루프를 막기 위해서는 INSERT, UPDATE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트와 0부터 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준을 제한할 수 있습니다. 이 방법으로 루프를 발생시키는 코드 문제를 해결할 때까지 문의 실행을 제어할 수 있습니다. 서버 차원의 기본값은 100입니다. 0을 지정하면 아무런 한계도 적용되지 않습니다. 하나의 문에는 하나의 MAXRECURSION 값만 지정할 수 있습니다.
   - 재귀적 공통 테이블 식을 포함한 VIEW를 사용하여 데이터를 업데이트할 수 없습니다.
   - CTE를 사용하여 쿼리에 커서를 정의할 수 있습니다. 재귀적 CTE에는 빠른 전진 전용 커서 및 정적(스냅숏) 커서만 사용할 수 있습니다. 재귀적 CTE에 또 다른 커서 유형을 지정하는 경우 해당 커서 유형이 정적으로 변환됩니다.
   - CTE에서 원격 서버 상의 테이블을 참조할 수 있습니다. CTE의 재귀 멤버에서 원격 서버를 참조하는 경우 로컬에서 반복적으로 테이블에 액세스할 수 있도록 각 원격 테이블을 위한 SPOOL이 생성됩니다.

3. 예제

<다음 예에서는 관리자의 계층적 목록 및 이들에게 보고하는 직원을 보여 줍니다>

<UPDATE문에서 재귀적 CTE 사용>

<다음 예에서는 ManagerID 12에게 직접 또는 간접적으로 보고하는 모든 직원의 VacationHours 값을 25% 업데이트하는 방법을 보여 줍니다>

이전 버전(SQL Server 2000)에서는 데이터를 암호화(Encrypt) 하고 암호를 해독(Decrypt)하는데 있어서,
자체 개발이나 Third Party Vender의 솔루션을 이용해야 했습니다.
이런 문제점을 해결하기 위해 2005에서는 SQL Server 자체적으로 지원하고 있습니다.


1. EncryptByKey 또는 DecryptByKey
   - 대칭 키를 사용하여 데이터를 암호화 또는 복호화 합니다.
     대칭 암호화 및 암호 해독은 비교적 속도가 빠르며 대량의 데이터 작업 시 적합합니다.


2. EncryptByAsymKey 또는 DecryptByAsymKey
   - 비대칭 키를 사용하여 데이터를 암호화 또는 복호화 합니다.
     대칭 키로 암호화/복호화를 수행하는 것 보다 비용이 훨씬 많이 듭니다.
     큰 데이터의 집합으로 작업할 경우에는 비대칭 키를 사용하지 않는 것이 좋습니다.


3. EncryptByCert 또는 DecryptByCert
   - 인증서의 공개 키를 사용하여 데이터를 암호화 또는 복호화 합니다.
     대칭 키로 암호화/복호화를 수행하는 것 보다 비용이 훨씬 많이 듭니다.
     큰 데이터의 집합으로 작업할 경우에는 비대칭 키를 사용하지 않는 것이 좋습니다.


4. EncryptByPassPhrase 또는 DecryptByPassPhrase
   - 전달 구(대칭 키)로 데이터를 암호화 또는 복호화 합니다.
     전달 구에는 상대적으로 긴 문자열 대신 의미 있는 구나 문장이 사용되므로 기억하기가 쉽습니다.




※ 테스트해 보면서 알게 된 사실 하나는…
   실제로는 EncryptByAsymKey라는 명령어로 사용되는 것을, 도움말 또는 핑크색으로 표현되는 예약어 목록에
   EncryptByAsmKey로 잘못 기재되어 있다는 사실입니다. DecryptByAsymKey도 마찬가지였습니다.
   일종의 버그로 판단되는데요. SP2에서도 수정이 안되었는데, 과연 다음 서비스팩에서는 수정이 될까요? 의문이군요…

이전 버전인 SQL 2000에서는 페이징 처리에 관련된 변수의 사용이 많이 불편하였습니다.
"SELECT TOP ()"에 변수를 바로 적용할 수가 없어서, VARCHAR 변수에 담아서 EXECUTE문으로 일괄처리를 하는 방식으로 사용을 했었죠.
SQL 2005에서는 이러한 부분들이 많이 개선되었는데……
바로 TOP ()에 변수를 적용할 수가 있고, 또한 페이징 처리를 위한 ROW_NUMBER()라는 함수가 새로 생겼습니다.

SQL 2000에서의 쿼리는 TOP ()에 변수를 바로 적용할 수가 없어서 다음과 같이 적용하였습니다.


그러나 SQL 2005에서는 TOP()에 변수를 바로 적용하여 사용합니다.
예를 들면,



실제적인 페이징 처리를 위하여 SQL 2000에서는 TOP ()을 활용한 상관 하위 쿼리 등을 사용해서 원하는 쿼리 형식을 만들거나, 순번 혹은 순위 값을 처리하기 위한 기능으로 IDENTITY 속성을 지정한 임시 테이블 혹은 테이블 변수를 사용하거나 IDENTITY() 함수를 SELECT INTO문과 함께 적용하는 방법을 사용하였습니다.


SQL 2005에서는 TOP()에 변수를 직접 입력하거나 ROW_NUMBER() 함수를 이용하여, 페이징 처리를 바로 구현할 수가 있습니다.


앞서 소개한 TOP() 구문의 향상된 기능을 병행한다면 이전보다 훨씬 좋은 쿼리 형식을 만들 수 있을 것입니다.

- 성능 차이가 날 수도 있다.
- 그러나 동일하게 만들 수 있는 경우도 많다.
- (요구사항)따라서, 위 두 가지는 성능 상으로 동일해야한다

입니다. 어쨋든 단편적으로 보면, 차이가 날 수도 있다는 것이지요.

그러나 일반적으로는 성능을 고려하기보다는
그 문법적인 차이, 기능적인 차이에 기본을 두고 사용합니다.
(그러면 결과도 적합할 것 같습니다)

다음은, SET vs. SELECT 의 차이를 간단히 정리한 것입니다.

- SET은 ANSI 구문
- SET은 한 번에 한 변수만 할당 가능
- 쿼리에서 할당하는 경우, SET은 스칼라 값(단일값)만 가능
- 쿼리에서 반환되는 값이 없는 경우, SET 은 NULL 값을 할당,
 SELECT 는 할당 작업을 수행하지 않음. (이전 값을 그대로 가진다?)
- 성능 측면에서, 한 번에 여러 값을 할당하는 경우 SELECT 가 약간 우위