미리 보는 유콘 T-SQL의 새로운 기능들

미리 보는 유콘 T-SQL의 새로운 기능들

편집자 메모:
이 기사에서 설명된 기능들은 최종 확정된 사항이 아니며, 최종 제품 릴리즈에서는 이 기사에서 설명한 대로 되지 않을 수도 않으며, 경우에 따라서는 최종 제품 릴리즈에 아예 포함되지 않을 수도 있다.
……………………………………………………………………………………….
독자들은 대개 Visual Basic .NET이나 C#과 같은 CLR(Common Language Runtime) 기반의 언어들을 통하여 프로그램이 가능한 객체를 개발하는 새로운 기능이 코드명 유콘(Yukon)의 차기 SQL 서버 버전에 포함된다는 사실에 대하여 들었을 것이다. 그 결과로 마이크로소프트가 T-SQL을 개선하는 데 드는 노력을 줄일 것이라고 생각할지도 모르겠지만, 실제로는 그렇지 않다. CLR 기반 프로그래밍은 T-SQL이 취약한 영역에서 T-SQL을 보완해 준다. T-SQL은 지금까지 늘 데이터 처리와 집합 기반의 연산에 있어서 강력한 면모를 보여 왔으며, 여전히 그 영역에서는 대적할 자가 없다. 그렇지만 복잡한 알고리즘이나 반복 처리 등이 포함된 영역에서는 CLR 언어가 더 강력하다. 마이크로소프트가 여러 가지로 T-SQL 개선을 위해 노력을 기울이고 있는 사실이 입증하듯이, T-SQL은 앞으로도 오랫동안 활용되고 발전할 것이다.

마이크로소프트는 오래 전부터 프로그래머들이 필요로 하고 요구해 왔던 사항들에 대한 해답을 유콘에서 제공하고 있다. 유콘에서는 T-SQL 분야에 다양한 측면에서의 개선이 이루어지고 있다. 유콘 베타1에서의 관계형 엔진의 개선에는 재귀 쿼리 기능, PIVOT, UNPIVOT 연산자, 보다 기능적인 TOP 절, CROSS APPLY 연산자, OUTER APPLY 연산자 등이 지원되는 CTE(Common Table Expressions)가 포함되어 있다.

