MSSQL 2005 T-SQL 새로운 기능들

목 차

1. 랭킹 함수
2. CTE(Common table expressions)기반 재귀쿼리
3. PIVOT, APPLY 관계 연산자
4. DRI(Declarative referential integrity - 선언적인 참조 무결성) 강화
5. 강화된 성능과 에러 핸들링
6. 기타

참고문헌

- 원문 URL
:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05tsqlenhance.asp
- AdventureWorks 샘플 데이터베이스 다운로드
:http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

기타

 MSSQL2005_T-SQL_New_Features-freeinmatrix.sql

본 내용을 스크립트 파일로 작성해서 첨부해 놓았습니다. 
SQL Server Management Studio에서 사용할 수 있습니다. 

1. 랭킹 함수
- 2005에서는 네개의 새로운 랭킹함수를 제공합니다.
ROW_NUMBER, RANK, DENSE_RANK, NTILE
일반적인 목적은 결과셋의 로우에 순차적으로 숫자를 할당하는 데 있다.
예를 들면 페이징, 스코어링, 히스토그램등...

USE tempdb or your own test database
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
track VARCHAR(10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)

SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
GO

◆ ROW_NUMBER
SCORE가 많은 순서대로 순위를 매긴다.(문제점 : 동점자 처리)
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC

rownum speaker track score
---------------------------------------------
1 Jessica Dev 9
2 Ron Dev 9
3 Suzanne DB 9
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8
7 Kevin DB 7
8 Brian Sys 7
9 Joe Dev 6
10 Robert Dev 6
11 Dan Sys 3

SCORE가 많은 순서대로 순위를 매긴다.
동점자의 경우 PCTFILLEDEVALS,NUMSESSIONS,SPEAKER 순으로...
(문제점 : 여전히 동점자가 생긴다.)
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,
numsessions DESC, speaker) AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

rownum speaker track score pctfilledevals numsessions
------------------------------------------------------------------
1 Ron Dev 9 30 3
2 Suzanne DB 9 30 3
3 Jessica Dev 9 19 1
4 Michele Sys 8 31 4
5 Kathy Sys 8 27 2
6 Mike DB 8 20 3
7 Kevin DB 7 25 4
8 Brian Sys 7 22 3
9 Robert Dev 6 28 2
10 Joe Dev 6 20 2
11 Dan Sys 3 22 4

SQL 2000에서는 위와 같은 결과를 내기 위해 아래와 같은 쿼리를
작성해야 했다. 복잡하다.
그 만큼 랭킹함수는 구문을 간략히 하고,
내부 실행에 있어서도 훨씬 효율적이다.
매 로우당 기준테이블의 로우 스캔을 해야하는데다
인덱스 사용을 할 수 없어 테이블 스캔을 해야하므로
성능이 떨어질 수 밖에 없다.
그에 비하면 SQL 2005에서는 ORDER BY에 사용되는
필드가 인덱스일 경우 해당 인덱스테이블을 사용할 수 있다.
SELECT
(SELECT COUNT(*)
FROM SpeakerStats AS S2
WHERE S2.score > S1.score
OR (S2.score = S1.score
AND S2.pctfilledevals > S1.pctfilledevals)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions > S1.numsessions)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions = S1.numsessions
AND S2.speaker < S1.speaker)) + 1 AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats AS S1
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

rownum speaker track score pctfilledevals numsessions
---------------------------------------------------------
1 Ron Dev 9 30 3
2 Suzanne DB 9 30 3
3 Jessica Dev 9 19 1
4 Michele Sys 8 31 4
5 Kathy Sys 8 27 2
6 Mike DB 8 20 3
7 Kevin DB 7 25 4
8 Brian Sys 7 22 3
9 Robert Dev 6 28 2
10 Joe Dev 6 20 2
11 Dan Sys 3 22 4

2005에서의 성능저하가 linear하다면 2000에서는 기하급수적이다.(로우수 비례)

일반적으로 ROW_NUMBER 함수는 페이징에서 사용할 수 있다.
페이지 사이즈가 3일때 두 번째 페이지를 출력하려면
넘버링을 한 테이블 D에서 순위 4위~6위까지를 출력하면 될 것이다.
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker

rownum speaker track score
---------------------------------------------
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8

좀 더 쓸모있게 확장해 보면 아래처럼...
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 1
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker
GO

rownum speaker track score
---------------------------------------------
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8

좀 더 성능을 높이려면 계산된(넘버링된) 테이블을 임시테이블로 만들고
인덱스를 설정한 후 사용하면 된다.
오직 해당 페이지의 로우만 스캔하게 될 것이다.
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 1
SET @pagesize = 3

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *
INTO #SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

DROP TABLE #SpeakerStatsRN
GO

rownum speaker track score
---------------------------------------------
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8

-- ◆ Partitioning - 파티셔닝
파티셔닝은 전체 로우에 일괄적으로 순번을 매기는 것이 아니라
특정 그룹내에서만 순번이 매겨지도록 할 때 사용된다.
아래구문은 PARTITION BY track 절 사용에 의해
동일 track 내에서만 순번이 매겨지도록 한다.
SELECT track,
ROW_NUMBER() OVER(
PARTITION BY track
ORDER BY score DESC, speaker) AS pos,
speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker

