SQL 서버의 핵심 T-SQL의 새로운 기능

SQL 서버 2005 실전 활용 1

SQL 서버의 핵심 T-SQL의 새로운 기능

한용희 | woom33@korea.com

연+재+순+서
1회｜2005. 2｜ SQL 서버의 핵심 T-SQL의 새로운 기능
2회｜VS.NET으로 개발하는 SQL 서버 2005
3회｜SQL 서버 2005 관리자를 위한 변화
4회｜DB 보호와 복구를 위한 새로운 모델

그동안 유콘(Yucon)이라는 코드명으로 불렸던 차기 SQL 서버가 드디어 명칭을 SQL 서버 2005로 확정을 지으면서 올 하반기 출시를 앞두고 있다. 2004년 12월호를 통해 SQL 서버 2005에 대한 전체적인 소개 기사가 나간 바 있지만 이번에는 실제 예를 통해 과연 SQL 서버 2005에서는 어떤 변화가 있는지 살펴 본다.

SQL서버 2005가 서서히 모습을 드러내고 있다. 현재 베타 2까지 발표된 상태이며, 올 하반기에 정식 버전이 나올 예정이다. SQL 서버 2005 출시 소식이 전해지면서 심지어 “지금까지의 SQL 서버는 다 잊어라”, “T-SQL을 쓰지 않고 닷넷으로 전부 통합되기 때문에 처음부터 다시 배워야 한다”는 이야기까지 있었다. 하지만 SQL 서버 2005가 실체를 드러내면서 그러한 소문은 사실이 아니었음이 드러났다.
우선 닷넷에서는 SQL 서버의 저장 프로시저, 사용자 정의 데이터 타입, 그룹 함수, 사용자 정의 함수, 트리거 등을 만들 수 있다. 하지만 닷넷의 C#, VB.NET은 객체지향적 언어이지만, 집합적 언어는 아니다. 그래서 닷넷 언어를 이용하면 테이블의 행마다 어떤 일을 처리해 주어야 한다. 그러나 SQL문은 언어 자체가 집합적이므로 SQL문을 이용하면 테이블 전체를 핸들링할 수 있고, 전체를 핸들링하는 것이 속도면에서도 더 빠르다.
결국 닷넷은 T-SQL의 기능을 확장하기 위한 도구이지 T-SQL을 대체할 수는 없다. 대부분의 DML 구문(SELECT, INSERT 등)에서는 T-SQL이 닷넷보다 빠르다. 단 닷넷으로 작성하면 좋은 경우가 있는데, 그것은 CPU 작업을 많이 하는 작업(수학적 계산, 암호화 계산 등)이나, T-SQL로는 할 수 없는 시스템 외부와의 연동 작업 등이다. 이제 T-SQL의 변화된 모습을 직접 확인해 보자.

TOP 구문의 개선

SQL 서버 2000에서는 TOP 문장에 변수를 쓸 수 없었다. 굳이 쓰려고 한다면, 동적 SQL문을 이용하여 EXEC 구문으로 수행하는 방법이 있었다. 하지만 이 방법은 많이 불편했던 것이 사실이다. SQL문 자체를 매번 동적 SQL 구문으로 만드는 것은 디버깅을 어렵게 하고, 가독성을 낮추는 결과를 초래하기 때문이다. SQL 서버 2005에서는 이런 문제를 해결하여 변수 사용이 가능하다. 다음 예제를 보자.

DECLARE @n INT; SET @n = 3; SELECT TOP (@n) EmployeeID, Title FROM HumanResources.Employee; EmployeeID Title ----------- -------------------------------------------------- 1 Production Technician - WC60 2 Marketing Assistant 3 Engineering Manager (3 row(s) affected)

여기서 한 가지 주의할 것은 ()이다. 상수일 때에는 없어도 무방하지만(SQL 서버 2000과의 호환성을 위해), 상수가 아니거나 데이터를 변경하는 구문에서는 반드시 괄호를 써주어야 한다. 괄호 안에 변수 뿐만 아니라 표현식이 들어올 수 있다. SQL문도 좋고, 함수도 좋다. 다음 예제는 부서의 개수만큼 사원의 정보를 읽어오는 예제이다. 부서가 총 16개 있으므로 16명의 사원 정보를 읽어 왔다.