유콘에서는 INSERT, UPDATE, DELETE 문에서도 아웃풋을 반환할 수 있게 되며, WAITFOR 명령어를 사용하여 데이터 조작 명령어인 DML((Data Manipulation Language)가 적어도 한 행에 대하여 작업을 수행할 때까지 기다렸다가 다음 명령어로 진행하도록 할 수도 있고, 또는 통지(notification)가 큐에 도달하기를 기다렸다가 다음 명령어로 진행하도록 할 수도 있다.

트리거에서 오브젝트를 삭제하는 것과 같은 DDL(Data Definition Language) 관련 이벤트들을 잡아낼 수도 있으며, DML 이벤트가 발생할 때 통지를 받을 수도 있다. Try/Catch 블록에 기반을 둔 새로운 오류 처리 메커니즘의 지원으로 이전에 비해 훨씬 더 효율적으로 오류들을 관리할 수 있게 되었다. 데이터 타입에 있어서도 중요한 개선 사항들이 있는데, 새로운 XML 데이터 타입이 지원되고, 일자 데이터 타입과 시각 데이터 타입을 별도의 데이터 타입으로 지원하며, MAX라는 옵션이 지원됨으로써 varchar, nvarchar, varbinary 등과 같은 동적 컬럼에서 대형 오브젝트들을 훨씬 더 세련되게 처리할 수 있게 되었다. 그리고 마지막으로, BULK라는 새로운 rowset provider가 지원됨으로써 사용자들이 이전에 비해 보다 용이하게 파일들을 액세스할 수 있게 되었다(Rowset provider를 사용하면 사용자가 데이터 소스를 관계형으로 액세스할 수 있으며, 이는 provider에 대하여 쿼리의 결과로서 테이블을 얻게 된다는 것을 의미한다).

이번 기사는 사전 검토 차원의 기사이기 때문에 기술적으로 깊은 부분까지 설명할 수도 없고 상세한 구문에 대하여 설명할 수도 없지만, 새로운 기능들에 대하여 주요 내용들을 알려 줄 수는 있다. 유콘 베타 테스트 프로그램에 참여하고 있는 분들의 경우에는 유콘에 포함된 온라인 설명서와 Yukon Beta Readiness Kit에 포함되어 있는 "유콘 T-SQL Enhancements" 기술 문서에서 보다 자세한 내용들을 참고하기 바란다. 유콘의 발표에 가까워질수록 다음 기사들에서는 보다 상세한 내용들을 다루게 될 것이다.

 

CTE의 기능
뷰와 파생된 테이블(derived table)의 기능을 결합할 수 있다면 좋겠다는 생각을 해 본 적이 있는가? T-SQL에서 재귀 쿼리를 작성할 필요가 있었던 적은 없는가? CTE(Common Table Expressions)를 사용하면 이 두 가지가 모두 가능해진다. CTE는 명명된 테이블 식 (named table expression)이며 그 뒤에 쿼리가 온다. CTE에는 두 가지 유형이 있는데, 하나는 비재귀(nonrecursive) CTE이고, 다른 하나는 재귀(recursive) CTE이다. 비재귀 CTE는 파생된 테이블과 뷰의 특성을 혼합한 기능이다. 파생된 테이블과 함께 사용하면 쿼리에 대하여 별칭(alias)을 부여할 수 있고, 쿼리의 결과로 얻어지는 컬럼에 대해서도 별칭을 제공할 수 있으며, CTE는 외부 쿼리(outer query)가 종료된 이후에는 남아 있지 않는다. 뷰와 함께 사용하면 외부 쿼리에서 테이블 식을 두 번 이상 참조할 수 있다. 대개 두 번 이상 테이블 식을 참조할 필요가 있고 테이블 식이 데이터베이스에 남아 있는 것을 원하지 않을 때 비재귀 CTE를 사용하게 될 것이다.
예를 들어, 각 년도별로 Northwind 데이터베이스의 Orders 테이블로부터 해당 년도의 총 주문 수량과 그 전년도의 총 주문 수량을 알고자 한다고 가정해 보자. [리스트1]에 있는 코드는 CTE를 사용하여 이 결과를 얻어 내는 방법을 보여 준다. [리스트 1]의 callout A를 보면 WITH 키워드 다음에 나오는 것이 CTE의 별칭(YearlyOrders)과 결과 컬럼 별칭들이다. Body인 callout B에서 사용자가 쿼리에 대해 부여한 별칭이 사용되고 있으며, 이 쿼리는 년도별 주문 수량 정보를 반환한다. 외부 쿼리에서는 YearlyOrders의 두 인스턴스인 CurYear와 PrevYear간에 외부 조인(outer join)을 수행하여 금년도 주문과 전년도 주문을 매칭시켜 보여 준다. [그림1]에는 [리스트1]의 코드를 수행한 결과가 나와 있다.

유콘 이전의 SQL 서버 버전에서 이와 같은 결과를 얻으려면 두 가지 방법을 사용할 수 있다. 하나는 뷰를 만들고 쿼리에서 그 뷰를 두 번 참조하는 것인데, 이 방법은 데이터베이스에 남아 있는 오브젝트를 생성할 수 밖에 없는 방식이다. 또 다른 방법은 파생된 테이블을 두 개 사용하는 것인데, 이 경우에는 코드를 두 번 작성해야 한다.

재귀 CTE는 T-SQL 쿼리에서 재귀 호출 기능이 가능하도록 해 준다. 재귀 CTE의 body에는 적어도 두 개의 쿼리(member라고도 함)가 포함되며, 이 쿼리들은 UNION ALL 연산자로 연결된다. Anchor member는 SQL 서버가 한 번 호출하는 쿼리이다. Recursive member는 CTE의 이름을 참조하는데, 이는 이전의 작업 단계에서 반환된 결과 집합을 나타낸다. 쿼리가 빈 집합을 반환할 때까지 SQL 서버가 반복적으로 Recursive member를 호출한다. 비재귀 CTE의 경우에는 외부 쿼리(outer query)가 CTE의 이름을 참조하지만, 재귀 CTE의 경우에는 참조가 Anchor member를 호출한 결과와 모든 Recursive member의 호출 결과들에 대하여 UNION ALL을 수행한 결과가 된다.

[리스트 2]에 재귀 CTE의 예제가 나와 있다. 이 코드의 목적은 Northwind 데이터베이스에 있는 Employees 테이블을 검색하여 지정한 직원과 지정한 직원의 모든 직간접 부하직원들을 반환하는 것이다. Employees 테이블에는 아홉 명의 직원들에 대한 정보가 저장되어 있으며, 모든 직원들은 한 명의 관리자에게 보고를 하는 체계이며, 관리자의 employee ID는 ReportsTo 컬럼에 저장된다. Andrew (employee ID 2)는 최상위 관리자이기 때문에 ReportsTo 컬럼의 값이 NULL이다. 만약 Andrew와 그의 직간접 부하직원을 요청하면 결과에는 아홉 명의 직원들이 모두 포함될 것이다.

이것이 어떻게 동작하는지를 이해하기 위하여 [리스트 2]에 있는 코드를 살펴 보자. SQL 서버는 callout A에서 Anchor member를 호출하며, 그 결과 Andrew에 대한 행과 Andrew가 시작점이기 때문에 0이라는 값을 포함하는 lvl이라는 의사 컬럼(pseudo column)을 반환한다. Callout B에서 Recursive member는 Employees 테이블을 이전 단계에서 얻은 결과와 조인하게 되며, 그 결과 이전 단계에서 반환된 직원들의 부하직원들이 반환된다. 재귀 쿼리에서 lvl 컬럼은 해당 직원이 관리 체인 상에서 Andrew로부터 얼마나 떨어져 있는지를 보여 주기 위하여 이전 값에 1을 더해서 lvl 컬럼 값을 반환한다.

Recursive member를 첫 번째 호출(invocation)하면 Andrew의 부하직원들인 Nancy, Janet, Margaret, Steven, Laura가 반환되며, 이들의 lvl 컬럼 값은 1이 된다. 두 번째 호출하면 첫 번째 호출에서 반환된 직원들의 부하직원들이 반환되는데, 그들은 Michael, Robert, Anne이며 이 때 그들의 lvl 컬럼의 값은 2가 된다. 세 번째 호출하면 빈 결과 집합을 반환하게 되는데, 그 이유는 마지막에 반환된 직원들은 부하직원들이 없기 때문이며, 이 경우 재귀 호출이 종료된다.
CTE의 이름을 참조하는 callout C의 외부 쿼리는 Anchor member의 결과를 포함하여 모든 호출의 통합 결과를 반환한다. [리스트 2]의 코드를 수행하여 얻어지는 결과가 [그림 2]에 나와 있다. 유콘 이전 버전의 SQL 서버에서 동일한 결과를 얻기 위해서는 직원 계층 구조를 설명하기 위하여 데이터베이스에 중복 데이터를 저장해 두거나 아니면 커서와 임시 테이블을 사용하고 반복 수행 코드를 작성해야 하는데, 그렇게 하면 시간도 많이 걸리고 유지 보수도 더 어려워진다.

 

PIVOT과 UNPIVOT
새로운 PIVOT 연산자를 사용하면 행을 컬럼으로 회전시킬 수 있으며, 이 때 원한다면 그에 따른 집계를 수행할 수도 있다. 예를 들어 Orders 테이블로부터 직원별 년간 주문 수량을 반환하고자 한다고 가정해 보자. 단순한 GROUP BY 쿼리를 작성하여 그 정보를 얻을 수도 있기는 하지만, [그림 3]에서와 같이 직원별로 하나의 행이 반환되도록 하며 별도의 컬럼에 각 년도의 주문 수량을 표시하고자 한다고 가정해 보자.

PIVOT 연산자를 사용하면 사용자가 보고자 하는 년도를 지정하기만 하면 원하는 정보를 쉽게 얻을 수 있다.

WITH EmpOrders(orderid, empid,
orderyear)
AS (SELECT OrderID, EmployeeID,
YEAR(OrderDate)FROM Orders)
SELECT * FROM EmpOrders
PIVOT(COUNT(orderid) FOR
orderyear IN([1996], [1997],
[1998])) AS P
ORDER BY empid

이 쿼리에서는 CTE를 사용하여 PIVOT 연산자와 함께 사용하고자 하는 컬럼들을 격리시킨다. CTE 대신 파생된 테이블을 사용할 수도 있지만, 쿼리가 CTE를 오직 한 번만 참조하기 때문에 이 경우에는 두 가지 방법에 차이가 별로 없다.

PIVOT 연산자의 body에 있는 FOR 절에는 컬럼의 이름을 지정하는데, 이 쿼리에서는 orderyear이며, 이 값이 바로 결과로 얻어지는 컬럼들을 회전시키는 기준이 되는 컬럼이다. FOR 절 앞에는 결과 컬럼 값 계산에 사용하고자 하는 집계 함수를 지정하는데, 이 경우에는 각 주문 년도에 대한 주문 ID의 개수가 된다.

내부적으로는 PIVOT 연산자가 지정하지 않는 컬럼들을 기반으로 하여 GROUP BY를 수행하며, 이 경우에는 empid를 기반으로 하여 GROUP BY가 수행된다. 각각의 서로 다른 employee ID에 대하여 [1996], [1997], [1998] 세 개의 컬럼들을 결과로 얻게 되며, employee ID와 년도의 각 교차 지점에 주문 수량 정보가 표시된다.

UNPIVOT 연산자는 PIVOT 연산자의 반대이다. UNPIVOT 연산자는 컬럼들을 행들로 회전시킨다. 한 예로써 이전의 PIVOT 쿼리를 수행하고 FROM 절 바로 앞에 INTO #EmpOrders를 추가해서 #EmpOrders라는 이름의 임시 테이블을 만든다. 그리고 각 사원들에 대한 년간 주문 수량을 반환하고자 하며, 그 결과의 각 행에는 사원과 년도의 조합을 표시한다고 가정해 보자. [그림 4]에 생략된 형태가 나와 있다.

다음의 UNPIVOT 쿼리를 수행해 보자:

SELECT empid, CAST(orderyear AS
int) AS orderyear, numorders
FROM #EmpOrders
UNPIVOT(numorders FOR orderyear
IN([1996], [1997], [1998]))
AS P
ORDER BY empid, orderyear

Orderyear와 numorders 둘 다 결과 컬럼이 된다. Orderyear 컬럼에는 IN 절에 의해 각각의 컬럼 이름들로부터 파생된 1996, 1997, 1998 주문 년도들이 포함될 것이다.

Numorders 컬럼에는 [1996], [1997], [1998] 세 개 컬럼들에 현재 저장되어 있는 값들이 포함되며, 각각은 각 년도에 대한 주문 수량 정보를 나타낸다. CAST() 함수를 사용하여 주문 년도에 대한 컬럼 이름들을 가지고 있는 문자열을 정수로 변환한다.

새로운 구문에 익숙해지기만 하면 PIVOT과 UNPIVOT 쿼리를 작성하는 것은 간단하다. 유콘 이전 버전의 SQL 서버에서도 동일한 결과를 얻을 수 있기는 하지만 더 길고 복잡한 코드를 작성해야 한다.

 

TOP 기능의 개선
마이크로소프트는 유콘에서 TOP 옵션에 두 가지 중요한 측면에서 개선을 가져왔다. 첫 번째 추가된 기능은 TOP에 대한 인수로서 상수가 아닌 식(expression)을 기술할 수 있게 된 것이다. 아마도 많은 분들이 오랫동안 기다려 온 기능이 아닐까 싶다. 예를 들면 다음에서와 같이 쿼리가 반환하는 행의 개수를 변수로 지정할 수 있게 된 것이다:

DECLARE @n AS int
SET @n = 5
SELECT TOP(@n) * FROM Orders ORDER BY OrderID

그리고 TOP 다음에 나오는 괄호 안에 서브쿼리와 같은 더 복잡한 식을 지정할 수도 있게 되었다. PERCENT 옵션을 사용하지 않을 때에는 식의 결과는 행들의 개수를 지정하는 bigint 값이 되며, PERCENT 옵션을 사용하는 경우에는 결과는 전체 행에서 차지하는 백분율을 지정하는 0과 100 사이의 부동 소수점 값이 된다

또 다른 개선 사항으로는 사용자가 INSERT, UPDATE, DELETE 문에 TOP을 사용할 수 있게 되었다는 점이다. 예를 들어 다음 코드는 과거 이력 데이터가 저장되어 있는 큰 테이블에서 한 번에 만 개씩 행들을 삭제하는 쿼리이다.

WHILE 1=1
BEGIN
DELETE TOP(10000) FROM Sales
WHERE dt < '20000101'
IF @@rowcount <10000 BREAK
END

이 코드에서와 같이 하나의 대량 삭제 DELETE문을 작은 크기의 여러 개의 DELETE문으로 쪼개지 않으면 트랜잭션 로그가 상당히 많이 커질 수 있으며, 그 작업 중에 발생하는 행 잠금들이 전체 테이블 잠금으로 상향 조정될 수도 있다. DELETE 연산을 여러 개의 n 행들로 분리할 때는 n개 행씩 삭제하는 DELETE 작업은 각각 개별 트랜잭션으로 간주된다. 작업이 수행되는 동안 사용자가 트랜잭션 로그 백업을 수행하게 되면 트랜잭션이 종료된 후에는 SQL 서버가 그 트랜잭션 로그 공＠?재사용할 수 있게 된다. 그리고 한 번 수행되는 DELETE 문의 삭제 행의 수가 작으면 대개의 경우 행 수준 잠금으로 처리되는데, 그 이유는 SQL 서버가 리소스 부족으로 인하여 전체 테이블 잠금으로 잠금 수준을 조정해야 하는 상황이 발생하지 않기 때문이다. SQL 서버 2000에서도 대량 삭제 DELETE 문을 여러 개의 DELETE 문들로 쪼개어 행들을 삭제할 수 있으며 유콘 이전 버전들에서는 SET ROWCOUNT 옵션을 사용하여 그렇게 할 수 있다. 그렇지만 새로 지원되는 TOP 옵션이 SET ROWCOUNT보다 나은 기능이다.

 

CROSS APPLY와 OUTER APPLY
CROSS APPLY와 OUTER APPLY는 외부 쿼리(outer query)의 각 행에 대하여 테이블 값 함수를 호출할 수 있도록 해 주는 새로운 관계형 연산자들이다. 원하는 경우 외부 행의 컬럼을 함수의 인자(argument)로 사용할 수도 있다. 예를 들어 다음의 코드를 수행하면 fn_custorders()라는 이름의 사용자 정의 함수(UDF)가 만들어지며, 이 함수는 인수로서 customer ID와 number를 받아 들여서 입력 받은 고객에 대한 가장 최근의 주문 요청 횟수를 포함하는 테이블을 결과로 반환한다:

CREATE FUNCTION fn_custorders
(@custid AS nchar(5), @n AS
int) RETURNS TABLE
AS
RETURN
SELECT TOP (@n) * FROM Orders
WHERE CustomerID = @custid
ORDER BY OrderDate DESC,
OrderID DESC

다음 쿼리는 CROSS APPLY 연산자를 사용하여 Customers 테이블에 있는 각 고객들에 대하여 가장 최근의 세 개의 주문을 반환한다.

SELECT C.CustomerID, C.CompanyName, O.*
FROM Customers AS C
CROSS APPLY fn_custorders
(C.CustomerID, 3) AS O
ORDER BY C.CustomerID, O.OrderDate DESC, O.OrderID DESC

이 쿼리는 결과 테이블에 현재 있는 91명의 고객들에 대하여 263개의 행을 반환한다. 이 쿼리의 경우에는 주문이 없는 고객들(FISSA와 PARIS)에 대해서는 fn_custorders() 함수가 빈 결과 집합을 반환하기 때문에 주문이 없는 고객들은 결과에서 제외된다.

만약 함수가 빈 결과 집합을 반환하는 데이터에 대하여 외부 쿼리로부터 행을 포함시키기를 원하면 CROSS APPLY 연산자 대신 OUTER APPLY 연산자를 사용하면 된다. 조건에 부합하지 않는 행들의 경우에는 함수가 반환하는 결과 컬럼들의 값은 NULL이 된다.

유콘 이전의 SQL 서버 버전들에서는 외부 쿼리의 각 행에 대하여 하나의 쿼리에서 테이블 값 함수를 호출할 수 없다. @n이 인수일 때 각 고객에 대하여 @n개의 가장 최근의 주문들을 반환하기 위해서는 동적 수행과 서브 쿼리를 사용하는 훨씬 복잡한 코드를 작성해야 하며, 그 솔루션은 성능 면에서도 좋지 않고 유지 보수 측면에서도 어려운 단점이 있다.

 

결과를 반환하는 DML문과 WAITFOR
유콘에서는 데이터를 변경하는 SQL 문들이 단순히 데이터를 수정하는 데 그치지 않고 데이터를 반환할 수도 있다. INSERT, UPDATE, DELETE문에 OUTPUT 절을 추가하여 반환하고자 하는 데이터를 지정할 수 있다. 트리거를 작성할 때 inserted 테이블과 deleted 테이블을 사용하는 방식과 유사하게 inserted 테이블과 deleted 테이블을 참조하여 확인하고자 하는 데이터를 지정하면 된다.

WAITFOR 명령어도 개선된다. SQL 서버 2000과 그 이전 버전들에서는 WAITFOR 명령어에서 사용할 수 있는 옵션이 두 가지뿐이다. 하나는 다음 명령어로 진행하기 전에 기다리는 기간을 지정하는 것이고, 다른 하나는 다음 명령어로 진행하는 일시(datetime)를 지정하는 것이다. 그렇지만 이제 유콘에서는 SQL 문을 지정할 수 있으며 다음 명령어로 진행하기 전에 그 SQL 문이 적어도 한 행을 처리할 때까지 또는 지정한 timeout 값에 도달할 때까지 기다리도록 할 수 있다.

이 두 가지 기능을 결합하면 데이터 변경 작업이 행들을 처리할 때까지 기다릴 수 있으며 데이터 변경 작업으로부터 데이터를 돌려 받을 수도 있다. 예를 들어, Queue라는 이름의 테이블에 메시지 큐를 관리한다고 가정해 보자:

CREATE TABLE Queue(
keycol int NOT NULL
IDENTITY PRIMARY KEY,
dt datetime NOT NULL
DEFAULT(GETDATE()),
processed bit NOT NULL
DEFAULT(0),
msg varchar(50) NOT NULL)

여러 프로세스들이 새로운 메시지들을 Queue 테이블에 insert할 것이다. 여러 개의 서로 다른 연결에서 다음 코드를 수행하면 된다:

WHILE 1 = 1
BEGIN
INSERT INTO Queue(msg)
VALUES('msg' +
CAST(CAST(RAND()*9999999 AS
int) AS varchar (7)))
WAITFOR DELAY '00:00:01'
END

위의 쿼리에서는 기존의 WAITFOR 사용 방식으로 WAITFOR 명령어를 사용하여 데이터를 insert하기 전에 1초씩 기다리도록 했다.

그런데 여기서 사용자가 여러 개의 메시지들을 처리하고 그 메시지들을 처리한 것으로 표시할 필요가 있다면, 여러 세션에서 [리스트 3]에 있는 코드를 수행하면 된다.

[리스트 3]의 코드는 세 가지 새로운 기능들을 사용하고 있다. 다음 명령어로 진행하기 전에 WAITFOR 명령어가 WAITFOR 명령어의 인수인 UPDATE 문이 적어도 한 행을 처리할 때까지 기다렸다가 COMMIT을 수행한다. UPDATE 문은 UPDATE된 행들의 새로운 데이터를 output으로 반환해 주며 쿼리에서 READPAST 힌트를 지정했기 때문에 잠금이 걸려 있는 행들은 그냥 지나간다.

유콘 이전의 SQL 서버 버전들에서는 READPAST 힌트를 SELECT 문에만 사용할 수 있지만, 유콘에서는 READPAST 힌트를 UPDATE 문과 DELETE 문에 대해서도 사용할 수 있으며, 그로 인해 여러 개의 프로세스들이 동시에 병렬 처리될 때 잠금이 걸려 있지 않은 행들은 처리하고, 다른 세션에서 처리 중이어서 잠금이 걸려 있는 행들은 그냥 지나갈 수 있게 되었다. 이와 유사하게 동시에 여러 프로세스들이 잠금이 걸려 있지 않은 이미 처리 완료된 메시지들을 삭제할 수 있다:

WHILE 1 = 1
WAITFOR(DELETE FROM Queue WITH
(READPAST)
OUTPUT DELETED.*
WHERE processed = 1)

만약 유콘 이전의 SQL 서버 버전들에서 큐잉 기능을 제공하고자 하는 경우에는 SQL 서버 외부에서 로직을 구현해야 한다. 앞에서 보여 준 예제들은 새로운 WAITFOR 명령어와 결과를 반환하는 DML 기능을 사용한 매우 단순한 예제들이다. 유콘에서는 완전히 새로운 큐잉 구조가 추가되며 서비스 브로커(Service Broker)라는 큐잉 플랫폼도 구현되어 있다. 이 주제는 이 기사에서 다루기에 너무 큰 주제인 까닭에 이후에 서비스 브로커에 대하여 다루는 기사들을 참고하기 바란다.

DDL 트리거와 DML 이벤트 통지
유콘 이전의 SQL 서버 버전들에서는 DDL 이벤트들에 대하여 트리거를 발생시킬 수 없다. DBA들은 오랫동안 그런 기능들을 요청해 왔으며, 주로 그 목적은 감사(auditing)와 권한이 부여된 사용자들에 의한 스키마 변경 방지를 위한 것이었다.

유콘에서는 오브젝트들을 생성하거나 삭제하는 것과 같은 DDL 이벤트들에 대해서도 서버 차원 또는 데이터베이스 차원에서 트리거를 만들 수 있다. 트리거 내에서 EventData()라는 새로운 함수를 호출하면 이벤트와 관련되는 정보(예를 들어 이벤트를 발생시킨 프로세스 ID, 발생 시각, 수행된 SQL 문)를 액세스할 수 있으며, EventData() 함수는 XML 형태로 된 정보를 반환해 준다. 예를 들어 서버 차원에서 로그인과 관련되는 이벤트를 잡아 내고자 하는 경우에는 다음과 같이 트리거를 작성하면 된다:

CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN,
DROP_LOGIN
AS
PRINT 'DDL LOGIN took place.'
PRINT EventData()

로그인 계정을 만들고 변경하고 삭제하는 다음 코드를 수행하면 트리거를 테스트해 볼 수 있다:
CREATE LOGIN login1 WITH PASSWORD = '123'
ALTER LOGIN login1 WITH PASSWORD = 'xyz'

DROP LOGIN login1
트리거 내에서 이벤트 데이터를 조사할 수도 있고, 작업을 롤백시키는 것과 같은 적절한 조치를 취할 수도 있다. 트리거를 삭제하고자 하는 경우에는 다음 코드를 수행하면 된다:
DROP TRIGGER audit_ddl_logins
ON ALL SERVER
이와 유사하게 데이터베이스 차원에서 특정 DDL 이벤트들 또는 모든 DDL 이벤트들을 잡아 내는 트리거를 사용할 수도 있다. 에를 들어 다음에 나오는 트리거는 트리거가 만들어진 데이터베이스에 대하여 수행되는 모든 DDL 이벤트들을 잡아 낸다:

CREATE TRIGGER audit_ddl_events ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
PRINT 'DDL event took place in database
' + DB_NAME() + '.'
PRINT EventData()

테이블과 뷰를 만든 다음에 만든 테이블과 뷰를 삭제하는 다음 코드를 수행하면 트리거를 테스트해 볼 수 있다:

CREATE TABLE T1(col1 int)
GO
CREATE VIEW V1 AS
SELECT * FROM T1
GO
DROP TABLE T1
DROP VIEW V1

이벤트 정보를 반환하는 것뿐만 아니라, 코드가 트리거를 호출하고 DDL 이벤트가 발생했음을 알리는 메시지를 인쇄할 수도 있다.

트리거를 삭제하고자 하는 경우에는 다음 코드를 수행하면 된다:

DROP TRIGGER audit_ddl_events
ON DATABASE

트리거는 동기적으로 동작하는데, 그것은 트리거 코드의 수행이 완료되기 전까지는 트리거 수행을 유발한 애플리케이션으로 컨트롤이 돌아가지 않는다는 것을 의미한다. 또한 유콘에서는 asynchronous event consumption도 도입된다. 여러 개의 서로 다른 애플리케이션들이 어떤 프로세스가 발생시킨 이벤트(DML 이벤트도 가능함)가 발생하는 경우에 통지를 받도록 구독할 수 있다. 그리고 그 이벤트를 활성화하는 코드를 발생시킨 애플리케이션은 구독 애플리케이션들 모두가 작업을 완료할 때까지 기다리지 않고 자신의 작업을 계속할 수 있다.

 

오류 처리
오랫동안 기다려 온 또 하나의 T-SQL 개선 사항은 오류 처리 기능이다. 유콘에는 TRY/CATCH 구조가 추가됨으로써 다른 개발 환경에서 지원되는 오류 처리 기능과 유사한 기능을 구현할 수 있다. 유콘 이전의 SQL 서버 버전에서는 연결이 비정상적으로 종료되었던 오류들을 이제 유콘에서는 잡아 낼 수 있으며, 그 오류들을 세련되고 구조화된 방식으로 처리할 수 있게 되었다. 한 예로서 T1 이라는 테이블을 만들어 보자:
CREATE TABLE T1(col1 int NOT NULL PRIMARY KEY)
{리스트 4}에 있는 코드는 TRY/CATCH 기능을 사용하여 테이블에 데이터가 insert될 때 발생할 수 있는 기본 키 위반 오류(Primary key violation error) 또는 데이터 변환 오류 등과 같은 오류들을 잡아 내는 방법을 보여 준다.

TRY 구조는 트랜잭션을 갑자기 중단시키는 오류들에 대해서만 동작하기 때문에, XACT_ABORT 옵션을 ON으로 설정해야 하며 오류 처리 코드를 트랜잭션 내부에 작성해야 한다. TRY 블록에는 오류를 잡아내고자 하는 트랜잭션을 포함시킨다. 오류 발생 시에는 트랜잭션이 “failed” 상태가 된다. 사용자는 여전히 트랜잭션의 컨텍스트 내에 남아 있게 되며, SQL 서버는 잠금을 계속 유지하며 트랜잭션 작업을 되돌려 주지 않는다. 컨트롤은 가장 가까운 CATCH 블록으로 전달되며, CATCH 블록에서는 사용자가 오류를 조사하고 실패한 트랜잭션을 롤백한 다음에 적절한 조치를 취할 수 있다.
[리스트 4]의 코드를 맨 처음 수행할 때에는 오류가 발생하지 않기 때문에 CATCH 블록이 활성화되지 않는다. [리스트 4]의 코드를 두 번째 수행하면 CATCH 블록이 기본 키 위반 오류를 잡아내게 된다. 그렇지만 첫 번째 INSERT 문을 주석 처리하고 두 번째 INSERT 문을 주석 해제한 다음에 코드를 다시 수행하면 어떤 일이 일어나는지를 확인해 보기 바란다. 그 경우에는 변환 오류가 발생하게 될 것이다.

데이터 타입과 BULK Rowset Provider
유콘은 데이터 타입에 있어서도 몇 가지 흥미로운 변화를 가져 온다. 그 중 하나는 새로운 XML 데이터 타입을 도입함으로써 XML 데이터를 변수와 테이블 컬럼으로 저장하고 관리할 수 있게 되었다는 점이다. 그리고 새로운 MAX 옵션을 사용하면 명시적인 길이를 지정하지 않고 varchar, nvarchar, varbinary와 같은 동적 컬럼들을 정의할 수도 있기 때문에, LOB 데이터 타입인 text, ntext,image 데이터 타입에 대한 보다 자연스러운 대안을 가질 수 있게 되었다. MAX 옵션을 사용하면 하나의 컬럼에 최대 2GB의 데이텨를 저장할 수 있다. 여기에서 보다 자연스럽다는 것은 새로운 MAX 옵션을 사용하면 유콘 이전 버전에서와 같이 WRITETEXT, UPDATETEXT, READTEXT 등의 명령어를 사용하지 않고 일반적인 DML 문을 사용하여 크기가 큰 동적 컬럼들을 처리할 수 있다는 것을 의미한다.

또한 유콘에서는 새로운 BULK rowset provider가 지원되는데, 이 기능을 사용하면 파일을 손쉽고 세련되게 행 집합들로 처리할 수 있다.

[리스트 5]에 있는 코드를 수행해 보면 이러한 변화들을 쉽게 이해할 수 있을 것이다. [리스트 5]에 있는 코드는 TestLargeObjects라는 테이블을 만들고 그 테이블에 하나의 행을 insert한다. 다음 코드를 수행하면 XML 컬럼 x가 SELECT 쿼리의 XML 결과로 업데이트된다:

UPDATE TestLargeObjects
SET x = (SELECT * FROM Customers FOR XML AUTO)
WHERE keycol = 1

다음과 같이 BULK provider와 OPENROWSET() 함수를 사용하면 텍스트 파일을 varchar(MAX) 컬럼으로 로드할 수 있는데, 이 때 파일 경로와 SINGLE_CLOB 옵션을 다음과 같이 지정하면 된다:

UPDATE TestLargeObjects
SET vc = (SELECT vc
FROM OPENROWSET(
BULK 'c:\temp\textfile1.txt',
SINGLE_CLOB) AS CLOB(vc))
WHERE keycol = 1

SINGLE_CLOB 옵션은 하나의 텍스트 파일을 처리하여 하나의 컬럼을 가지는 단일 행으로 반환되도록 처리한다는 것을 의미한다. 이와 유사하게 SINGLE_NCLOB 옵션을 지정함으로써 유니코드 형태의 파일을 nvarchar(MAX) 컬럼으로 로드할 수도 있고, 이진 파일을 SINGLE_BLOB 옵션을 사용하여 varbinary(MAX) 컬럼으로 로드할 수도 있다.

아마도 이 중 가장 여러분을 흥분시키는 기능은 일자 데이터 타입과 시각 데이터 타입을 별도로 지원하는 것일 것이다. 다음 코드를 수행하면 일자 컬럼과 시각 컬럼을 별도로 가지는 테이블이 만들어지고 그 테이블에 하나의 행이 insert된다:

CREATE TABLE DateTimeTest(datecol date, timecol time)
INSERT INTO DateTimeTest
VALUES(CAST('2003-11-01' AS
date), CAST('10:30:59.999999'
AS time))

새로 지원되는 일자 데이터 타입과 시각 데이터 타입은 CLR 기반이며, CLR 기반이라는 것은 그 데이터 타입들이 유콘이 지원하는 .NET 구조에 기반하여 개발되었다는 것을 의미한다. 그 결과로 별도로 지원되는 일자와 시각 데이터 타입이 다양하고 유용한 메서드와 속성(property)을 제공해 준다. 예를 들어 다음에 있는 쿼리에서처럼 ConvertToString() 메서드를 date와 time 양쪽에 적용하면 지정한 포맷 문자열에 따라 date와 time 값을 표시할 수 있다:

SELECT
datecol::ConvertToString(
'MM/dd/yyyy')AS thedate,
timecol::ConvertToString(
'HH:mm:ss.fff') AS thetime
FROM DateTimeTest

[그림 5]를 보면 이 쿼리의 수행 결과가 어떻게 나오는지 확인할 수 있다. 결과 컬럼 thedate에는 MM/dd/yyyy 형태로 된 포맷화된 일자가 표시되며, thetime 컬럼에는 HH:mm:ss.fff 형태로 된 시각이 포함된다.

T-SQL의 미래
앞에서 보았듯이 유콘에는 많은 T-SQL 관련 개선 사항과 새로운 기능이 포함되어 있다. 유콘의 등장으로 독자들은 이전 버전에 비해 더 적은 양의 코드를 작성하여 이전 버전들에서와 동일한 결과를 얻을 수 있게 되며, 훨씬 더 좋은 솔루션들을 많이 확보할 수 있게 될 것이다.
집합 기반의 관계형 언어로서의 T-SQL은 지금까지도 항상 강력했었지만 앞으로는 훨씬 더 강력해질 것이다.
 
[그림 1] 해당 년도와 그 전년도의 주문 정보
 
[그림 2] Andrew와 Andrew의 직간접 부하 직원들

 
[그림 3] PIVOT 기능을 활용한 직원별 년간 주문 정보

 
[그림 4] UNPIVOT 기능을 활용한 직원별 년간 주문 정보
 
[그림 5] 일자와 시각 값 조회

리스트 1
리스트 1: CTE를 사용하여 금년도와 전년도 주문 정보를 반환하는 쿼리
USE Northwind
BEGIN CALLOUT A
WITH YearlyOrders(orderyear, numorders)
END CALLOUT A
AS
BEGIN CALLOUT B
(
SELECT YEAR(OrderDate), COUNT(*)
FROM Orders
GROUP BY YEAR(OrderDate)
)
END CALLOUT B
SELECT CurYear.orderyear, CurYear.numorders,
PrevYear.numorders AS prev
FROM YearlyOrders AS CurYear
LEFT OUTER JOIN YearlyOrders AS PrevYear
ON CurYear.orderyear = PrevYear.orderyear + 1
ORDER BY orderyear

리스트 2
리스트 2: 재귀 CTE를 사용하여 Andrew의 부하직원들을 반환하는 쿼리
WITH EmpsCTE(empid, mgrid, fname, lname, lvl)
AS
(
BEGIN CALLOUT A
SELECT EmployeeID, ReportsTo, FirstName, Lastname, 0
FROM Employees
WHERE EmployeeID = 2
END CALLOUT A
UNION ALL
BEGIN CALLOUT B
SELECT E.EmployeeID, E.ReportsTo, E.FirstName,
E.Lastname, M.lvl + 1
FROM Employees AS E
JOIN EmpsCTE AS M
ON E.ReportsTo = M.empid
END CALLOUT B
)
BEGIN CALLOUT C
SELECT * FROM EmpsCTE
END CALLOUT C

리스트 3: TRY/CATCH를 활용한 오류 처리 예제
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO T1 VALUES(1)
-- INSERT INTO T1 VALUES('two')
COMMIT
END TRY
BEGIN CATCH TRAN_ABORT
DECLARE @err AS int
SET @err = @@error
ROLLBACK
IF @err = 2627 PRINT 'Primary key violation.'
ELSE IF @err = 245 PRINT 'Conversion error.'
ELSE PRINT 'Error ' + CAST(@err AS varchar(10))
+ ' occurred.'
END CATCH

리스트 4: 메시지들에 대하여 처리 표시를 하고 UPDATE 결과를 output으로 반환하는 코드

WHILE 1 = 1
BEGIN
BEGIN TRAN
WAITFOR(UPDATE Queue WITH (READPAST)
SET processed = 1
OUTPUT INSERTED.*
WHERE processed = 0)
-- process messages' content
COMMIT
END

 

리스트 5: LOB 테스트 테이블을 만드는 코드
CREATE TABLE TestLargeObjects(keycol int NOT NULL PRIMARY KEY,
x XML, vc varchar(MAX), nvc nvarchar(MAX), vb varbinary(MAX))
INSERT INTO TestLargeObjects(keycol)
VALUES(1)

출처 : SQL 매거진 12월호