track pos speaker score
---------------------------------------------
DB 1 Suzanne 9
DB 2 Mike 8
DB 3 Kevin 7
Dev 1 Jessica 9
Dev 2 Ron 9
Dev 3 Joe 6
Dev 4 Robert 6
Sys 1 Kathy 8
Sys 2 Michele 8
Sys 3 Brian 7
Sys 4 Dan 3

-- ◆ RANK, DENSE_RANK
ROW_NUMBER와 아주 유사한 사용패턴을 갖는다.
ORDER BY에 의해 소팅되고, PARTITION BY에 의해 그룹내에서 적용된다.
이름에서도 알 수 있듯이 분명한 사용상의 차이가 있다.
ROW_NUMBER에서는 동점 로우가 존재해도 순번이 달리 매겨졌던데 비해
RANK,DENSE_RANK는 동점 로우에 같은 순번을 매긴다.
즉 말 그대로 전형적인 랭킹 함수라 할 수 있다.
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
RANK() OVER(ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC

speaker track score rownum rnk drnk
---------------------------------------------------------------------------------
Jessica Dev 9 1 1 1
Ron Dev 9 2 1 1
Suzanne DB 9 3 1 1
Kathy Sys 8 4 4 2
Michele Sys 8 5 4 2
Mike DB 8 6 4 2
Kevin DB 7 7 7 3
Brian Sys 7 8 7 3
Joe Dev 6 9 9 4
Robert Dev 6 10 9 4
Dan Sys 3 11 11 5

위 쿼리의 결과에서 알 수 있듯이
RANK의 경우 1,2,3,4위가 있을 때 1,2,3위가 같아 모두 1위로 매겨지게 되면
4위가 2위가 되지 않고 그대로 4위가 되는 것이고,
DENSE_RANK의 경우에는 4위가 2위로 매겨지는 차이가 있다.

-- ◆ NTILE
결과셋을 n개의 그룹으로 나누고 나뉘어진 그룹에 순번을 매긴다.
아래의 쿼리를 실행하면 11개의 로우를 score로 정렬한 후,
4개씩 그룹을 지어 순번을 매기게 되는데 마지막 그룹의 로우수는
다른 그룹과 다를 수 있다.(여기에서는 3개)
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC

speaker track score rownum tile
---------------------------------------------------------------
Jessica Dev 9 1 1
Ron Dev 9 2 1
Suzanne DB 9 3 1
Kathy Sys 8 4 1
Michele Sys 8 5 2
Mike DB 8 6 2
Kevin DB 7 7 2
Brian Sys 7 8 2
Joe Dev 6 9 3
Robert Dev 6 10 3
Dan Sys 3 11 3

NTILE의 활용예를 보자. 3개의 그룹으로 나누어
각 그룹별로 scorecategory를 부여하는 예이다.
SELECT speaker, track, score,
CASE NTILE(3) OVER(ORDER BY score DESC)
WHEN 1 THEN 'High'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'Low'
END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker

speaker track score scorecategory
--------------------------------------
Kevin DB 7 Medium
Mike DB 8 Medium
Suzanne DB 9 High
Jessica Dev 9 High
Joe Dev 6 Low
Robert Dev 6 Low
Ron Dev 9 High
Brian Sys 7 Medium
Dan Sys 3 Low
Kathy Sys 8 High
Michele Sys 8 Medium

2. 재귀쿼리와 CTE(Common Table Expressions)
◆ CTE
CTE는 VIEW와 Derived Table(인라인뷰라고도 한다.)의 장점을 모은것이다.
VIEW는 한 번 생성해 놓으면 삭제되기 전까지는 테이블처럼 사용할 수 있다.
Derived Table은 한 문장의 쿼리에서만 일시적으로 사용할 수 있다.
CTE는 View나 테이블처럼 영속성이 없고 하나의 배치쿼리내에서만
지속성을 갖을 수 있다. 즉, 배치쿼리내에서 선언적으로 정의한 후에는
여러 쿼리 문장에서 반복적으로 사용할 수 있다는 것이다.
View, Derived Table, CTE의 사용패턴을 보자.

▷ View
CREATE VIEW <view_name>(<column_aliases>)
AS
< view_query>
GO
SELECT *
FROM <view_name>

▷ Derived Table
SELECT *
FROM (<derived_table_query>) AS <derived_table_alias>(<column_aliases>)

▷ CTE
WITH <cte_alias>(<column_aliases>)
AS
(

<cte_query>
)
SELECT *
FROM <cte_alias>

CTE는 WITH 키워드를 사용해서 정의하는데
배치구문의 맨 앞에 CTE를 정의하지 않을 경우
WITH 키워드 앞에 세미콜론(;)을 반드시 입력해야 한다.
세미콜론은 FROM절에 사용되는 Hint 키워드등과의 혼동을 없애는 역할을 한다.

View, Derived Table, CTE의 사용예를 보도록 하자.
그 전에 AdventureWorks 데이터베이스를 만들어야 한다.
AdventureWorks 데이터베이스는 SQLEXPRESS버전일 경우
아래의 URL에서 다운로드 받아 설치한후 DB연결하면 된다.
AdventureWorks 샘플 데이터베이스 다운로드 :http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

▷ View
USE AdventureWorks
Go

CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN VEmpOrders AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN VEmpOrders AS OM
ON E.ManagerID = OM.EmployeeID

▷ Derived Tables

SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E

JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
ON E.ManagerID = OM.EmployeeID

▷ CTE

WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
)
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN EmpOrdersCTE AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN EmpOrdersCTE AS OM
ON E.ManagerID = OM.EmployeeID