SELECT TOP ( SELECT COUNT(DepartmentID) FROM HumanResources.Department ) EmployeeID, Title FROM HumanResources.Employee; EmployeeID Title ----------- -------------------------------------------------- 1 Production Technician - WC60 2 Marketing Assistant 3 Engineering Manager ... (16 row(s) affected)

TOP문의 또 다른 변화는 INSERT, DELETE, UPDATE와 같은 DML 구문에서도 쓸 수 있다는 것이다. 다음 예제는 첫 번째에 있는 사원의 성별을 바꾸는 구문이다.

UPDATE TOP (1) HumanResources.Employee
SET Gender = ‘F’;

새로운 순위 함수

일반적으로 페이징 처리를 할 때에는 두 가지 방법이 있다. 먼저 클라이언트 쪽에서 모든 데이터를 읽어서 페이징 처리를 하는 방법이 있고, DB 쪽에서 페이징 처리를 해서 해당 페이지만을 읽어 오는 방법이 있다. 첫 번째 방법은 모든 데이터를 읽어야 하므로, 데이터가 많을 때에는 사용하지 못한다. 두 번째 방법 또한 DB 쪽에서 처리를 하려면 동적 SQL문을 이용하여 복잡한 쿼리문을 작성해야 하므로 쉽게 구현하기가 힘들었다. 이러한 불편을 개선하기 위해 SQL 서버 2005에서는 두 가지 해결책을 내 놓았다. 첫 번째는 ADO.NET에서 페이징 처리를 하는 것이고, 두 번째는 T-SQL문을 이용하는 것이다. ADO.NET에서 하는 방법은 다음에 다룰 것이고, 이번 연재에서는 T-SQL문에서 하는 방법에 대해 알아보자. 다음 예제는 사원을 생년월일 순으로 정렬한 예제이다.

SELECT Row_Number() OVER( ORDER BY BirthDate ) AS RowNum, EmployeeID, BirthDate FROM HumanResources.Employee; RowNum EmployeeID BirthDate -------------------- ----------- ----------------------- 1 282 1930-01-11 00:00:00.000 2 233 1932-12-30 00:00:00.000 3 253 1933-01-05 00:00:00.000 4 240 1933-01-08 00:00:00.000 5 235 1933-01-14 00:00:00.000 6 224 1933-01-17 00:00:00.000 ...

이제는 Row_Number()라는 함수를 이용하면 행 번호를 출력할 수 있다. 과거 SQL 서버 2000에서는 행 번호를 출력하는 함수가 없어 무척 불편했는데, 이제는 행 번호를 편하게 출력할 수 있다. 그런데 여기서 한 가지 주의할 것이 있다. 앞에서 OVER 다음에 오는 ORDER BY 문장은 행 번호를 어떤 순서로 매길 것인지를 정하는 구문이다. 만약 이 문장 FROM 절 다음에 또 다른 ORDER BY 절이 온다면, 이는 행 번호를 다 매긴 후에 ORDER BY 구문에 의해서 행을 정렬하라는 의미가 된다. 따라서 FROM 이후에 ORDER BY EmpolyeeID라는 문장을 넣어 준다면, RowNum는 EmployeeID에 의해 정렬이 되므로 흐트러지게 된다. 직접 해보면 이해가 빠를 것이다. 지면 관계상 예제는 ‘이달의 디스켓’으로 대신한다. 이제 행 번호를 이용하여 페이징 처리를 해보자.

