'데이터베이스/SQL Server' 카테고리의 글 목록 (43 Page)

데이터베이스/SQL Server

[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL 2008.04.29
[SQL 서버 2005 실전 활용] ② 닷넷과의 통합 2008.04.29
[SQL 서버 2005 실전 활용] ③ 관리자가 알아야 할「변화들」 2008.04.29

[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL

2008. 4. 29. 07:20

728x90

[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL
SQL 서버의 핵심 T-SQL의 새로운 기능

한용희 (롯데정보통신)2005/04/28

연재순서
1회. 더 강력해진 T-SQL의 새로운 기능
2회. 닷넷과의 통합
3회. 관리자를 위한 SQL 서버 2005
4회. DB 보호와 복구

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

연재 가이드

운영체제 : 윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구 : MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식 : MS SQL 서버 2000, C#
응용분야 : MS 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의 변화된 모습을 직접 확인해 보자.

SQL 서버 2005 따라하기

SQL 서버 2005는 툴에서도 많은 변화가 있다. 툴에 대한 자세한 설명은 세 번째 연재에서 하기로 하고 여기서는 일단 이번 연재를 따라하기 위한 간단한 사용법을 보자. 먼저 SQL 서버 2005 베타 2를 설치하고 나면, 기존 엔터프라이즈 관리자라든지, 쿼리 분석기가 보이지 않을 것이다. 2005에서는 이 두 가지 툴을 하나로 통합했는데, 그것이 SQL 서버 매니지먼트 스튜디오이다.

<화면 1> SQL 서버 매니지먼트 스튜디오 화면

SQL 서버 매니지먼트 스튜디오를 시작한 뒤 화면 왼쪽 상단에 있는 ‘New Query’ 버튼을 눌러서 새로운 창을 열면 이번 실습을 따라 할 수 있다. 기존에 SQL 서버를 설치하면 항상 샘플 데이터베이스로 Northwind와 pubs가 따라다녔다. 이제는 이 데이터베이스가 기본적으로 설치되지 않는 대신 AdventureWorks 데이터베이스가 새로 등장했다. AdventureWorks에는 SQL 서버 2005에서 새롭게 소개한 개념들이 많이 포함되어 있으므로 실습을 쉽게 할 수 있다.

SQL 서버 2005 T-SQL에서는 문장 끝에 세미콜론(;)을 허용한다. 이전 버전과의 호환성을 위해 문장 끝에 세미콜론을 쓰지 않아도 되지만 CTE(Common Table Expression)를 구현할 때에는 CTE 문장 앞에는 세미콜론이 있어야 한다. 그러나 이제는 문장 끝에 세미콜론이 있는 것이 가독성에도 좋으므로 세미콜론을 붙이는 것이 좋은 프로그래밍 습관이 될 것이다. 이번 예제는 모두 문장 끝에 세미콜론을 붙였다.

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의 개수로 나눈 후 공평하게 순위를 배정하는 방법이다. 자세한 예제는 ‘이달의 디스켓’을 참고하기 바란다.

데이터를 조작할 때 유용한 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 E mployeeID
,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 멤버로 나눌 수 있다. 상단 구문은 재귀 호출의 기준이 되는 구문으로 재귀 호출되는 구문이 없는 표현이 있어야 한다. 그래서 이 구문에서는 사장은 관리자가 없으므로 사장을 조회하도록 하였다. 하단의 재귀 구문에서는 자기 자신을 참조하여 재귀 구문을 수행하는 부분이므로 사원-관리자 관계를 조인하고 있다. 이제 이를 이용하면 재귀 구문도 쉽게 구현할 수 있을 것이다.

CASE문을 대체하는 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'<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="XMLSchema1" targetNamespace="http://tempuri.org/XMLSchema1.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd" xmlns:mstns="http://tempuri.org/XMLSchema1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="MyXML">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" />
<xs:element name="Name" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';

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

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

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

INSERT INTO MyXMLTest VALUES
( '<MyXML xmlns="http://tempuri.org/XMLSchema1.xsd">
<ID> 10 </ID>
<Name> "Hong Gil Dong" </Name>
</MyXML>'
);

INSERT INTO MyXMLTest VALUES
( '<MyXML xmlns="http://tempuri.org/XMLSchema1.xsd">
<ID> 20 </ID>
<Name> "Kim Su Jung" </Name>
</MyXML>'
);

INSERT INTO MyXMLTest VALUES
( '<MyXML xmlns="http://tempuri.org/XMLSchema1.xsd">
<ID> "CXX" </ID>
<Name> "Choi Man Ho" </Name>
</MyXML>'
);

(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 <MyXMLxmlns="http://tempuri.org/XMLSchema1.xsd"> <ID>10</ID><Name> "Hong Gil Dong" </Name></MyXML>
2 <MyXML xmlns= "http://tempuri.org/XMLSchema1.xsd"> <ID>20</ID><Name> "Kim Su Jung" </Name></MyXML>

(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 <MyXML xmlns="http://tempuri.org/XMLSchema1.xsd"> <ID>10</ID><Name> "Hong Gil Dong" </Name></MyXML>

(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 연산자

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

* 이 기사는 ZDNet Korea의 제휴매체인 마이크로소프트웨어에 게재된 내용입니다.

[출처] [SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

SQL2000 Table Hints 관련 문서 (0)	2008.04.29
mssql 함수모음 (0)	2008.04.29
[SQL 서버 2005 실전 활용] ② 닷넷과의 통합 (0)	2008.04.29
[SQL 서버 2005 실전 활용] ③ 관리자가 알아야 할「변화들」 (0)	2008.04.29
SQL Server DBA 가이드 (0)	2008.04.29

[SQL 서버 2005 실전 활용] ② 닷넷과의 통합

2008. 4. 29. 07:19

728x90

[SQL 서버 2005 실전 활용] ② 닷넷과의 통합

한용희 (롯데정보통신) 20/07/2005

SQL 서버 2005의 가장 큰 변화라고 한다면 아마도 닷넷 프레임웍과의 통합일 것이다. 이제는 쿼리문을 C#을 이용해서 개발할 수 있을 뿐만 아니라 C#을 통해서 T-SQL이 하지 못하는 기능을 마음껏 확장할 수도 있다. 이번 글에서는 CLR에 통합된 SQL 서버 2005의 새로운 모습을 살펴본다.

지난 글에서는 T-SQL의 새로운 모습에 대하여 살펴 보았다. T-SQL은 언어 자체가 집합적 언어이기 때문에 여전히 데이터를 조작하고 접근하는데 있어서는 닷넷 언어 보다 더 좋은 성능을 나타낸다. 하지만 T-SQL은 절차적 언어이기 때문에 객체지향적 프로그래밍을 할 수 없다는 단점이 있다.

그러나 닷넷을 이용하면 더 이상 이 문제로 고민하지 않아도 된다. C#, VB.NET, Managed C++를 이용해서 얼마든지 객체지향적 프로그래밍이 가능하다. 또한 복잡한 로직이나 계산, 외부 자원 연동, 코드 재사용등에 있어서는 T-SQL 보다 더 좋은 접근성과 성능을 보여준다. 한마디로 .NET 프레임웍과의 통합은 T-SQL을 교체하는 개념이 아니라, 더욱 확장하고 강화하기 위하여 도입된 것이라고 보면 된다.

SQL Server 2005가 .NET 프레임웍과 통합되면서 안정성이 대폭 향상되었다. 이전 SQL Server 2000에서 확장 저장 프로시저를 C++를 이용해서 작성을 하는 경우, 간혹 잘못된 코드로 인하여 SQL Server 전체가 다운되어 버리는 경우가 있었다. 그래서 확장 저장 프로시저를 매우 신중하게 만들어야 했으며 만드는 과정 자체도 간단하지가 않았다.

하지만 SQL Server 2005에서는 기본적으로 .NET 프레임웍의 호스팅 모델을 따라간다. SQL Server 2005와 각각의 .NET 코드로 만들어진 확장 저장프로시저는 서로의 독립성을 보장한다. 서로 메모리를 직접적으로 침범할 수 없으며, 서로의 실행 환경을 침해 할 수도 없다. 각각 별도로 운영된다는 것이다.

<그림1>을 보면 .NET 프레임웍의 호스팅 모델이 와 있다. SQL Server와 외부 어셈블리는 서로 다른 도메인을 가지고 있어 자신의 독립적인 실행 환경을 보호한다. 그래서 이제는 확장 저장 프로시저 때문에 더 이상 SQL Server가 다운되는 일은 없다.

<그림1> .NET 프레임웍 호스팅

SQL Server는 자기 자신만의 특별한 쓰레드 스케줄링, 동기화, 잠금, 메모리 할당 정책을 가지고 있다. SQL Server 자체가 워낙 메모리를 많이 사용하고 성능이 중요한 기업용 애플리케이션이기 때문에 보통의 CLR에서 제공하는 정책을 따르지 않고 자기 자신만의 특별한 방식을 적용해서 운영을 한다.

약 외부 어셈블리가 CPU나 메모리를 과도하게 많이 써서 SQL Server를 운영하는데 지장을 준다면, SQL Server는 이를 즉시 탐지해 내고 해당 사용권을 외부 어셈블리로부터 뺏어온다. 이렇게 함으로써 SQL Server는 더 이상 외부의 간섭에 영향을 받지 않고, 자기 자신을 스스로 지속적으로 안정적으로 운영할 수 있는 능력을 가지게 되었다.

간단한 사용자 정의 함수 만들기
먼저 간단한 사용자 정의 함수를 C#으로 만들어 볼 것이다. 복잡한 표현식이나 계산을 요하는 작업의 경우 C#으로 만드는 것이 더 효율적이므로 이번 예제에서는 우편번호를 체크하는 간단한 정규식 표현 함수를 만들어 보자.

먼저 VS.NET을 시작하고 새로운 프로젝트로 SQL Server Project를 선택한다. CLREx이라는 새로운 프로젝트를 만들고 AdventureWorks DB 서버에 연결한 후 새로운 아이템으로 IsValidZipCode라는 사용자 정의 함수를 추가한다.

<화면1> SQL Server용 템플릿

<화면2> IsValidZipCode 초기 생성 화면

그러면 <화면2>와 같은 템플릿 코드가 들어있다. 여기에서 주의해서 보아야 할 것은 함수 위에 있는 속성 [SqlFunction]이다. 이 속성은 아래의 함수가 SQL에서 사용하는 사용자 정의 함수임을 컴파일러에게 알려주는 지시자이다. 이제 기본 코드는 지우고 아래와 같이 코딩을 하자.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
[SqlFunction]
public static bool IsValidZipCode(SqlString ZipCode)
{
return System.Text.RegularExpressions.Regex.IsMatch(ZipCode.ToString(), @"^\d{3}-\d{3}");
}
};

[ Partial Class ]

사용자 정의 함수와 저장 프로시저에 보면 partial 못 보던 새로운 클래스 지시자가 있는 것을 볼 수 있을 것이다. 이는 Visual Studio 2005 에서 새로 나온 개념으로 클래스를 부분적으로 나누어 완성할 수 있는 기능이다. 이는 하나의 클래스를 여러 개발자가 여러 파일로 분할해서 만들 경우 나중에 합쳐줘야 하는 불편 없이, 이 지시자 하나면 컴파일러가 알아서 하나의 클래스로 인식해서 컴파일을 한다. 예를 들면 아래와 같이 하나의 클래스를 두 개의 파일로 나누어서 만들 수 있다.

// File: MyClassP1.cs
public partial class MyClass
{
public void method1()
{
}
}
// File: MyClassP2.cs
public partial class MyClass
{
public void method2()
{
}
}

MyClass라는 클래스는 두 개의 메소드를 갖는 클래스로서 이렇게 두 개의 파일로 나누어서 정의할 수 있다. 예제에 있는 사용자 정의 함수에 partial이라는 지시자가 붙음으로써 앞으로 만드는 모든 사용자 정의 함수는 하나의 클래스로 만든다는 의미가 된다.

간단하게 해당 문자열이 우편번호식인지 검사하여 결과를 리턴해 주고 있다. 이제 이 코드를 컴파일하여 배포까지 하자. 그러면 자동으로 SQL Server에 이 어셈블리가 등록이 된다. 배포를 성공적으로 끝내면 아래와 같이 테스트를 해보자.

select dbo.IsValidZipCode('333-333');
select dbo.IsValidZipCode('333-A33');

-----
1
(1 row(s) affected)
-----
0
(1 row(s) affected)

잘 작동하는 것을 볼 수 있을 것이다. 사용자 정의 함수를 만들어서 사용해 보았는데, 함수를 만들고 배포 하는 것이 간단하다는 것을 느꼈을 것이다. 그럼 SQL Server 내부에는 어떻게 등록이 되어 있는 것일까?

SELECT * FROM sys.assemblies;

sys.assemblies라는 뷰를 보면 해당 CLREx 이라는 어셈블리가 등록되어 있는 것을 확인할 수 있을 것이다.

SELECT * FROM sys.assembly_files;

sys.assembly_files에는 실제 어셈블리의 내용이 들어있다. 즉, DLL 바이너리 자체를 SQL Server안에 등록 한 것이다. 그러므로 한번 어셈블리를 SQL Server안에 배포를 하면 해당 DLL 파일은 없어도 무방하다.

위에서는 배포를 VS.NET을 이용해서 자동으로 배포를 하였지만, 수동으로 배포하는 방법도 있다.

CREATE ASSEMBLY UDF1
FROM '\\localhost\Projects\CLREx\CLREx\bin\Debug\CLREx.dll';

CREATE FUNCTION IsValidZipCode(@ZipCode nvarchar(10))
RETURNS bit
EXTERNAL NAME
CLREx.UserDefinedFunctions.IsValidZipCode;

위와 같이 먼저 어셈블리를 등록을 하고 해당 함수를 만들어 주면 수동으로도 등록을 할 수 있다.

이제는 저장 프로시저를 C#으로 만든다고?
이번에는 저장 프로시저를 만들어 보자. 저장 프로시저를 만들려면 먼저 SQL문장을 실행해서 결과를 리턴해야 한다. 그러기 위해서는 어셈블리가 DB에 접속을 해서 SQL문장을 보내주어야 한다. 일반적으로 ADO.NET을 이용해서 DB에 접속을 하지만 기존의 연결 방법을 사용할 경우에는 외부에서 접속해 들어오는 것이므로 성능상에 문제가 있다.

따라서 내부 접속을 위한 별도의 Data Provider가 필요한데 그것이 바로 SQL Server Managed Provider이다. 이 프로바이더는 SQL Server 내에서 실행되므로 별도의 접속을 맺을 필요 없이 빠르게 수행을 한다. 따라서 open, close와 같은 절차가 필요없는 Data Provider이다. 사용 방법은 아래와 같이 선언을 하면 된다.

using System.Data.SqlServer;

SQL Server Managed Provider에는 효과적인 작업을 위하여 몇가지 타입을 제공한다. SqlCommand, SqlPipe, SqlResultSet, SqlTransaction, SqlTriggerContext 와 같은 타입을 제공한다. 이중 대부분은 SqlClient에 있는 것과 동일하고 SqlPipe와 SqlTiggerContext가 이번에 새로 등장한 타입이다. SqlTiggerContext는 트리거 작성을 위한 타입이고 SqlPipe는 테이블과 같은 데이터를 호출 하는 쪽에 보내 줄때 사용하는 타입이다. 그러면 SqlResultSet과 뭐가 다르냐고 할 수도 있다.

SqlResultSet은 성능 문제로 인하여 사용을 권하지 않는 타입이고(이제는 없어질지도 모른다) SqlPipe가 성능상 더 좋은 타입이다. SqlPipe는 말 그대로 호출자에게 파이프로 물을 보내듯이 데이터를 받는 즉시 바로 보낸다. 성능면에서도 T-SQL의 저장 프로시저와 거의 비슷한 성능을 보여준다. 그러므로 앞으로 테이블 데이터를 리턴 받는 경우에는 SqlPipe를 써야 한다. 또한 .NET 저장 프로시저는 리턴값으로 int형과 void형만을 리턴 할 수 있으므로 어차피 SqlResultSet 형식으로 리턴하지도 못한다.

이번에는 직접 저장 프로시저를 만들어 보자. 이전에 만든 프로젝트에 저장프로시저를 하나 추가하고 아래와 같이 코딩을 한다.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public partial class StoredProcedures
{
[SqlProcedure]
public static void SelectEmp(SqlInt16 val)
{
SqlCommand sqlCmd = SqlContext.GetCommand();

sqlCmd.CommandText = "SELECT * FROM HumanResources.Employee "
+ "WHERE DepartmentID = @pDeptID";

// 파라메터값 대입
sqlCmd.Parameters.AddWithValue("@pDeptID", (Object)val);

// SqlPipe를 이용하여 결과 리턴
SqlContext.GetPipe().Execute(sqlCmd);
}
};

이번 예제는 사용자 테이블에서 특정 부서의 사람들을 추출하는 저장프로시저이다. 이를 컴파일 하고 배포한 다음 아래와 같은 SQL문장으로 테스트 해 보면 결과를 볼 수 있을 것이다.

EXEC dbo.SelectEmp 4;

사용자 정의 데이터 타입을 이용하여 나만의 데이터 타입을 만들자
SQL Server에는 기본적으로 CHAR, INT와 같은 기본 데이터 타입을 지원한다. 여기에 더 확장을 하여 우리가 원하는 데이터 타입을 스스로 만들어서 추가할 수도 있다. 예를 들면, 위도, 경도, 포인트를 나타내는 데이터 타입이라든지 이메일 주소를 나타내는 데이터 타입을 새로 만들어서 추가할 수 있다. 포인트를 보면 10:30 과 같은 표현식을 수용하는 하나의 칼럼을 만들 수도 있다. 그런데 사실 이러한 표현은 기존의 칼럼을 두 개로 나누어서 x, y 좌표 값을 저장해도 된다. 구지 사용자 정의 데이터 타입(User-Defined Data Types)(UDT)을 안 만들어도 할 수는 있다.

하지만, 의미상 하나로 표현하는 것이 더 타당하고, 그 자료형과 관련된 많은 메서드나 행위가 필요할 때에는 하나의 데이터형으로 만드는 것이 바람직하다. 예를 들면 날짜 같은 데이터 타입을 년,월,일로 나누어서 3개의 칼럼에 저장하는 것 보다는 년월일 하나로 만들어서 하나의 칼럼에 저장하는 것이 더 의미상 더 타당하다는 것은 누구나 알고 있다.

또한 날짜와 관련된 많은 메서드와 제약사항들이 있기 때문에 이를 3개의 칼럼으로 나누어서 처리하는 것은 많은 불필요한 코드들을 필요로 한다. 예를 들면 월에 1월을 더하거나 빼는 연산과 같은 것들을 하나의 데이터 타입에 같이 넣어 두면 어디서나 손쉽게 끌어다 쓸 수 있다. SQL Server의 UDT는 데이터 자체뿐만 아니라 메서드도 같이 포함 할 수 있으므로 (사실 UDT는 클래스나 구조체로 정의한다) 이러한 구현이 가능하다.

자 그럼 여기서 이런 생각까지 하는 사람이 있을 수도 있다. “UDT를 클래스의 개념으로 볼 수 있으니, 이제는 객체를 그대로 DB에 저장 할 수 있다는 얘기군. 그럼 아예 사원(Employee) 객체를 통째로 DB에 저장해 볼까?” 여기까지 생각을 하면 “그동안 미들티어에서 했던 OR매핑(Object Relational Mapping)이 더 이상 필요 없는 진정한 객체지향의 DB가 탄생했군!” 이라고 생각하는 사람이 있을 수도 있다.

틀린 얘기는 아니다. 하지만 성능과 용량이 문제가 된다. UDT는 8KB라는 사이즈 제한이 있고, 인덱싱 처리의 제약, 그리고 데이터 업데이트시 부하가 있다. 그러므로 UDT는 그러한 복잡한 객체를 저장하는 데에는 적절하지 않다. 처음에 예를 들었던, 위도, 경도, 포인트와 같이 가벼운 객체를 저장할 때에만 이 UDT를 사용해야 한다.

UDT는 결국 클래스를 하드디스크에 저장하는 것이기 때문에 직렬화를 해야 한다. 직렬화를 위해서는 데이터의 크기가 중요하다. 기본적으로 .NET 환경에서는 값타입(Value Type)과 참조타입(Refernce Type)이라는 두 가지 타입이 있다. 값타입은 int, char과 같이 실제 데이터가 직접 있는 타입이고, 참조 타입은 string과 같이 실제 데이터가 아닌 데이터의 주소가 들어있는 타입을 말한다. 따라서 이들 데이터 타입에 따라 저장하는 방법도 달라진다.

값타입은 대부분 고정된 길이를 가지고 있으므로 컴파일러가 알아서 그 크기를 계산할 수 있지만, 참조 타입의 경우 그 크기가 얼마나 될지 모른다. 그래서 하드디스크에 얼마 정도의 공간을 할당해야 하는지 모르는 것이다. 그래서 참조 타입을 직렬화 하는 경우에는 사용자가 직접 그 방법을 정의를 해줘야 한다. 직렬화 방법을 정리해 보면 아래와 같이 3가지 방법이 있다.

◆ SerializedDataWithMetadata
값타입이나 참조 타입에 관계없이 어떤 데이터 타입도 저장 가능. 하지만 성능 면에서는 가장 느리다. 아마 Beta3에서는 없어질 포맷이다. 한마디로 사용하면 안 되는 포맷이다.
◆ Native
크기가 고정된 값타입의 데이터 형만 저장 가능. 가장 빠르다.
◆ UserDefined
값타입, 참조 타입 모두 사용가능. 하지만, 사용자가 데이터를 읽는 방법과 쓰는 방법을 정의해 주어야 한다.

위 3가지 포맷중 사용자 정의 포맷에서 읽기와 쓰기를 직접 구현하는 것이 간단하지가 않다. 약간 복잡하다. 그래서 이번 예제는 UDT를 소개하는 것이 목적이므로 Native 포맷을 이용하는 간단한 포인트 예제를 보여주려고 한다.

기존 CLREx프로젝트에 새로운 아이템으로 Point라는 사용자 정의 데이터 타입을 추가해 보자. 그러면 기본적인 코드들이 생성되어 있을 것이다. 모두 지우자. 현재 템플릿에서 생성된 코드는 옛날 방식의 코드이다. 기본적인 구조는 아래와 같다.

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct Point : INullable
{
private Boolean is_null;
private Int32 m_x;
private Int32 m_y;

// 기본 메서드
public override string ToString() { ... }
public bool IsNull { get; }
public static Point Null { get; }
public static Point Parse(SqlString s) {...}

// 추가한 메서드
public Int32 x {...}
public Int32 x {...}
public decimal DistanceTo(Point other) {...} // 두 포인트간 거리 구하기
}

이 메서드들을 채워주면 포인트 UDT가 완성 된다. 위의 가상코드를 보면 직렬화를 지원하고 Native 포맷으로 정의되어 있는 것을 볼 수 있을 것이다. 그리고 class가 아닌 struct로 선언한 것이 보일 것이다. 구지 class가 아닌 struct를 쓴 이유는 전통적으로 사용자 정의 데이터 타입은 구조체를 썼기 때문이다. 그 이유는 클래스는 힙에 데이터가 저장이 되지만 구조체는 그렇지가 않다. 따라서 클래스는 가비지 콜렉터가 쉽게 수거해 갈 수 있지만, 구조체는 그렇지 않다.

성능면에서 구조체가 약간 더 빠르다는 것이다. 또한 NULL값을 구현하는데 있어 구조체는 별도의 초기화 없이 기본적으로 모든 값을 기본값으로 초기화를 해준다. 예를 들면 숫자형은 모두 0으로 자동 초기화를 해준다. 그래서 데이터 형을 다루는 데에는 아무래도 클래스 보다는 구조체가 약간 더 편하다고 할 수 있다. SQL Server에서는 NULL이라는 값이 존재한다. 따라서 UDT를 만들때에는 NULL이라는 의미를 부여해 주어야 한다. 그래서 INullable 인터페이스를 상속받아서 NULL을 구현하고 있다.

포인트를 저장하기 위해서 X,Y값을 위한 공간을 마련하고 널값 체크를 위한 공간도 마련하였다. 그런데 사실 널값 체크를 위해서 위와 같이 별도의 저장 공간을 사용하는 것은 하드디스크 낭비가 될 수 있다. 그래서 어떤 사람들은 위와 같은 경우 Int32.MinValue를 널값 대신으로 사용하기도 한다. 즉 Int32의 최소값을 널값으로 대신 하는 것이다. 만약 포인트의 데이터형이 string형이면 이러한 불편이 없다. string형은 참조 타입이기 때문에 null이라는 값을 수용할 수 있기 때문이다.

Int32라는 데이터형은 값타입이기 때문에 NULL을 수용할 수가 없어 위와 같은 방법을 사용하였다. 어떤 방법을 사용하던 그것은 개발자의 몫이니 상황에 따라 적절한 방법을 사용하면 된다. 이번 예제에서는 하드디스크의 공간을 걱정 안해도 되므로 그냥 따로 널값 체크를 위한 데이터형을 따로 만들었다. 기본적인 메서드의 설명은 아래와 같다.

<표1> 기본적인 메쏘드 설명

실제 완성된 코드는 지면관계상 이달의 디스켓에 있으니 참고하기 바란다.
이제 이 UDT를 컴파일 하고 배포 하면 아래와 같이 테스트 할 수 있다.

DECLARE @a Point, @b Point;

IF @a is null
PRINT 'null'
ELSE
PRINT 'not null';

SET @a.x = 10;
SET @a.y = 20;

SET @b.x = 100;
SET @b.y = 110;

SELECT CAST(@a AS CHAR);
SELECT CAST(@b AS CHAR);

SELECT @a.DistanceTo( @b ); -- 두 점사이의 거리 구하기
-----------------------------------------------------------------
null
10:20
100:110
127

SUM, MAX와 같은 집합 함수만으로는 더 이상 충분하지 않다.
이번에 SQL Server의 CLR 통합 기능 중에서 제일 반가운 기능이 바로 이 기능이다. 기존에 MIN, MAX, SUM, COUNT, AVG 같은 집합 함수를 쓰다 보면 부족함을 느끼는 경우가 많다. 이러한 집합 함수가 있으면 좋은데... 하고 많은 사람들이 원했던 것이 사실이다. 이제는 이러한 집합 함수를 직접 만들어 쓸 수 있다. 만드는 방법은 UDT와 상당히 유사하다. 이번 예제에서는 최대 변이값을 구하는 함수를 만들 것이다. 즉, 최대값-최소값을 구하는 MaxVariance라는 함수 이다. 기존 프로젝트에 새로운 아이템으로 Aggregate를 추가하고 이미 있는 템플릿 코드는 역시 옛날 방식이므로 지운다. 기본적인 구조는 아래와 같다.

[Serializable]
[StructLayout(LayoutKind.Sequential)]
[SqlUserDefinedAggregate(Format.Native)]
public struct MaxVariance
{
private Int32 m_LowValue;
private Int32 m_HighValue;

public void Init() {...}
public void Accumulate(SqlInt32 Value) {...}
public void Merge(MaxVariance Group){...}
public SqlInt32 Terminate() { ... }
}

데이터형이 값타입 밖에 없으므로 Native 포맷으로 했으며, 최대값과 최소값을 저장하는 별도의 변수를 만들었다. 각 메소드별 설명은 아래와 같다.

<표2> 각 메쏘드별 설명

자세한 코드는 이달의 디스켓에 있으니 참고하기 바란다. 위의 사용자 정의 집합(User-Defined Aggregate)(UDA)을 컴파일 하고 배포한 후 아래와 같은 코드로 테스트 해 보자. 아래 코드는 전체 사원중에서 휴가시간이 가장 많은 사람과 가장 적은 사람의 차이를 나타낸 것이다.

SELECT dbo.MaxVariance(VacationHours)
FROM HumanResources.Employee;

SELECT MAX(VacationHours) - MIN(VacationHours)
FROM HumanResources.Employee;

-----------
99
(1 row(s) affected)
99
(1 row(s) affected)

위와 아래의 쿼리문을 대조해 보면 제대로 된 결과가 나왔음을 확인해 볼 수 있다.

클라이언트 ADO.NET의 개선점
이번에 ADO.NET 2.0으로 나오면서 SQL Server와 관련해서 크게 주목할 부분은 두가지가 있다. 하나는 비동기 호출기능과 하나의 연결로 다수의 커맨드를 실행하는 기능(Multiple Active Result Sets)(MARS)이다. 지난호에서 ADO.NET에서도 페이징 처리가 가능하다고 했는데, 그 기능이 이젠 없어질 예정이라서 이번에 제외했다.

더 이상 기다릴 필요 없는 비동기 호출
비동기 호출 기능은 기존에 쿼리 문장을 수행 시키고 결과가 올 때까지 기다려야 했단 불편을 없애고, 클라이언트는 결과가 올 때까지 나름대로의 작업을 할 수 있다. 그러므로 사용자는 쿼리 문장을 날리고 모레시계의 아이콘을 기다릴 필요 없이 다른 작업을 수행 할 수도 있다. 이때 처음 DB에 연결을 맺을 때 비동기 호출을 쓴다는 표시를 “Asynchronous Processing=true” 이와 같이 해주어야 한다. 간단한 예제를 보자. 전체 예제는 이달의 디스켓에 있다.

SqlConnection cnn = new SqlConnection(
"Data Source=localhost;" +
"Initial Catalog=AdventureWorks;" +
"Integrated Security=SSPI;" +
"Asynchronous Processing=true");

cnn.Open();
// 2초간의 딜레이 후 조회
SqlCommand cmd = new SqlCommand(
"WAITFOR DELAY '00:00:02';SELECT * FROM Sales.Customer", cnn);

Console.WriteLine("작업 시작");
IAsyncResult iar = cmd.BeginExecuteReader();

while (!iar.IsCompleted) { Console.Write("*"); } //결과 올 때까지 별찍기

cmd.EndExecuteReader(iar);
Console.WriteLine("\n작업 끝");

--------------------------------------------------------------------
작업 시작
******************
작업 끝

위 예제는 고객 데이터를 조회 하는데 있어 비동기 호출을 이용하고 있다. 먼저 비동기 호출의 장점을 보려면 DB에서 시간이 오래 걸리는 작업을 돌려주어야 그 효과를 확실히 볼 수 있다. 그래서 2초간 딜레이를 주는 문장을 삽입하여 강제로 시간이 오래 걸리도록 하였다. 그리고 클라이언트는 결과가 올 때까지 계속 별을 찍다가 결과가 오면 끝내는 예제이다.

그런데 이번 예제에서는 간단히 하기 위해서 끝났는지 안 끝났는지를 알아보기 위하여 WHILE문에서 계속 체크를 하였지만, 실제 사용할 때에는 이렇게 할 필요 없이 비동기 콜백 함수를 만들어서 다 끝나면 저절로 그 함수가 호출되게 하는 것이 더 좋은 방법이 될 것이다.

하나의 연결로 다수의 쿼리 실행
기존 ADO.NET에서는 하나의 연결을 맺으면 하나의 커맨드만 실행가능 하였다. 그래서 다른 커맨드를 실행하려면 별도의 연결을 다시 맺어야만 했다. 하지만 이제는 하나의 연결로 다수의 커맨드를 실행 할 수 있다. 이렇게 함으로써 매번 새로운 연결을 안 맺어도 되므로 성능 향상이 있는 것이다. 구현하는 방법은 어렵지 않다. 그냥 쓰면 된다. 아래 예제를 보자.

// 하나의 연결
SqlConnection cnn = new SqlConnection(
"Data Source=localhost;" +
"Initial Catalog=AdventureWorks;" +
"Integrated Security=SSPI;");
cnn.Open();

// 첫번째 실행
SqlCommand cmd1 = new SqlCommand(
"SELECT * FROM Production.Location", cnn);
SqlDataReader dr1 = cmd1.ExecuteReader();

// 두번째 실행
SqlCommand cmd2 = new SqlCommand(
"SELECT * FROM HumanResources.Department", cnn);
SqlDataReader dr2 = cmd2.ExecuteReader();

// 결과 출력
while (dr1.Read() == true && dr2.Read() == true)
{
Console.WriteLine(dr1[0] + " | " + dr2[0] );
}
-------------------------------------------------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
.......

cmd1과 cmd2가 하나의 cnn이라는 연결을 공유해서 쓰고 있다. 전체 예제는 이달의 디스켓에 있다.

SQL Server의 변신은 무죄?
처음에 SQL Server가 닷넷 프레임웍(CLR)에 통합된다고 하였을 때 많은 사람들이 궁금증을 가지고 지켜보았다. 이제는 C#을 공부해야 하는가 하고 걱정하는 사람들도 있었다. 하지만 막상 뚜껑을 열어보니 CLR통합 이라는 기능은 T-SQL을 대체하는 기능이 아닌 좀더 확장하고 보강하기 위한 기능으로 보는 것이 좋다는 결과가 나왔다. SQL Server를 개발하는데 있어 기본은 T-SQL이다. 하지만 거기서 멈추지 않고 더욱 새로운 기능을 추가하고 확장하고 싶다면 .NET을 이용하면 된다. 다음 글에서는 DB 관리툴과 보안에 대해 소개할 것이다.@

* 이 기사는 ZDNet Korea의 제휴매체인 마이크로소프트웨어에 게재된 내용입니다.

[출처] [SQL 서버 2005 실전 활용] ② 닷넷과의 통합|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

mssql 함수모음 (0)	2008.04.29
[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL (0)	2008.04.29
[SQL 서버 2005 실전 활용] ③ 관리자가 알아야 할「변화들」 (0)	2008.04.29
SQL Server DBA 가이드 (0)	2008.04.29
T-SQL로 테이블, 컬럼명 조회하기 (0)	2008.04.29

[SQL 서버 2005 실전 활용] ③ 관리자가 알아야 할「변화들」

2008. 4. 29. 07:04

728x90

[SQL 서버 2005 실전 활용] ③ 관리자가 알아야 할「변화들」

한용희 (롯데정보통신) 01/08/2005

SQL 서버 2005는 5년 만에 나온 제품인 만큼 엔진, 관리 툴, 보안에 많은 변화가 있다. 이번 글에서는 SQL 서버 2005 엔진의 새로운 변화, 그리고 대폭 바뀌고 개선된 관리 툴에 대한 소개와 향상된 보안 기능에 대해 알아볼 것이다.

앞서 두 번의 연재 동안 개발자 관점에서의 SQL 서버 2005의 모습을 살펴보았다. 이번 글부터는 관리자의 관점에서 바뀐 SQL 서버 2005의 새로운 모습을 소개할 것이다.

원래는 이 내용을 첫 회에 연재하려고 하였으나 지루할 것 같아 일단 당장 눈에 보이는 변화인 개발자 부문을 먼저 다뤘다. 여기에서는 SQL 서버 2005의 시스템에 대한 전반적인 부분부터 관리 툴에 대한 소개하고 보안 관련된 변화까지 알아볼 것이다.

4GB 메모리의 한계를 넘는 64비트 컴퓨팅 지원
현재 대부분 쓰이고 있는 32비트 프로세서는 기본적으로 메모리를 최대 4G(232)까지 지원한다. 그런데 DB 서버에서는 프로세서의 속도보다도 더 중요한 것이 바로 메모리 용량이다. 그래서 SQL 서버에서는 AWE(Address Windowing Extensions)를 이용하여 최대 32G까지 지원하고 있다.

AWE는 가상의 메모리 공간을 마련하여 실제 물리적 메모리와 맵핑하는 방식으로 4G 이상의 메모리에 접근한다. 하지만 이는 가상 메모리와 물리적 메모리 사이에 변환이 필요하므로 오버헤드를 유발하기 때문에 직접 접근하는 것보다는 느리다.

64비트 프로세서를 사용하게 되면 이런 제약은 없어진다. 현재 SQL 서버 2005는 인텔 아이태니엄/제온(EMT64), AMD 옵테론/애슬론64와 같은 64비트 프로세서를 지원하기 때문에 이들을 이용하면 현재 상태에서는 512GB까지 메모리 확장이 가능하다. 따라서 CPU를 64비트 프로세서로 바꾸기만 해도 성능 개선 효과를 볼 수 있을 것이다.

<그림 1> 32비트와 64비트 메모리 어드레싱의 차이

최근에 2001OUTLET에서 SQL 서버 2000을 32비트에서 64비트로 마이그레이션한 뒤 성능 향상에 대한 사례 발표를 한 적이 있다. 관심 있는 독자는 참고 사이트(참고자료)에서 확인해 볼 수 있다. 이 발표 내용 중 성능 향상에 대해 한 가지만 소개하면, 110GB의 테이블의 인덱스를 재생성하는 데 있어 기존에는 10시간 이상 걸리던 작업이 64비트 환경에서는 1시간 45분 만에 끝났다고 한다. 이러한 성능 향상에는 CPU를 교체한 것 이외에도 메모리, 스토리지를 업그레이드한 효과도 포함된 것이므로 단순 비교에는 무리가 있다.

효율적인 멀티프로세서 활용을 위한 NUMA 지원
일반적인 멀티프로세서 환경인 SMP(Symmetric MultiProcessing) 환경에서는 CPU와 메모리가 버스라는 통로를 통해 접근하므로 프로세서를 많이 달수록 버스 통로는 바빠지게 된다. 그러므로 프로세스를 많이 장착한다고 해서 반드시 프로세스를 정착한 개수만큼의 성능 개선 효과를 볼 수 없다.

그러나 NUMA(Non-Uniform Memory Access) 방식을 사용하면 이런 문제를 해결할 수 있다. NUMA는 윈도우 서버 2003에서 지원하는데, 이는 메모리와 CPU를 하나의 노드로 묶어서 전용의 로컬 메모리 공간을 확보하는 방식을 말한다. 따라서 각각의 노드들은 각각의 로컬 메모리를 가지고 있어서 로컬 메모리 내에서는 빠른 속도로 메모리 접근을 할 수 있다.
하지만 이 방식의 단점이라면 서로 다른 노드 사이에 메모리 접근을 하는 것은 외부 버스를 통해 접근해야 하므로 느릴 수밖에 없다. 그러므로 성능을 향상시키기 위한 핵심은 바로 이 노드들 사이의 메모리 접근을 줄이는 것이다. 그러기 위해서는 운영체제와 응용 프로그램간의 긴밀한 협조가 있어야만 한다. SQL 서버 2005는 이러한 NUMA를 적극 지원하여 크로스 노드 문제를 완화하고 있다.

<그림 2> SMP(Symmetric MultiProcessing)

<그림 3> NUMA(Non-Uniform Memory Access)

하나로 두 개의 CPU 성능을 구현하는 하이퍼쓰레딩 지원
하이퍼쓰레딩(hyper-threading)을 지원하는 인텔 CPU의 경우 하나의 CPU로 마치 두 개의 CPU가 동작하는 것처럼 흉내 낼 수 있다. 이를 이용하면 멀티쓰레드 애플리케이션이나 멀티 애플리케이션을 수행할 때 성능이 개선된다고 알려져 있다. 이를 이용하면 금전적인 면에서 절약을 할 수 있다. SQL 서버 라이선스 1-CPU를 구매하고 하이퍼쓰레딩을 이용하여 마치 두 개의 CPU를 돌리는 것과 같은 흉내를 낼 수 있다. 하지만 리얼 2-CPU보다는 성능이 떨어지므로 그리 권장할 만한 방법은 아니다.

향상된 멀티플 인스턴스 지원
기존에는 최대 16개까지 인스턴스를 지원했지만, SQL 서버 2005에서는 최대 50개까지 인스턴스를 지원한다.

멈추지 않는 운영을 위한 데이터베이스 미러링
데이터베이스의 안정적인 운영을 위해 기존에는 대부분 클러스터링을 구현해 사용했다. 하지만 클러스터링은 데이터베이스 자체 내에서 지원되는 기능이 아닌 외부에서 지원되는 기능이다. 그래서 SQL 서버 2005에서는 자체 내에서 이러한 기능을 지원하기 위해 미러링이라는 기능을 추가했다. 미러링은 클러스터링과 다르게 별도의 하드웨어가 필요 없으며, 별도의 공유 스토리지도 필요 없다. 또한 길이 제한도 없어서 멀리 떨어진 곳에서도 설치가 가능하다.

이는 primary 서버와 mirroring 서버 두 대를 구축하여 서로 트랜잭션 로그 정보를 주고받기 때문에 가능한 것이다. 이 가운데 watch 서버가 추가되어 primary 서버의 동작을 감시하다가 primary 서버가 다운되면 즉시 mirroring 서버로 교체시켜주는 방식으로 동작한다. 자세한 내용은 다음 호에서 다룰 예정이다.

간편한 이력 관리를 위한 데이터베이스 스냅샷
데이터베이스를 운영하다 보면 특정 시점의 데이터를 저장하고 싶을 때가 있다. 백업을 이용하면 되지만 시간이 오래 걸리고 대용량의 저장 공간이 필요하다는 단점이 있다. SQL 서버 2005에서는 이런 불편을 해소하기 위하여 데이터베이스 스냅샷 기능을 지원한다. 이는 특정 시점의 데이터를 쉽게 보관하고 복구하는 기능을 제공한다. 이 때 실제 전체 데이터를 모두 보관하는 것이 아니라 메타 데이터만 보관하기 때문에 부담이 없다. 이 역시 자세한 내용은 다음 호에서 다룰 예정이다.

IIS 없이 HTTP 지원
SQL 서버 2005에서는 웹 서비스와 같은 HTTP 요청을 IIS 없이도 스스로 할 수 있는 기능을 제공한다. 따라서 웹과 연동된 프로그래밍을 할 때 더욱 쉽게 개발할 수 있게 되었다. 이 점은 비주얼 스튜디오 2005에서도 지원하는 기능이기도 하다. 비주얼 스튜디오 2005에서도 ASP.NET 프로그램을 개발하는 데 있어 더 이상 IIS가 없어도 가능하기 때문이다.

근무시간에도 가능한 인덱스 재생성
기존 SQL 서버 2000의 경우 인덱스를 재생성하게 되면 재생성하는 동안에는 데이터를 갱신하지 못했다. 그래서 인덱스를 다시 만드는 경우 대부분 야근을 하는 것이 보통이었다. 하지만 이제는 그러지 않아도 된다. 실시간으로 인덱스를 재생성하면서도 데이터 갱신 작업이 가능하다. 어떻게 이 기능이 가능할까? 그것은 바로 두 개의 인덱스를 SQL 서버가 유지하기 때문이다.

즉, 하나는 기존의 인덱스를 그대로 유지하면서 온라인 작업이 가능하게 하고, 다른 하나의 인덱스는 재생성 작업을 하는 데 이용한다. 그러다가 인덱스 재생성 작업이 끝나면 기존 인덱스는 삭제하고 재생성된 인덱스를 붙이는 방식이다. 그런데 이 방법에는 두 개의 인덱스를 유지하는 데 따른 오버헤드가 있다. 그러므로 사용자는 온라인/오프라인을 선택해서 인덱싱 작업을 할 수 있다.

또한 기존에 클러스터드 인덱스를 재생성하는 경우, 넌클러스터드 인덱스까지 같이 재생성되는 문제점이 있었다. 이는 넌클러스터드 인덱스가 클러스터드 인덱스를 참조하기 때문에 어쩔 수 없는 현상이었다. 그래서 클러스터드 인덱스 한 번 바꾸려면 시간이 많이 걸려서 대용량 테이블의 경우 만만한 작업이 아니었다. 하지만 이제는 클러스터드 인덱스를 재생성한다고 해서 넌클러스터드 인덱스까지 영향을 주지 않는다.

그럼 온라인 인덱싱 기능을 직접 시험해 보자. 다음은 adventureworks 데이터베이스의 SalesOrderDetail 테이블의 인덱스를 재생성하는 구문이다. 이 테이블이 12만행이나 되기 때문에 이러한 작업을 테스트하기에 안성맞춤이다.

SELECT GETDATE();
ALTER INDEX ALL ON Sales.SalesOrderDetail REBUILD
WITH (ONLINE = ON);
SELECT GETDATE();

-----------------------
2005-03-12 16:06:35.110
(1 row(s) affected)

-----------------------
2005-03-12 16:06:43.913
(1 row(s) affected)

이 결과를 보면 SalesOrderDetail 테이블의 인덱스를 재생성하는 데 있어 WITH 옵션에 ON을 주어서 온라인으로 하고 시간은 35초에서 43초까지 약 8초가 걸렸다. 이 작업을 돌리는 것과 동시에 다음 데이터 갱신 작업을 하자.

UPDATE Sales.SalesOrderDetail
SET OrderQty = 10000
WHERE SalesOrderID = 43659;
SELECT GETDATE();

(12 row(s) affected)
-----------------------
2005-03-12 16:06:39.677
(1 row(s) affected)

결과를 보면 39초에 갱신 작업이 끝났음을 알 수 있을 것이다. 인덱스를 재생성하는 동안에도 데이터 갱신 작업을 성공한 것이다. 그런데 만약 여기에서 ONLINE을 OFF로 했을 때의 시간은 얼마나 걸릴까? 실제 2～3초 밖에 걸리지 않는다. 즉, 인덱스를 두 개 만들지 않아도 되므로 그만큼 빠른 것이다.

온라인 복구 기능 지원
SQL 서버 2000에서는 데이터베이스가 복구되는 동안 사용자는 데이터베이스를 사용하지 못했다. 하지만 SQL 서버 2005에서는 부분 복구 기능을 지원한다. 한 예로 데이터베이스의 primary 파일 그룹이 복구되면 primary를 사용하는 데이터베이스는 사용이 가능하다. 나머지는 사용하면서 복구를 한다.

백업 미러링 지원
데이터를 백업할 때 하나의 테이프에만 백업을 했는데 만약 그 테이프에 오류가 생긴다면 난감할 수밖에 없다. 그럴 때에는 두 개의 테이프에 동시에 백업받는 것이 안전하다. SQL 서버 2005에서는 이러한 경우를 위해 백업 미러링을 지원한다. 즉, 테이프 1에 데이터를 백업하면서 동시에 테이프 2에도 백업을 하는 것이다. 그렇다고 시간이 두 배가 걸리는 것은 아니다. 미러링을 하기 때문에 더 추가하더라도 성능에 영향을 미치지 않는다. 단, 이 때 백업 장치는 동일한 장치이어야만 미러링이 가능하다. 다음은 미러링 백업 예제이다.

BACKUP DATABASE AdventureWorks
TO TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2'
WITH
FORMAT,
MEDIANAME = 'AdventureWorksSet1'

동시에 하는 데이터베이스 백업과 로그 백업
SQL 서버 2000에서의 로그 백업은 데이터베이스 백업이 끝난 후에나 가능했다. 하지만 SQL 서버 2005에서는 데이터베이스와 로그를 동시에 백업할 수 있다.

다운돼도 접속할 수 있는 관리자 전용 연결 기능
SQL 서버를 운영하다가 가끔 잘못되면 CPU 사용률이 거의 100%가 되는 경우가 발생할 수 있다. 이럴 경우에는 마우스도 움직이기 어렵다. 어떤 조치를 취하고 싶어도 마우스가 움직이지 않으니 어떻게 해 보지도 못하고 발만 동동 구르는 경우가 있다. SQL 서버 2005에서는 이런 경우, 관리자 전용 연결 기능(dedicated administrator connection) 기능을 이용하여 SQL 서버에 접속해 들어가서 문제를 해결할 수 있다. 이는 커맨드라인 유틸리티를 이용하는 것인데, 과거 OSQL을 대체하는 SQLCMD를 이용하면 된다. SQLCMD를 사용할 때 ‘-A’ 옵션을 주면 관리자 전용 연결로 들어 갈 수 있다. 명령 프롬프트에서 다음과 같이 실행해 보자.

C:\Documents and Settings\Administrator>sqlcmd -S localhost -E -A
1> USE adventureworks
2> go
Changed database context to 'AdventureWorks'.
1> select Name from Person.AddressType
2> go
Name
--------------------------------------------------
Archive
Billing
Home
Main Office
Primary
Shipping

(6 rows affected)
1>

이 예제는 로컬 SQL 서버(-S localhost)에 관리자 전용 연결(-A)을 신뢰된 연결(-E)로 접근하여 쿼리를 수행하는 모습이다.

익스체인지나 아웃룩이 필요 없는 메일링 기능
기존 SQLMail의 경우, 사용하려면 익스체인지와 아웃룩이 필요했다. 설치 또한 계정 문제가 얽혀 있어서 간단하지 않았다. 그래서 이번 SQL 서버 2005에서는 좀 더 편리한 SQLiMail을 지원한다. 이는 익스체인지나 아웃룩 없이도 SMTP 서버만 있으면 사용 가능한 메일링 기능이다. 이 기능은 현재는 기본적으로 설치되지 않고 관리자가 추가로 설치해야 한다. 방법은 두 가지가 있는데, 마법사를 이용하는 방법과 쿼리를 직접 이용해서 설치하는 방법이 있다. 쿼리를 이용하려면 다음과 같은 폴더에 스크립트가 있으니 이를 실행해서 설치하고 프로파일과 계정을 만들어서 연결시켜 주면 된다.

C:\Program Files\Microsoft SQL Server\MSSQL. 1\MSSQL\Install\Install_SQLiMail.sql

마법사를 이용하는 방법은 매니지먼트 스튜디오에서 매니지먼트에 부분에 보면 SQLiMail이라는 아이콘이 있다. 그 아이콘을 더블클릭하면 마법사가 실행된다.

<화면 1> SQLiMail 마법사

사용 방법은 기존과 비슷하다.

EXEC dbo.sendimail_sp
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = '잘 도착했나요?',
@subject = '테스트 메일입니다.' ;

이와 같이 받을 사람을 지정하고 메일을 보내면 된다.

익스체인지

아웃룩 없이 SQL 서버 2000에서도 메일 보내기

사실 기존 SQL 서버 2000에서도 SQLMail을 사용하지 않고도 단순히 SMTP 서버와 CDOSYS 오브젝트만으로 메일을 보낼 수 있다. 다음 링크를 보면 자세한 내용이 나와 있다.

http://support.microsoft.com/default.aspx?scid=kb;ko;312839

시스템 트레이에서 사라진 SQL 서버 서비스 관리자
SQL 서버 2000에서는 서비스 관리자가 시스템 트레이 아이콘으로 있어서 거기에서 관리했다. 하지만 이는 다른 MS 제품 대부분이 MMC(Microsoft Management Console)를 이용하여 관리하는 것과는 차이점이 있었다. 그래서 MS는 그런 트레이 아이콘을 없애고 MMC에 포함시켰다. 이제는 MMC 내에서 서비스를 시작하고 중지할 수가 있다. [제어판]-[관리도구]-[컴퓨터관리]에 가보면 SQL 컴퓨터 매니저가 있다.

<화면 2> SQL 서버 2000의 서비스 관리자

<화면 3> SQL 서버 2005의 SQL 컴퓨터 매니저

SQL 컴퓨터 매니저에서는 다음과 같은 서비스를 관리한다.

◆ SQL 서버
◆ SQL 서버 Agent
◆ SQL 서버 Analysis Services
◆ Report Server
◆ Microsoft Search
◆ Distributed Transaction Coordinator(DTC)
◆ Full Text Search

엔터프라이즈 관리자+쿼리 분석기 = SQL 서버 매니지먼트 스튜디오
맨 처음 SQL 서버 2005를 설치하면 쿼리 분석기를 찾지 못해 약간 당황할 수도 있다. SQL 서버 2005에서는 기존 DB 관리를 위한 엔터프라이즈 관리자와 스크립트 수행을 위한 쿼리 분석기가 SQL 서버 매니지먼트 스튜디오라는 이름으로 하나의 도구로 합쳐졌다.

<화면 4> SQL 서버 매니지먼트 스튜디오

<화면 4>를 보면 다양한 구성이 추가된 것을 볼 수 있다. 마치 비주얼 스튜디오를 연상하게 하는 구조처럼 변했다. 이 매니지먼트 스튜디오는 SQL 서버 2005 뿐만 아니라 SQL 서버 2000, SQL 서버 7까지 붙여서 관리할 수 있다. 이 매니지먼트 스튜디오의 가장 큰 변화라면 아마도 non-modal 기능일 것이다.

기존에는 EM(Enterprise Manager)에서 어떤 작업을 하기 위해서 창을 띄우면 그 창은 modal 창으로 떠서 그 작업이 다 끝날 때까지 기다려야만 했다. 하지만 매니지먼트 스튜디오에서는 non-modal 형식으로 창이 뜨기 때문에 동시에 다른 작업을 수행하는 것이 가능하다.

또 다른 변화로는 매니지먼트 스튜디오에서는 많은 수의 오브젝트를 다를 수 있다는 것이다. 기존 EM에서는 DB에 접속할 때 항상 모든 오브젝트를 한꺼번에 열거하기 때문에 오브젝트가 많을 경우에는 시간이 오래 걸렸다. 하지만 매니지먼트 스튜디오에서는 그 오브젝트를 브라우저에서 열기 전까지는 나열하지 않는다. 즉, 현재 필요한 정보만 읽어보고 필요에 따라 그때그때 정보를 읽어 오기 때문에 DB에 많은 오브젝트가 있더라도 접속하는 데 시간이 오래 걸리지 않는다.

<화면 4>를 보면 가운데 있는 것이 쿼리 편집기(query editor)이다. 쿼리 편집기가 기존 쿼리 분석기와는 달리 다수의 창을 열 경우 상단에 탭으로 표시된다. 기본에 별도의 창이 열려서 관리하기 불편했는데, 상단에 탭으로 표시되니 창을 관리하기가 쉬워졌다. 약간 불편한 점이라면 상단 탭의 제목이 너무 길어서 잘 보이지 않는다는 것이다. 이 쿼리 에디터에서는 T-SQL 뿐만 아니라 MDX, DMX, XMLA 등도 같이 실행이 가능하다.

<화면 4>의 우측에 보면 솔루션 탐색기(solution explorer)가 있는데, 이는 비주얼 스튜디오처럼 프로젝트를 관리할 수 있는 기능을 말한다. 다수의 SQL문을 하나의 프로젝트로 묶어서 관리가 가능하다. 또한 소스세이프도 지원하기 때문에 다수의 개발자가 동시 개발을 해도 소스 관리가 되며, 버전 컨트롤도 되기 때문에 앞으로 쿼리문 관리도 더욱 쉬워질 전망이다.

쿼리문을 이용해서 개발하다 보면 주로 반복되는 패턴들이 있다. 그래서 숙련된 개발자나 관리자들은 이러한 스크립트들을 별도로 모아서 관리하고 있다. 하지만 이제는 매니지먼트 스튜디오의 템플릿 탐색기(template explorer)와 보조 편집기(assisted editor)를 이용하면 이러한 반복되는 패턴들을 쉽게 이용할 수가 있다. 템플릿 탐색기는 자기만의 템플릿을 등록하거나 기존에 등록된 템플릿을 이용할 수 있으며, 보조 편집기는 SP, 트리거, 함수 같은 것들을 만들기 쉽게 도와주는 편집기이다.

<화면 5> 템플릿 탐색기 [View]-[Templete Explorer]

<화면 6> 보조 편집기 [SQL Instance]-[Databases]-[Programmability]-[Stored Procedures]-마우스 오른쪽 버튼-[New Stored Procedure]

튜닝의 조언자, 데이터베이스 튜닝 어드바이저
기존 인덱스 튜닝 마법사는 인덱스만을 튜닝하는 데 도움을 주었다. 하지만 튜닝 어드바이저는 인덱스뿐만 아니라 파티셔닝과 같은 전반적인 데이터베이스 튜닝에 대한 조언을 해준다. 먼저 프로필러로 해당 DB를 추적한 다음에 이를 trc 파일로 저장을 한다. 이를 튜닝 어드바이저에서 불러와서 튜닝을 하면 어떻게 하라는 권고 사항을 알려준다. <화면 7>의 예제를 보면, 튜닝 어드바이저가 해당 테이블의 현재 인덱스를 삭제하라고 조언하고 있다.

<화면 7> Database Tuning Advisor

소유자와 사용자를 분리하는 스키마
SQL 서버 2000에서는 데이터베이스 오브젝트의 소유자가 사용자였다. 예를 들면 SQL 서버 2000에서 Northwind DB의 Products 테이블의 소유자는 dbo이다.

Northwind뿐만 아니라 아마 대부분의 테이블 소유자는 모두 dbo로 되어 있을 것이다. 그 이유는 테이블의 소유자를 어떤 한 사용자로 두었다가 만약 그 사용자를 교체해야 한다면, 모든 데이터베이스 오브젝트의 소유자를 다 바꿔줘야 하는 불편이 있기 때문이다. 이는 애플리케이션 프로그램의 변경에도 영향을 미치는데 애플리케이션에서 해당 오브젝트를 사용하는 코드를 기술할 때 대부분 소유자를 명시하기 때문이다. 예를 들어

pubs.dbo.MyProc

이런 식으로 저장 프로시저를 호출해야 하기 때문에 소유자의 변경은 프로그램 전체를 다 변경해야 한다는 심각한 문제점이 발생한다. 그래서 대부분 그냥 소유자는 dbo로 통일해서 쓰는 경우가 많았다. SQL 서버 2005에서는 이러한 문제점을 개선하고자 스키마라는 개념을 확장했다. 데이터베이스의 오브젝트들을 묶어서 스키마라고 하고 사용자는 이 스키마를 소유할 수 있는 것이다.

<그림 4> 스키마 사용자 분리

블로킹이 걸리는 경우

저장 프로시저 소유자를 명시하지 않아 블로킹이 걸리는 경우

이전의 SQL 서버에서는 자신의 소유가 아닌 저장 프로시저를 호출할 때 소유자를 명시하지 않고 호출하는 것이 가능하다. 예를 들면 다음처럼 하는 것이다.

exec MyProc

그런데 이럴 경우 간혹 프로필러로 추적해 보면 캐시 부적중(cache miss)이 발생한다. 즉, 바로 재사용 가능한 실행 계획을 찾지 못하고 한 번 실패를 한 후에 컴파일 잠금을 하고 기존 실행 계획 중 재사용할 수 있는 것이 있는지 찾아본다. 그러다가 기존에 재사용 가능한 실행 계획이 있다는 것을 발견하고 재컴파일을 하지 않고 기존 실행 계획을 재사용하는 것이다. 이런 일련의 과정에서 문제가 되는 것은 바로 컴파일 잠금이 발생한다는 것이다. 대규모 사용자가 동시에 이 SP를 호출한다면 블로킹이 걸릴 수도 있는 것이다. 그러므로 소유자를 명시하는 것이 바람직한 방법이다. 자세한 내용은 다음을 참조하기 바란다.

◆ http://support.microsoft.com/default.aspx?scid=kb;en-us;263889
◆ 『고급 SQL 서버 개발자 가이드』 64쪽～65쪽(켄 헨더슨 저/ 하성희 역)

그러므로 이제는 소유자가 바뀌더라도 해당 오브젝트들의 소유자를 모두 바꾸어 줄 필요가 없다. 단지 스키마의 소유자를 바꾸어 주면 되는 것이다. 직접 실습을 해보자. 먼저 3명의 로그인을 생성한다.

CREATE LOGIN LoginA WITH PASSWORD = '123';
CREATE LOGIN LoginB WITH PASSWORD = '123';
CREATE LOGIN LoginC WITH PASSWORD = '123';

그 다음 각각의 로그인에 맞는 사용자를 생성한다.

USE AdventureWorks;
CREATE USER UserA FOR LOGIN LoginA WITH DEFAULT_SCHEMA = Schema1;
CREATE USER UserB FOR LOGIN LoginB;
CREATE USER UserC FOR LOGIN LoginC;

이 때 UserA에만 기본 스키마로 Schema1이라는 것을 할당했다. 나머지는 명시를 하지 않았는데, 그러면 기본 스키마로 dbo가 할당된다. 이제 UserA에는 테이블 생성 권한을 주고, UserB에는 Schema1 스키마의 조회 권한을 주자.

GRANT CREATE TABLE to UserA;
GRANT SELECT on Schema::Schema1 TO UserB;

Schema1 스키마의 소유자를 UserA로 정하자.

CREATE SCHEMA Schema1 AUTHORIZATION UserA;

사용자 UserA로 변환한 다음 테이블을 생성한다.

SETUSER 'UserA';
CREATE TABLE Schema1.TestTable(id integer);

사용자 UserB로 변환한 다음 조회를 해보자. 잘된다.

SETUSER 'UserB';
SELECT * FROM Schema1.TestTable;

이제 Schema1의 소유자를 바꿔보자.

SETUSER;
ALTER AUTHORIZATION ON SCHEMA::[Schema1] TO [UserC];

다시 UserB에 조회 권한을 주고 조회해 보면 잘된다. 즉, 스키마의 소유자가 변하더라도 다른 곳을 수정하지 않아도 되는 것이다.

동의어 기능

SQL 서버 2005에서는 스키마명을 꼭 명시해 주어야 하기 때문에 이름이 길어져서 코딩하는데 약간 불편함이 있을 수 있다. 그럴 때에는 동의어 기능을 이용하면 코딩에 드는 노력을 줄일 수 있다.

CREATE SYNONYM Orders FOR Sales.SalesOrderHeader

이와 같이 지정을 하면 다음부터는 Sales.SalesOrderHeader라고 길게 치지 않아도 Orders라고 치면 된다. 하지만 이 방식은 코딩 노력을 줄여준다는 의미에서는 좋은 반면 가독성 측면에서는 좋지 않은 방법이 될 수도 있다. 왜냐하면 소스코드라는 것은 한군데 있으면 판독하기 쉽지만 여러 군데에 소스코드가 나누어져 있다면 판독하기가 쉽지 않기 때문이다.

끊어진 소유권 체인도 연결 가능?
SQL 서버 2000에서 테이블과 저장 프로시저의 소유자가 같은 경우에는 전혀 권한 체크를 하지 않는다. 예를 들어 Table1과 저장 프로시저 Proc1(Proc1에서 Table1을 참조)의 소유자가 UserC라면 누구든 Proc1을 실행할 수 있는 사람이면 비록 Table1에 권한이 없더라도 Proc1을 통해 실행이 가능하다. 이를 소유권 체인(ownership chain)이라고 부른다.

<그림 5> SQL 서버 2000의 소유권 체인

하지만 저장 프로시저와 테이블의 소유자가 다른 경우 권한 체크를 하게 되며 권한이 없을 경우 에러를 발생시킨다. 예를 들면 Table2의 소유자가 UserD이고 Proc2(Proc2에서 Table2를 참조)의 소유자가 UserB라면 UserA가 UserB에 실행 권한이 있다고 하더라도 테이블과 저장 프로시저간의 소유자가 다르므로 권한 체크를 한다. 그러므로 Table2에 대해 UserA가 권한이 없다면 에러를 발생시킨다. 이를 끊어진 소유권 체인(broken ownership chain)이라고 한다. 이를 해결하기 위해 SQL 서버 2005에서는 WITH　EXECUTE 구문을 제공한다.

<그림 6> SQL 서버 2005의 execution context

ALTER PROC UserB.Proc2 WITH EXECUTE AS 'UserZ'

이와 같이 실행을 하면 UserB.Proc2는 마치 UserZ가 실행하는 것처럼 가장하게 된다. 따라서 UserZ가 Table2에 대해 권한만 있다면 이 구문은 실행이 잘된다.

데이터를 보호하기 위한 암호화 메커니즘 제공
만약 데이터 중에 사용자 패스워드가 있다면 대부분 암호화하여 저장할 것이다. SQL 서버 2005에서는 이를 위해 인증(certificate), 대칭키(symmetric keys), 비대칭키(asymmetric keys) 등 세 가지 방식의 암호화 메커니즘을 제공한다. 사용자는 이 세 가지 중 한 가지를 선택하여 데이터를 암호화하여 보호할 수 있다.

CREATE CERTIFICATE Cert1
WITH SUBJECT = 'Test',
ENCRYPTION_PASSWORD = '123',
EXPIRY_DATE = '2010/12/31';

DECLARE @n nvarchar(100);
SET @n = EncryptByCert ( Cert_ID('Cert1'), N'ABC');

SELECT @n;

SELECT CAST ( DecryptByCert( Cert_ID('Cert1'), @n, N'123') as nvarchar);

------------------------------
ㅤㅂㅝㅋ?O????使?′ㅤㅈㅗㄵ???啣??얏??ㅤㄷㅘㅎ???손?蚓恩????ㅤㄷㅠㄻ???ㅤㅈㅐㅌ?�??ㅤㅃㅝㅌㅤㄴㅖㄺ??艅?
(1 row(s) affected)

------------------------------
ABC
(1 row(s) affected)

이 예제를 보면 인증 방식으로 암호화하는데 비밀번호는 123으로 했다. 암호화를 하니 그냥 조회해보면 알아볼 수 없는 값들이 나온다. 하지만 비밀번호를 이용하여 제대로 풀면 원래의 값을 조회할 수 있다.

SQL 서버 2005 관리자가 봐야 할 것들
이번에는 SQL 서버 2005의 관리자라면 한 번쯤 봐야할 만한 내용들을 전체적으로 알아보고, 추가로 보안에 대한 내용을 소개했다. 마지막인 다음 연재에서는 대용량 데이터를 다루기 위한 테이블 파티셔닝과 가용성(availability)을 높이기 위한 미러링과 스냅샷에 대해 소개할 예정이다.@

* 이 기사는 ZDNet Korea의 제휴매체인 마이크로소프트웨어에 게재된 내용입니다.

[출처] [SQL 서버 2005 실전 활용] ③ 관리자가 알아야 할「변화들」 |작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL (0)	2008.04.29
[SQL 서버 2005 실전 활용] ② 닷넷과의 통합 (0)	2008.04.29
SQL Server DBA 가이드 (0)	2008.04.29
T-SQL로 테이블, 컬럼명 조회하기 (0)	2008.04.29
색인 생성과 관리 (0)	2008.04.29

PREV 1 ···40 41 42 43 44 45 46 ···68 NEXT

컴퓨토피아