EmployeeID NumOrders MaxDate ManagerID NumOrders MaxDate
--------------------------------------------------------------------------------
261 360 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
238 361 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
264 362 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
241 360 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
198 164 2004-10-23 00:00:00.000 274 160 2004-09-01 00:00:00.000
244 361 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
233 360 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
164 361 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
231 362 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
223 400 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
266 401 2004-09-03 00:00:00.000 274 160 2004-09-01 00:00:00.000
274 160 2004-09-01 00:00:00.000 71 NULL NULL

WITH 키워드 이후 CTE정의는 콤마(,)를 사용해서 복수개 정의할 수 있다.
우선 정의된 CTE는 곧 이어 다른 CTE정의 때에 곧 바로 사용할 수도 있다.
이 때에는 뒤에서 정의된 CTE는 앞에서 정의된 CTE에서 사용할 수 없다.
즉, 순차적으로 참조할 수 있고 자기 자신도 참조할 수 있다.
예를 들면, A_CTE, B_CTE, C_CTE에서 B_CTE는 A_CTE와 B_CTE를 참조할 수 있고,
C_CTE는 참조할 수 없다.
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader

GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE

MN MX Diff
-----------------------------
160 401 241

◆ 재귀쿼리(Recursive Queries)
사용패턴
WITH RecursiveCTE(<column_list>)
AS
(
Anchor Member:(AM - 고정멤버)
SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM <some_table(s)>
...
UNION ALL
Recursive Member(RM - 재귀멤버)
SELECT query that refers to RecursiveCTE
SELECT ...
FROM <some_table(s)>
JOIN RecursiveCTE

...
)
Outer Query
SELECT ...
FROM RecursiveCTE
...

재귀 CTE의 동작 방식은 아래와 같다.
1. 고정멤버가 활성화된다. 첫번째 결과셋 R0를 만들어낸다.
2. 재귀멤버가 활성화된다. 고정멤버가 빈 결과셋을 반환할 때까지 스텝(스텝번호=i)을
증가시키면서 Ri를 만들어낸다. 이 때 만들어진 결과셋들은 UNION ALL 구문으로 결합된다.
일반적인 재귀함수의 작동방식을 생각해내면 이해가 빠를 것이다.

사용예를 봅시다.
싱글패어런트 예제 : 사원 조직도
USE tempdb
Go
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
Go

Employees 테이블의 데이터 구조는 아래와 같다.
1(Nancy)
┌───────────────┴─┬────────────────────────────┐
2(Andrew) 3(Janet) 4(Margaret)
┌─┴───┐ ┌──┴─────┬────────────┐ │
5(Steven) 6(Michael) 7(Robert) 8(Laura) 9(Ann) 10(Ina)
이하 생략...알아서 상상하시길...

Robert(empid = 7)를 포함해서 Robert가 관리하고 있는 사원을 모두 출력해 보자(레벨순으로)
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL

Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

empid empname mgrid lvl
----------------------------------------------------
7 Robert 3 0
11 David 7 1
12 Ron 7 1
13 Dan 7 1
14 James 11 2

(1) 고정멤버 활성화됨. empid=7인 Robert를 가져온다.
(2) 재귀멤버 활성화됨. Employees테이블에서 mgrid=7인 사원들을 가져와서 UNION ALL한다.
David, Ron, Dan을 가져오는데, 다시 재귀멤버가 활성화되어
David, Ron, Dan이 관리하는 사원들을 가져오게 된다.
이 과정이 반복되는데 더 이상 하위 사원들이 없을 때까지 반복한다.
David가 관리하는 James 밖에 없으므로 스텝 두 번째에서 재귀호출 종료된다.
* lvl은 재귀호출의 스텝번호와 같음을 알 수 있고, 조직의 레벨값이된다.

WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol

empname
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(1) Nancy
| (2) Andrew
| | (5) Steven
| | (6) Michael
| (3) Janet
| | (7) Robert
| | | (11) David
| | | | (14) James
| | | (12) Ron
| | | (13) Dan
| | (8) Laura
| | (9) Ann
| (4) Margaret
| | (10) Ina

위 예제는 각자 분석해 보고,
더 많은 예제는 원문을 참조하기 바란다.

3. PIVOT and UNPIVOT
PIVOT은 말 그대로 행과 열을 바꾸는 것이라 생각하면 된다.
주로 크로스탭 결과셋을 만들 때 가장 많이 필요한 기능이다.

이해를 돕기 위해 크로스탭 질의의 간단한 예를 살펴보자.

OT(년도,수출입구분,수출입물량)이라는 OT테이블이 있다.
데이터는 아래의 표와 같다.

연도 구분 물량
--------
2000 수출 20
2000 수입 120
2001 수출 50
2001 수입 130
2002 수출 80
2002 수입 135

연도별 수출입 물량 추이는 아래와 같다.
구분 2000 2001 2002
-- --------
수출 20 50 80
수입 120 130 135