SELECT * FROM( SELECT Row_Number() OVER( ORDER BY BirthDate ) AS RowNum, EmployeeID, BirthDate FROM HumanResources.Employee ) A WHERE A.RowNum BETWEEN 4 AND 8; RowNum EmployeeID BirthDate -------------------- ----------- ----------------------- 4 240 1933-01-08 00:00:00.000 5 235 1933-01-14 00:00:00.000 6 224 1933-01-17 00:00:00.000 7 281 1934-04-10 00:00:00.000 8 268 1941-11-17 00:00:00.000 (5 row(s) affected)

간단하게 페이징을 처리하는 것을 볼 수 있을 것이다. 여기서 한 가지 단점이 있는데, Row_Number()라는 함수는 WHERE절 이후에 판단한다는 것이다. 즉 WHERE에 의해 SELECT를 범위를 정한 후에 행 번호를 구할 수 있다. 따라서 WHERE절에는 Row_Number()라는 함수를 쓸 수가 없다. 그러므로 이와 같이 전체를 읽은 후에 페이징 처리를 해야 하는 단점이 있다.
그밖에 새로운 순위 함수로는 Rank(), Dense_Rank(), NTile() 등이 있다. Rank는 같은 순위가 있을 경우 다음에는 그만큼 순위를 건너뛰는 것이고, DenseRank는 순위를 건너뛰지 않는다. NTile은 전체를 NTile의 개수로 나눈 후 공평하게 순위를 배정하는 방법이다. 자세한 예제는 ‘이달의 디스켓’을 참고하기 바란다.

DML 구문의 결과를 확인할 수 있는 OUTPUT 구문

SQL로 프로그래밍을 하다보면 삽입, 삭제, 업데이트시 이들 연산에 의해 일어난 결과 값을 알고 싶을 때가 있다. 예를 들면 데이터를 삭제할 때 무슨 데이터를 삭제했는지 알고 싶을 때가 있을 것이다. 이때에는 보통 삭제하기 전에 삭제할 값을 다른 테이블에 따로 저장해 두고, 삭제한 다음에 다른 테이블에 잠시 넣어둔 데이터를 조회함으로써 그 결과 값을 알 수 있었다. 이제는 이렇게 하지 않고도 자신이 삭제한 값을 바로 알 수가 있다. 삽입의 경우도 마찬가지이다. IDENTITY 컬럼이 있는 테이블의 경우, 데이터를 삽입한 후 다시 그 테이블을 조회해야만 자신이 삽입한 행의 IDENTITY 값을 알 수가 있었다. 이제는 삽입을 할 때 바로 알 수가 있다. 다음 예제를 보자.

DECLARE @Tmp1 TABLE ( Num INT IDENTITY(1,1), Data varchar(100) ); DECLARE @Tmp2 TABLE ( Num INT , Data varchar(100) ); INSERT INTO @Tmp1 VALUES(‘1 Data’); INSERT INTO @Tmp1 VALUES(‘2 Data’); INSERT INTO @Tmp1 OUTPUT inserted.* INTO @Tmp2 VALUES(‘3 Data’); DELETE TOP(1) FROM @Tmp1 OUTPUT deleted.* INTO @Tmp2; SELECT * FROM @Tmp2; Num Data ----------- ---------- 3 3 Data 1 1 Data

첫 번째는 삽입을 할 때 OUTPUT 구문을 이용하여 그 삽입한 값을 받아왔고, 두 번째는 삭제를 할 때 삭제한 값을 OUTPUT 구문을 이용하여 받아왔다. 이를 이용하면 데이터를 조작하는 구문에서 쉽게 결과 값을 받아 올 수 있다.

재귀호출을 위한 CTE

테이블을 디자인하다 보면, 간혹 자기 자신을 참조하는 테이블을 디자인하는 경우가 있다. 예를 들면 사원 테이블의 경우 관리자 또한 사원이기 때문에 그 안에 포함하여 디자인하는 경우가 있다. 한 예로 AdventureWorks의 Employee 테이블을 보면 다음과 같이 사원과 관리자의 컬럼이 있다.

SELECT EmployeeID ,ManagerID FROM HumanResources.Employee; EmployeeID ManagerID ----------- ----------- 109 NULL 4 3 9 3 11 3 158 3 263 3 267 3 270 3 2 6 46 6 ...

