MSSQL 2005 T-SQL 새로운 기능들

Posted at 2008.12.17 05:47 | Posted in 데이타베이스/MSSQL

출처 : http://blog.jeidee.net/194 - 필요한 내용이라 복사해 둡니다.

목  차

 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

기타



본 내용을 스크립트 파일로 작성해서 첨부해 놓았습니다.
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을 참조하기 바란다.




Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기