OT테이블의 연도 데이터(열 : 2000,2001,2002)가 행(컬럼)으로 바뀐 것이다.
위 테이블이 바로 크로스탭 결과셋(보고서)이다.
OT테이블에서 크로스탭 결과셋을 출력하는 쿼리를 생성하는 것은 복잡한 작업이다.
하지만 크로스탭 결과셋을 출력해야 하는 경우는 실무에선 정말 빈번한 업무라 할 수 있다.
SQL 2005에서 추가된 PIVOT 함수가 이런 어려움을 아름답게 해결해 주었다고 한다.
눈치를 챗다면 알겠지만, 행과 열을 바꾼다는 의미는 다시말해
테이블 스키마를 동적으로 변경해야 하는(필드가 고정되지 않고 동적으로 증감하는)
경우에 필요한 것임을 알 수 있다.
위의 예에서도 알다시피 OT테이블에서 연도컬럼의 로우셋이 크로스탭 결과셋에서는
컬럼으로 바뀌었다는 것을 알 수 있다.
즉, 정상적인 테이블로 설계한다면
OT2(수출입구분, 2000년, 2001년, 2002년) 이라는 OT2테이블을 설계해야 했던 것이다.
하지만 연도데이터는 2003년, 2004년 계속 늘어날 수 있고 또는 생기지 않을 수 있는
동적 데이터이기 때문에 OT2테이블 처럼 테이블구조(스키마)를 고정시킬 수 없는 것이고,
따라서 OT테이블에서 크로스탭 질의를 사용해 크로스탭 보고서를 생성할 수 밖에 없다.

이번에는 실제 데이터를 사용해서 자세히 살펴보도록 하자.
옥션에서 경매에 붙여지는 다양한 물품(아이템)정보를 표현해 보도록 하자.
AuctionItems테이블은 아이템 정보를 갖는 마스터 테이블이다.
동일 아이템도 등록된 시점에 따라 다른 아이템으로 취급된다.
CREATE TABLE AuctionItems
(
itemid INT NOT NULL PRIMARY KEY NONCLUSTERED,
itemtype NVARCHAR(30) NOT NULL,
whenmade INT NOT NULL,
initialprice MONEY NOT NULL,

other columns

)
CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid
ON AuctionItems(itemtype, itemid)
INSERT INTO AuctionItems VALUES(1, N'Wine', 1822, 3000)
INSERT INTO AuctionItems VALUES(2, N'Wine', 1807, 500)
INSERT INTO AuctionItems VALUES(3, N'Chair', 1753, 800000)
INSERT INTO AuctionItems VALUES(4, N'Ring', -501, 1000000)
INSERT INTO AuctionItems VALUES(5, N'Painting', 1873, 8000000)
INSERT INTO AuctionItems VALUES(6, N'Painting', 1889, 8000000)