109의 관리자는 없으므로 사장이 될 것이고, 4번의 관리자는 3번이다. 이런 식으로 이루어진 테이블의 값을 조회하는 데 있어 3번의 부하 직원을 모두 조회하는 경우를 보자.

SELECT E1.EmployeeID [관리자1] ,E2.EmployeeID [관리자2] ,E3.EmployeeID [관리자3] FROM HumanResources.Employee E1 LEFT OUTER JOIN HumanResources.Employee E2 on E2.ManagerID = E1.EmployeeID LEFT OUTER JOIN HumanResources.Employee E3 on E3.ManagerID = E2.EmployeeID WHERE E1.EmployeeID = 3; 관리자 1 관리자 2 관리자 3 ----------- ----------- ----------- 3 4 NULL 3 9 NULL 3 11 NULL 3 158 79 3 158 114 3 158 217 3 263 5 3 263 265 3 267 NULL 3 270 NULL (10 row(s) affected)

3번은 158번을 관리하고 있고 79번은 158의 관리를 받으므로, 결국 3번의 관리를 받는 직원으로 간주할 수 있다. 그런데 이러한 쿼리는 몇 가지 문제점을 가지고 있다. 조직의 깊이가 어느 레벨까지 내려갈지도 모르는 것이고, 조직의 변동에 따라 JOIN문을 추가해야 하므로 소스코드의 수정이 있어야 한다. 또한 불필요한 NULL 정보를 리턴하고 있어 정보의 전달 과정 또한 매끄럽지 못하다. 이를 좀 더 유연성있고 쉽게 표현해 보자. 다음은 SQL 서버 2005에서 가능한 구문이다.

WITH EmpCTE(MgrID, EmpID) AS ( SELECT E.ManagerID, E.EmployeeID FROM HumanResources.Employee E WHERE ManagerID = 3 UNION ALL SELECT E.ManagerID, E.EmployeeID FROM HumanResources.Employee E JOIN EmpCTE ON EmpCTE.EmpID = E.ManagerID ) SELECT * FROM EmpCTE; MgrID EmpID ----------- ----------- 3 4 3 9 3 11 3 158 3 263 3 267 3 270 263 5 263 265 158 79 158 114 158 217 (12 row(s) affected)

좀 더 유연성 있는 결과가 나왔다. 조직이 어떻게 변하든, 레벨이 얼마다 더 깊어지든 상관없이 소스코드를 고치지 않고서도 좋은 결과를 낼 수 있다. 이 구문은 CTE(Common Table Expression)라는 SQL 서버 2005에서 새로 소개된 기능을 사용한 것이다. 그런데 그중에서도 재귀 CTE 구문을 이용한 것이다. 사실 CTE라는 것은 Derived Table, 뷰, 임시 테이블 등 어떤 것으로도 대체할 수 있는 구문이다. 그러므로 이와 같이 재귀 구문으로 쓰지 않는 한 새로운 점이 없는 구문이다. 사실 마이크로소프트에서도 이 재귀 기능 때문에 CTE라는 구문을 도입한 것이다.
CTE는 일종의 임시적인 가상 뷰로 보면 된다. 왜 임시적이냐 하면 CTE는 DML 구문(예, SELECT)과 같은 구문에 붙여 사용하기 때문이다. 단독으로는 쓸 수 없다. CTE는 정의할 때 생기는 것이 아니라 실제로 구현할 때 실행되는 구문이다. 앞에서 보면 EmpCTE라는 것을 정의하고 그 밑에 있는 SELECT문에서 사용하고 있다.
앞의 CTE 구문을 보면 UNION ALL 구문을 기준으로 상단의 Anchor 멤버와 하단의 recursive 멤버로 나눌 수 있다. 상단 구문은 재귀 호출의 기준이 되는 구문으로 재귀 호출되는 구문이 없는 표현이 있어야 한다. 그래서 이 구문에서는 사장은 관리자가 없으므로 사장을 조회하도록 하였다. 하단의 재귀 구문에서는 자기 자신을 참조하여 재귀 구문을 수행하는 부분이므로 사원-관리자 관계를 조인하고 있다. 이제 이를 이용하면 재귀 구문도 쉽게 구현할 수 있을 것이다.

관계형 데이터를 표 형식으로 보기 위한 PIVOT과 UNPIVOT

SQL 서버 2000에서 관계형 데이터에 행별로 저장된 값을, 가로 테이블로 된 형식으로 보기 위해서는 CASE문을 써야만 그렇게 볼 수 있었다. 하지만 SQL 서버 2005에서는 PIVOT 연산자를 이용하여 간단히 구현할 수 있다. 자세한 내용은 본지 12월호에 소개되었기 때문에 예제만 살펴보자. 한 예로 연도별 판매사원의 매출을 구하는 예제를 보도록 하자. 다음은 SQL 서버 2000 방식으로 구현한 예제다.

SELECT SalesPersonID ,SUM( case Year(OrderDate) when 2002 then TotalDue else 0 end ) as [2002] ,SUM( case Year(OrderDate) when 2003 then TotalDue else 0 end ) as [2003] ,SUM( case Year(OrderDate) when 2004 then TotalDue else 0 end ) as [2004] FROM Sales.SalesOrderHeader GROUP BY SalesPersonID; SalesPersonID 2002 2003 2004 ------------- --------------------- --------------------- --------------------- 278 1604754.5514 1851628.4003 755593.2997 281 2973850.1213 3177895.6297 1429353.8926 275 4137233.9019 5244417.2148 2053782.7569 ...

이번에는 2005 방식으로 구현한 예이다.

WITH C ( SalesPersonID, TheYear, TotalDue) AS ( SELECT SalesPersonID , Year( OrderDate) AS TheYear , TotalDue FROM Sales.SalesOrderHeader ) SELECT SalesPersonID , [2002],[2003],[2004] FROM C PIVOT ( SUM(TotalDue) FOR TheYear IN ( [2002],[2003],[2004]) ) AS PVT; SalesPersonID 2002 2003 2004 ------------- --------------------- --------------------- --------------------- NULL 7216029.7246 10819121.9238 10796844.5288 268 530374.4999 610881.0169 333855.4924 275 4137233.9019 5244417.2148 2053782.7569

대용량 데이터 타입

SQL 서버 2005에서는 기존의 대용량 데이터를 다루는 데 사용했던 text, ntext, image 데이터 타입 대신에 새로운 데이터 타입을 소개하고 있다(사실 앞의 3가지 데이터 타입은 이제 사라질 예정이라고 한다). 그것은 text->varchar(max), ntext->nvarchar(max), image->varbinary(max)이다. 이들은 최대 2GB까지 데이터를 저장할 수 있다. 이들 데이터 타입은 마치 문자열 데이터를 다루듯이 다룰 수 있기 때문에 대부분의 문자열 함수를 지원한다. 또한 이전에는 이러한 대용량 데이터를 처리하기 위해서는 별도의 구문이 필요했지만 이제는 그냥 보통 데이터 타입을 쓰듯이 그대로 쓸 수 있다. 다음 예는 varchar(max) 데이터 타입을 선언한 예이다.

CREATE TABLE Test ( Num int IDENTITY(1,1), Vc varchar(max) );

XML 데이터 타입

SQL 서버 2000에서는 XML 데이터는 SQL 서버 엔진 안에 속하지 못하고 변방에서 맴돌았다. SQL 서버 2000에서는 XML 데이터를 마치 문자열 데이터처럼 다뤘기 때문에 별도의 함수나 구문을 사용해야만 했다. 하지만 SQL 서버 2005에서는 XML도 당당히 기본 데이터 타입으로 자리잡고 있다. XML 데이터 타입에는 크게 두 가지 형태가 있다. typed와 untyped이다. type는 well formed XML 형식을 지원한다. XSD로 만든 스키마를 연결해 주면 스키마에 맞는 XML 데이터만 저장할 수 있다. 반면 untyped는 이러한 스키마 없이 그냥 생성한 XML 데이터 타입을 말한다. 아무래도 typed XML 데이터 타입을 쓰는 것이 속도나 기능면에서 여러 가지로 유리한 점이 있다. 다음 구문은 스키마를 정의하는 구문이다.