ItemAttributes는 AuctionItems에 등록된 아이템별 속성정보를 갖는 테이블이다.
속성은 아이템별로 다른 정보를 갖을 수 있으므로 고정된 컬럼을 갖는 테이블로
설계할 수 없다.
CREATE TABLE ItemAttributes
(
itemid INT NOT NULL REFERENCES AuctionItems,

attribute NVARCHAR(30) NOT NULL,
value SQL_VARIANT NOT NULL,
PRIMARY KEY (itemid, attribute)
)
INSERT INTO ItemAttributes
VALUES(1, N'manufacturer', CAST(N'ABC' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(1, N'type', CAST(N'Pinot Noir' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(1, N'color', CAST(N'Red' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(2, N'manufacturer', CAST(N'XYZ' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(2, N'type', CAST(N'Porto' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(2, N'color', CAST(N'Red' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(3, N'material', CAST(N'Wood' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(3, N'padding', CAST(N'Silk' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(4, N'material', CAST(N'Gold' AS NVARCHAR(15)))
INSERT INTO ItemAttributes
VALUES(4, N'inscription', CAST(N'One ring ...' AS NVARCHAR(50)))
INSERT INTO ItemAttributes
VALUES(4, N'size', CAST(10 AS INT))
INSERT INTO ItemAttributes
VALUES(5, N'artist', CAST(N'Claude Monet' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'name', CAST(N'Field of Poppies' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'type', CAST(N'Oil' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(5, N'height', CAST(19.625 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(5, N'width', CAST(25.625 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(6, N'artist', CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'name', CAST(N'The Starry Night' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'type', CAST(N'Oil' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
VALUES(6, N'height', CAST(28.75 AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
VALUES(6, N'width', CAST(36.25 AS NUMERIC(9,3)))

GO

아이템구분이 painting인 아이템(5,6)을 각 어트리뷰트별로 출력해 보자.
PIVOT 연산자를 사용하지 않는다.
SELECT
itemid,
MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist],
MAX(CASE WHEN attribute = 'name' THEN value END) AS [name],
MAX(CASE WHEN attribute = 'type' THEN value END) AS [type],
MAX(CASE WHEN attribute = 'height' THEN value END) AS [height],
MAX(CASE WHEN attribute = 'width' THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid

itemid artist name type height width
--------------------------------------------------
5 Claude Monet Field of Poppies Oil 19.625 25.625
6 Vincent Van Gogh The Starry Night Oil 28.750 36.250

이번에는 PIVOT 연산자를 사용해서 같은 결과를 출력해 보자.
SELECT *
FROM ItemAttributes AS ATR
PIVOT

(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(5,6)

itemid artist name type height width
--------------------------------------------------
5 Claude Monet Field of Poppies Oil 19.625 25.625
6 Vincent Van Gogh The Starry Night Oil 28.750 36.250

더 다양한 사용 예는 원문을 참조하기 바란다.

◆ UNPIVOT
UNPIVOT은 PIVOT의 반대개념으로 보면된다.
테이블의 컬럼을 로우셋으로 만드는 것이다.
예를 들면 AuctionItems 테이블의 itemtype, whenmade, initialprice를 attribute 컬럼의 로우셋으로
각 컬럼의 원래 값을 value 컬럼의 로우셋으로 변경한다.
SELECT * FROM AuctionItems

itemid itemtype whenmade initialprice
-------------------------------------------------------------------
3 Chair 1753 800000.00
5 Painting 1873 8000000.00
6 Painting 1889 8000000.00
4 Ring -501 1000000.00
1 Wine 1822 3000.00
2 Wine 1807 500.00

SELECT itemid, attribute, value
FROM (SELECT itemid,
CAST(itemtype AS SQL_VARIANT) AS itemtype,
CAST(whenmade AS SQL_VARIANT) AS whenmade,
CAST(initialprice AS SQL_VARIANT) AS initialprice
FROM AuctionItems) AS ITM
UNPIVOT
(
value FOR attribute IN([itemtype], [whenmade], [initialprice])
) AS UPV

itemid attribute value
-----------------------------
1 itemtype Wine
1 whenmade 1822
1 initialprice 3000.00
2 itemtype Wine
2 whenmade 1807
2 initialprice 500.00
3 itemtype Chair
3 whenmade 1753
3 initialprice 800000.00
4 itemtype Ring
4 whenmade -501
4 initialprice 1000000.00
5 itemtype Painting
5 whenmade 1873
5 initialprice 8000000.00
6 itemtype Painting
6 whenmade 1889
6 initialprice 8000000.00

◆ APPLY
Outer 테이블(기준테이블)의 레코드 단위로 테이블반환 함수를 호출할 수 있다.
JOIN 연산자와 흡사하게 작동하는데,
쉽게 말하면 SELECT * FROM OT 문장에서 OT테이블의 레코드 하나 하나마다
함수가 호출되어 반환된 레코드셋과 조인된다고 볼 수 있다.
APPLY 연산자는 CROSS APPLY와 OUTER APPLY가 있다.

▷ CROSS APPLY

두 개의 정수를 입력 받아 작은 값과 큰 값을 갖는 결과셋을 반환하는
함수를 생성한다.
CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT) RETURNS TABLE
AS
RETURN
SELECT
CASE
WHEN @p1 < @p2 THEN @p1
WHEN @p2 < @p1 THEN @p2
ELSE COALESCE(@p1, @p2)
END AS mn,
CASE
WHEN @p1 > @p2 THEN @p1
WHEN @p2 > @p1 THEN @p2
ELSE COALESCE(@p1, @p2)
END AS mx
GO
SELECT * FROM fn_scalar_min_max(10, 20)

mn mx
--------------------
10 20

기준테이블 T1을 만들어보자.
CREATE TABLE T1
(
col1 INT NULL,
col2 INT NULL
)

INSERT INTO T1 VALUES(10, 20)
INSERT INTO T1 VALUES(20, 10)
INSERT INTO T1 VALUES(NULL, 30)
INSERT INTO T1 VALUES(40, NULL)
INSERT INTO T1 VALUES(50, 50)

T1의 각 레코드별로 fn_scalar_min_max 함수를 호출한다.
SELECT *
FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M

col1 col2 mn mx
--------------------------------------
10 20 10 20
20 10 10 20
NULL 30 30 30
40 NULL 40 40
50 50 50 50

첫째 레코드를 보면 10, 20을 넘겨서 mn,mx(10,20)
둘째 레코드를 보면 20, 10을 넘겨서 mn,mx(10,20)
셋째 레코드를 보면 NULL, 30을 넘겨서 mn,mx(30,30)
....

눈치 챗나?
기준 테이블의 레코드개수만큼 fn_scalar_min_max가 호출되어 fn_scalar_min_max이
토해낸 결과셋 만큼 더해져 출력되는 것이다.
fn_scalar_min_max의 결과레코드개수가 n개 이상이면 기준테이블의 개수가 * n 개 된다.

좀 더 확실히 디벼보기 위해 부서테이블을 만들고 이전에 만들어 두었던
Employees 테이블과 연결해서 살펴보도록 하자.
Departments(부서)테이블을 만든다.
deptmgrid는 Empoloyees테이블의 empid와 연결된다.
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
SET NOCOUNT ON
INSERT INTO Departments VALUES(1, 'HR', 2)
INSERT INTO Departments VALUES(2, 'Marketing', 7)
INSERT INTO Departments VALUES(3, 'Finance', 8)
INSERT INTO Departments VALUES(4, 'R&D', 9)
INSERT INTO Departments VALUES(5, 'Training', 4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)
GO

재귀쿼리(CTE)에서 살펴봤던 조직도를 반환하는 사용자함수를 만든다.
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION all
Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
JOIN employees_subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO

CROSS APPLY를 사용해 부서테이블 기준으로 해당 부서에 소속된 사원들을
트리형태로 구해보자.
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

deptid deptname deptmgrid empid empname mgrid lvl
-------------------------------------------------------
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1

그런데 결과셋을 보니 무언가 이상하다.
Gardening 부서가 출력되지 않았다.
CROSS APPLY는 함수에서 리턴되는 결과셋이 없을 경우
기준테이블의 레코드를 출력하지 않는 것이다.

▷ OUTER APPLY
Gardening 부서를 출력하는 방법은?
OUTER APPLY를 사용하는 것이다.
OUTER APPLY는 CROSS APPLY와 거의 흡사한 결과를 출력하지만(사용법도 같다.)
OUTER Table(기준테이블)의 모든 레코드를 기본으로 함수에서 리턴되는 결과셋이 없더라도
기준테이블의 레코드는 모두 출력한다.

SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

-------------------------------------------------------
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
6 Gardening NULL NULL NULL NULL NULL

◆ 연관된 서브쿼리에서 테이블반환 함수사용
SQL 2000에서는 지원하지 않았지만 2005에서는 지원한다.
SELECT *
FROM Departments AS D
WHERE (SELECT COUNT(*)
FROM fn_getsubtree(D.deptmgrid)) >= 3

deptid deptname deptmgrid
-------------------------------------------
1 HR 2
2 Marketing 7

4. DRI(선언적 참조 무결성) 강화
◆ SET DEFAULT와 SET NULL
SQL 2005에서는 외부키(Foreign key) 제약조건과 관련된 동작(트리거) 네 가지를 지원한다.
SQL 2000에서는 DELETE와 UPDATE시 NO ACTION과 CASCADE 두 가지만 지원했지만,
SQL 2005에서는 SET DEFAULT와 SET NULL을 추가로 지원한다.
SET DEFAULT는 DELETE와 UPDATE시 해당 컬럼의 Default Value로 값을 셋팅하며,
마찬가지로 SET NULL은 해당 컬럼을 NULL 값으로 셋팅한다.

예를 들어 설명해 보자.
USE tempdb
go

Customers 테이블을 생성한다.
CREATE TABLE Customers
(
customerid CHAR(5) NOT NULL,

other columns

CONSTRAINT PK_Customers PRIMARY KEY(customerid)
)

INSERT INTO Customers VALUES('DUMMY')
INSERT INTO Customers VALUES('FRIDA')
INSERT INTO Customers VALUES('GNDLF')
INSERT INTO Customers VALUES('BILLY')
Go

Orders 테이블은 Customers 테이블과 customerid(외부키)로 연결된다.
Customers 테이블의 레코드가 DELETE 될 때 SET NULL 액션 지정,
UPDATE 될 때 SET DEFAULT 액션을 지정한다.
CREATE TABLE Orders
(
orderid INT NOT NULL,
customerid CHAR(5) NULL DEFAULT('DUMMY'),
orderdate DATETIME NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers
FOREIGN KEY(customerid)
REFERENCES Customers(customerid)
ON DELETE SET NULL
ON UPDATE SET DEFAULT
)
INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101')
INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102')
INSERT INTO Orders VALUES(10003, 'BILLY', '20040101')
INSERT INTO Orders VALUES(10004, 'BILLY', '20040103')
INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104')
INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')
GO

Customers 테이블의 레코드가 DELETE, UPDATE 될 때 어떻게 작동되는지 보도록 하자.
DELETE FROM Customers
WHERE customerid = 'FRIDA'
UPDATE Customers
SET customerid = 'DOLLY'
WHERE customerid = 'BILLY'

Orders 테이블 내용.
FRIDA가 삭제되면서 Orders의 FRIDA값이 NULL값으로 변경되었더.(SET NULL)
DOLLY가 수정되면서 Orders의 DOLLY값이 DEFAULT값인 DUMMY로 변경되었다.(SET DEFAULT)

orderid customerid orderdate
----------------------------------------
10001 NULL 2004-01-01 00:00:00.000
10002 NULL 2004-01-02 00:00:00.000
10003 DUMMY 2004-01-01 00:00:00.000
10004 DUMMY 2004-01-03 00:00:00.000
10005 GNDLF 2004-01-04 00:00:00.000
10006 GNDLF 2004-01-05 00:00:00.000

5. 강화된 성능과 에러 핸들링

◆ BULK Rowset Provider
BULK는 파일에 접근하는 OPENROWSET에서 명시하여 사용되는 SQL 2005의 새로운 Provider이다.
파일에서 대량의 데이터를 읽어 내는 데 사용되는데 자세한 사용패턴은 다음과 같다.

OPENROWSET
( { 'provider_name'
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { [ catalog. ] [ schema. ] object | 'query' } 
| BULK 'data_filename',
{FORMATFILE = 'format_file_path' [, <bulk_options>] |
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB}
}
)

<bulk_options> ::=
[ , CODEPAGE = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] 
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , ROWS_PER_BATCH = 'rows_per_batch']
[ , MAXERRORS = 'max_errors']
[ , ERRORFILE ='file_name']
}
)

읽기 예문은 다음과 같다.
SELECT col1, col2, col3
FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',
FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)
읽어서 테이블에 추가하는 예문은 다음과 같다.
INSERT INTO MyTable WITH (IGNORE_CONSTRAINTS)
SELECT col1, col2, col3
FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',
FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)
그 외 상세한 내용은 원문을 참조하거나 MSDN을 참조하기 바란다.

◆ 예외 핸들링
SQL 2005에서 드뎌 TRY...CATCH 예외처리 구문을 지원하게 되었다.
SQL 2000에서는 매 구문마다 에러를 체크한 후 GOTO 구문을 사용해 분기하여 예외처리를 하였다.
게다가 데이터타입 변경(Convert)에러 같은 것은 배치작업을 종료하게 했고, T-SQL에서는 이 에러를 잡아내지도 못했다.
SQL 2005에서는 배치작업을 비정상종료케 했던 에러들을 잡아내서 처리할 수 있다.

예제를 보도록하자.
CREATE TABLE Employees
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,

other columns

CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid) REFERENCES Employees(empid)
)

예외를 잡아내려는 문장들은 BEGIN TRY/END TRY로 감싼다.
예외가 발생했을 때 처리 문장들은 BEGIN CATCH/END CATCH로 감싼다.

Employees테이블에 1번 사원을 추가해 보자.
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)

PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'

perform corrective activity

END CATCH
처음 실행하면 After INSERT가 정상출력된다.
두번째 실행하면 INSERT Failed가 출력된다.
예외가 발생해 CATCH 블럭으로 분기되면 사용자 어플리케이션에는 에러를 전달하지 않는다.
에러를 전달하기 위해서 RAISERROR구문을 사용하던지 결과셋을 반환하는 쿼리를 작성해주어야 한다.
에러정보는 ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(),ERROR_STATE()함수를 사용할 수 있다.
위 함수는 CATCH 블럭으로 올 때 발생한 에러정보를 리턴하므로, 반복사용해도 같은 정보를 출력해 준다.
반면 @@ERROR는 다르다. @@ERROR는 최종 문장이 실행되었을 때 정보가 변경되므로,
@@ERROR대신 위 함수를 사용하는 것이 바람직하다.
저장프로시저,트리거등의 루틴에서 처리되지 않은 에러가 발생할 경우 상위 수준의 CATCH 블럭으로
제어가 넘어간다. 좀 더 자세한 정보는 원문이나 MSDN을 참조 또는 직접 테스트 해보도록 합시다.

다음 예문을 실행해 보면서 확실히 개념을 익혀두자.
PRINT 'Before TRY...CATCH block.'
BEGIN TRY
PRINT ' Entering TRY block.'
INSERT INTO Employees(empid, empname, mgrid) VALUES(2, 'Emp2', 1)
PRINT ' After INSERT.'
PRINT ' Exiting TRY block.'
END TRY

BEGIN CATCH
PRINT ' Entering CATCH block.'
IF ERROR_NUMBER() = 2627
BEGIN
PRINT ' Handling PK violation...'
END
ELSE IF ERROR_NUMBER() = 547
BEGIN
PRINT ' Handling CHECK/FK constraint violation...'
END
ELSE IF ERROR_NUMBER() = 515
BEGIN
PRINT ' Handling NULL violation...'
END
ELSE IF ERROR_NUMBER() = 245
BEGIN
PRINT ' Handling conversion error...'
END
ELSE
BEGIN
PRINT ' Handling unknown error...'
END
PRINT ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
PRINT ' Error Message: ' + ERROR_MESSAGE()
PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10))
PRINT ' Exiting CATCH block.'
END CATCH
PRINT 'After TRY...CATCH block.'

TRY블럭에서 트랜잭션을 사용할 경우, CATCH블럭에서 트랜잭션의 상태를 조사할 필요가 있다.
SQL 2005에서는 XACT_STATE() 함수를 제공한다.
XACT_STATE()는 트랜잭션의 상태를 리턴한다. 리턴 값들은 0, -1, 1 값이다.
0값을 리턴할 경우 트랜잭션이 오픈되지 않았음(오픈된 트랜잭션 없음)을 의미한다.
1값을 리턴할 경우 트랜잭션이 오픈되었으면 ROLLBACK되거나 COMMIT되야함을 의미한다.
-1값을 리턴할 경우 트랜잭션이 오픈되었지만 COMMIT할 수 없음을 의미한다.(uncommitable state)
즉, ROLLBACK만 허용되며 반드시 롤백을 하지 않을 경우 열려진 LOCK들은 유지되므로 데이터를 오직
읽기만 할 수 있다. 반드시 ROLLBACK을 해서 트랜잭션을 종료해주어야 한다.
XACT_STATE() 예문은 다음과 같다.
BEGIN TRY
BEGIN TRAN
INSERT INTO Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1)

other activity

COMMIT TRAN
PRINT 'Code completed successfully.'
END TRY
BEGIN CATCH
PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.'
IF (XACT_STATE()) = -1
BEGIN
PRINT 'Transaction is open but uncommittable.'

...investigate data...

ROLLBACK TRANSACTION can only ROLLBACK

...handle the error...

END
ELSE IF (XACT_STATE()) = 1
BEGIN
PRINT 'Transaction is open and committable.'

...handle error...

COMMIT TRANSACTION or ROLLBACK
END
ELSE
BEGIN
PRINT 'No open transaction.'

...handle error...

END
END CATCH

6. 기타

◆ TOP 기능 향상
TOP 구문은 오직 SELECT 결과셋에서 상위 몇 개 또는 몇 퍼센트의 데이터만을 출력해주는 기능으로 동작하며,
SQL 2000까지는 오직 상수만을 매개변수로 지정할 수 있었다.
SQL 2005에서는 다음 두 개의 기능이 향상되었다.
(1) 매개변수로 변수나 서브쿼리를 지정할 수 있다.
(2) DELETE, UPDATE, INSERT 쿼리에서도 동작한다.
사용패턴은 다음과 같다.

SELECT [TOP (<expression>) [PERCENT] [WITH TIES]]
FROM <table_name>...[ORDER BY...]
DELETE [TOP (<expression>) [PERCENT]] FROM <table_name> ...
UPDATE [TOP (<expression>) [PERCENT]] <table_name> SET ...
INSERT [TOP (<expression>) [PERCENT]] INTO <table_name> ...

보시다시피 expression은 소괄호()로 묶여야 한다. 상수일 경우에는 관계없다.
또한 PERCENT 옵션을 붙이지 않을 경우 expression 결과값은 bigint로 변환된다.
PERCENT 옵션을 붙일 경우 float이나 0~100의 범위로 변환된다.

변수를 사용하는 예문은 다음과 같다.
USE AdventureWorks
DECLARE @n AS BIGINT
SET @n = 2
SELECT TOP(@n) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

서브쿼리를 사용하는 예문은 다음과 같다.
USE AdventureWorks
SELECT TOP(SELECT
COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate))
FROM Purchasing.PurchaseOrderHeader) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

SET ROWCOUNT 구문은 배치쿼리 전반에 걸쳐 모든 문장에 적용된다.
주로 대용량 트랜잭션을 작은 트랜잭션으로 쪼개어 실행할 때 사용된다.
SET ROWCOUNT 1000
DELETE FROM BigTable WHERE datetimecol < '20000101'
WHILE @@rowcount > 0
DELETE FROM BigTable WHERE datetimecol < '20000101'
SET ROWCOUNT 0
하지만 SQL 2005에서는 성능상의 문제로 TOP 옵션을 사용하는 것이 OPTIMIZER가
더 효율적인 실행계획을 세우도록 돕기때문에 TOP 옵션을 사용할 것을 권장한다.

◆ OUTPUT 절, 갱신 쿼리에 의한 결과셋을 테이블 변수에 적용하기
INSERT, UPDATE, DELETE 문에 의해 적용된 로우셋을 테이블변수에 추가하는 기능이다.
사용패턴은 다음과 같다.
OUTPUT <dml_select_list> INTO @table_variable

사용예제를 보자.
DECLARE @DeletedOrders TABLE
(
orderid INT,
orderdate DATETIME,
empid INT,
custid VARCHAR(5),
qty INT
)
WHILE 1=1

BEGIN
BEGIN TRAN
DELETE TOP(5000) FROM Orders
OUTPUT deleted.* INTO @DeletedOrders
WHERE orderdate < '20030101'
INSERT INTO OrdersArchive
SELECT * FROM @DeletedOrders
COMMIT TRAN
DELETE FROM @DeletedOrders
IF @@rowcount < 5000
BREAK
END

◆ 동적 컬럼을 위한 MAX 명시자
예제를 보면 이해하기 쉽다.
CREATE TABLE CustomerData
(
custid INT NOT NULL PRIMARY KEY,
txt_data VARCHAR(MAX) NULL,
ntxt_data NVARCHAR(MAX) NULL,

binary_data VARBINARY(MAX) NULL
)
MAX는 가변길이 데이터 타입 컬럼의 경우 사용가능하다.
컬럼, 변수, 매개변수, 트리거등에도 사용할 수 있다.
WRITE메소드를 추가로 지원해 해당컬럼의 특정 오프셋을 지정해 값을 변경할 수도 있다.
UPDATE table_name
SET column_name.WRITE(@chunk, @offset, @len)
WHERE ...
@offset 위치에서 @len길이까지 지우고 @chunk를 삽입한다. @offset은 0(zero)기반이다.
즉, @offset = 0 일 경우 첫 문자를 가리킨다.
@chunk = NULL 일 경우 @len은 무시된다. 그리고 @offset 위치에서 값이 잘리게 된다.
다음 예문은 28의 위치에서 끝까지 잘라버린다.
UPDATE CustomerData
SET txt_data.WRITE(NULL, 28, 0)
WHERE custid = 102
@len = NULL일 경우 @offset에서 끝까지 잘라버린다. @chunk가 적용된다.
UPDATE CustomerData
SET txt_data.WRITE('102', 9, NULL)
WHERE custid = 102
@offset = NULL일 경우 @len은 무시되고, @chunk가 새로이 (끝까지) 적용된다.
UPDATE CustomerData
SET txt_data.WRITE(' is discontinued', NULL, 0)
WHERE custid = 102

◆ XML과 XQuery
SQL 2005에서는 새로이 xml 데이터 타입이 도입되었다.
PRIMARY KEY, UNIQUE, FOREIGN KEY제약조건을 적용할 수 없다.
예제를 보자.
USE AdventureWorks
DECLARE @x AS XML
SET @x = (SELECT C.CustomerID, O.SalesOrderID
FROM Sales.Customer C
JOIN Sales.SalesOrderHeader O
ON C.CustomerID=O.CustomerID
ORDER BY C.CustomerID
FOR XML AUTO, TYPE)
SELECT @x

< C CustomerID="1"><O SalesOrderID="43860" /><O SalesOrderID="44501" /><O SalesOrderID="45283" /><O SalesOrderID="46042" /></C><C CustomerID="2"><O SalesOrderID="46976" /><O SalesOrderID="47997" /><O SalesOrderID="49054" /><O SalesOrderID="50216" /><O Sales

xml 변수 @x에 저장된 SELECT쿼리가 반환하는 로우셋을 XML형태로 반환한다.

◆ DDL Triggers와 DDL과 시스템 이벤트 통지 관련 정보는 원문이나 MSDN을 참조하기 바란다