CREATE XML SCHEMA COLLECTION MyXMLSchema AS N’ ’;

스키마는 비주얼 스튜디오에서 만든 후 쿼리문에 붙여 넣었다. 앞를 보면 ID, Name이라는 두 가지 요소가 있고, ID는 INT형, Name은 문자형으로 정의하고 있다. 이제 이 스키마를 이용하는 테이블을 만들어 보자.

CREATE TABLE MyXMLTest ( Num int IDENTITY(1,1), XMLData XML( MyXMLSchema) );

간단히 데이터 타입을 선언하고 스키마도 같이 기술해 주고 있다. 여기에 한번 데이터를 삽입해 보자.

INSERT INTO MyXMLTest VALUES ( ‘ 10 “Hong Gil Dong” ’ ); INSERT INTO MyXMLTest VALUES ( ‘ 20 “Kim Su Jung” ’ ); INSERT INTO MyXMLTest VALUES ( ‘ “CXX” “Choi Man Ho” ’ ); (1 row(s) affected) (1 row(s) affected) Msg 6926, Level 16, State 1, Line 1 XML Validation: Invalid simple type value: ‘“CXX”’

결과를 보면 처음 두 데이터는 잘 들어갔는데, 마지막 데이터는 오류를 내고 들어가지 못했다. 이유는 스키마 규칙을 어겼기 때문이다. ID에 문자열이 있기 때문에 에러가 나면서 데이터 삽입을 거부하고 있다. 이제 결과를 조회해 보자.

SELECT * FROM MyXMLTest; Num XMLData ------------------------------------------------------------------------------------------------------------------- 1 10 “Hong Gil Dong” 2 20 “Kim Su Jung” (2 row(s) affected)

예상대로 2행만 삽입이 되었다. 이제 이 Typed XML 데이터형을 가지고 XML 데이터 검색에 강한 X쿼리를 쓸 수 있다. 다음 예제는 ID가 10인 데이터를 검색하는 구문이다.

SELECT * FROM MyXMLTest WHERE XMLData.exist( ‘declare namespace xd=”http://tempuri.org/XMLSchema1.xsd” /xd:MyXML[xd:ID = 10]’) = 1; Num XMLData ------------------------------------------------------------------------------------------------------------------- 1 10 “Hong Gil Dong” (1 row(s) affected)

EXIST 함수의 경우 존재하면 1을 반환하고 없으면 0을 반환한다. 따라서 ID가 10인 데이터가 있으므로 하나의 행을 반환했다. 이제 이 X쿼리문을 이용하면 좀 더 쉽게 XML 데이터를 검색할 수 있을 것이다.

예외 처리

SQL 서버 2000에서의 오류 처리 기능은 @@ERROR 변수 값을 확인하면 됐다. 하지만 이 변수 값은 단 하나의 SQL문에서만 생명력이 있기 때문에 에러가 날 만한 구문이 많이 있다면 모두 그 부분에서 @@ERROR 변수를 확인해야만 했다. 하지만 이제는 간단하게 TRY-CATCH 구문으로 묶어 줌으로써 이를 간단하게 해결할 수 있다. 이 구문은 이미 다른 언어에서는 많 이 사용하는 구문이기 때문에 이해하기 어렵지는 않을 것이다.

SET XACT_ABORT ON; BEGIN TRY BEGIN TRAN DELETE FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659; COMMIT END TRY BEGIN CATCH ROLLBACK SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() as ErrorMessage; END CATCH ErrorNumber ErrorMessage ------------------------------------------------------------------------------------------------------------------- 547 DELETE statement conflicted with REFERENCE constraint ‘FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID’. The conflict occurred in database ‘AdventureWorks’, table ‘SalesOrderDetail’, column ‘SalesOrderID’. (1 row(s) affected)

이 예제는 외래키 위반 사례를 TRY-CATCH 구문으로 묶어본 것이다. 하지만 이러한 예외 처리는 심각하지 않는 구문에서만 유효하다. 다음과 같은 경우를 보자.

CREATE PROC TestTran AS SET XACT_ABORT ON; BEGIN TRY BEGIN TRAN DELETE PPP COMMIT END TRY BEGIN CATCH ROLLBACK SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() as ErrorMessage; END CATCH GO EXEC TestTran; GO SELECT ‘Orphaned transaction’ , @@TRANCOUNT; Msg 208, Level 16, State 1, Procedure TestTran, Line 10 Invalid object name ‘PPP’. -------------------- ----------- Orphaned transaction 1 (1 row(s) affected)

PPP라는 테이블은 존재하지 않는다. 이러한 심각한 오류의 경우 CATCH 문에서 걸리지 않는다. 그래서 롤백되지 않는 분리된 트랜잭션이 남아 있게 된다. SQL 서버 2000에서도 이 경우는 마찬가지이므로 주의해야 한다. DDL 트리거 SQL 서버 2000에서의 트리거는 데이터를 조작하는 구문(INSERT, UPDATE 등)에서만 사용이 가능했다. 하지만 이제는 DDL 이벤트(테이블, 뷰, 프로시저 등을 생성하거나 삭제)에서도 사용이 가능하다. 다음 예제를 보자.

CREATE TRIGGER NoTableUpdate ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT ‘DROP TABLE and ALTER TABLE statement are not allowed’; ROLLBACK; DROP TABLE dbo.MyXMLTest; DROP TABLE and ALTER TABLE statement are not allowed Msg 3609, Level 16, State 2, Line 1 Transaction ended in trigger. Batch has been aborted.

현재 테이블 삭제와 변경시 발생하는 트리거를 정의하고 테이블 삭제와 변경을 못하도록 막아 놓았다. 그래서 실제로 테이블 삭제 테스트를 해보면 에러가 발생하면서 테이블 삭제가 되지 않는다. 이를 응용하면 관리자가 테이블이나 SP를 관리하는 데 도움을 줄 수 있다. DB를 관리하다 보면 잘 되던 프로그램이 갑자기 이상한 결과 값을 반환하거나 심한 경우 전체 DB가 다운되는 경우가 있다. 여러 가지 원인이 있을 수 있지만, 그중에서도 SP를 변경해서 생기는 경우가 종종 있다. 이럴 때 SP 변경 트리거를 걸어서 로그 관리를 한다면 원인이 되는 SP를 쉽게 찾을 수 있을 것이다. 또한 테이블 변경의 경우 매우 중요한 이슈이므로 테이블 변경 트리거를 걸어서 바로 알림 메시지(이메일, SMS 등)를 받을 수도 있다.

이것이 전부는 아니다

이것으로 T-SQL의 변화에 관해 짧게 소개를 해보았다. 하지만 지면상 많은 부분을 소개하지 못해 아쉬울 따름이다. T-SQL은 현재 소개한 것 말고도 많은 변화가 있다. 대략 나열해 보면 다음과 같다.

◆ 스냅샷 격리 - 쓰기 잠금을 수행하지 않는 추가적인 격리 레벨
◆ 문장 단위 재컴파일 - SP 전체 재컴파일이 아닌 문장 단위의 재컴파일 기능 지원
◆ TABLESAMPLE 구문 - 테이블의 샘플 데이터 조회
◆ APPLY - 사용자 정의 함수를 위한 새로운 JOIN 연산자

특히 문장 단위의 재컴파일 기능은 성능 면에서 좋은 효과를 줄 것으로 기대하고 있다. 다음 호에는 닷넷과 연동하는 부분에 대해 다룰 예정이다.

제공 : DB포탈사이트 DBguide.net

출처명 : 마이크로소프트 [2005년 2월호