'2005' 태그의 글 목록 (8 Page)

2005

세로 컬럼들을 가로 컬럼으로 모으기.... pivot 쿼리 2008.04.29
SQL Server 2005의 XQuery 소개 2008.04.29
SQL Server 2005의 분할된 테이블 및 인덱스 2008.04.29

세로 컬럼들을 가로 컬럼으로 모으기.... pivot 쿼리

2008. 4. 29. 09:29

728x90

글쓴이 : 김홍선

pivot 쿼리란 row의 형태로 주어지는 데이타를 column의 형태로 보여주는 쿼리이다.
예를 들어, 아래와 같이 scott.emp 테이블이 주어졌다고 하면,

EMPNO ENAME DEPTNO
-----------------------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10

위 테이블에서, 한 row는 해당 deptno에 속하는 한 명의 사원(employee)을 나타낸다.
이 테이블에서 각 deptno에 속하는 사원의 수를 다음과 같이 출력하고자 한다면,

DEPTNO_10 DEPTNO_20 DEPTNO_30
-------------------------------
3 5 6

쿼리를 아래와 같이 만들어 준다.

SELECT COUNT (DECODE (deptno, 10, 1)) deptno_10,
   COUNT (DECODE (deptno, 20, 1)) deptno_20,
   COUNT (DECODE (deptno, 30, 1)) deptno_30
FROM emp

pivot 쿼리의 특징 중의 하나는 select 절에 count(decode(...)), sum(decode(...)), max(decode(...)) 등의 함수를 많이 쓴다는 것이다.

예제 추가)

아래와 같이 deptno 별로 clerk, salesman, manager의 수가 나타나도록 쿼리를 만들어보자.
(여기서도 emp 테이블을 사용한다.)

DEPTNO CLERK SALESMAN MANAGER
---------- ---------- ---------- ----------
10 1 0 1
20 2 0 1
30 1 4 1

쿼리는 아래와 같다.

SELECT deptno
   , COUNT (DECODE (job, 'CLERK', 1)) clerk
   , COUNT (DECODE (job, 'SALESMAN', 1)) salesman
   , COUNT (DECODE (job, 'MANAGER', 1)) manager
FROM emp
GROUP BY deptno

위에서 예를 든 것과 같이 row 형태의 데이타를 column 형태로 보여주는 쿼리를 row-to-column pivot 쿼리라고 하고, 그 반대로 column 형태를 row 형태로 보여주는 쿼리를 column-to-row pivot 쿼리라고 하자.

column-to-row pivot 쿼리의 예는 아래 페이지에서 확인할 수 있다.
- http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=77

[출처] [본문스크랩] 세로 컬럼들을 가로 컬럼으로 모으기.... pivot 쿼리|작성자 우리자나

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

퀴즈 문제 (0)	2008.04.29
DateTime ? Char(8) or (10) (0)	2008.04.29
컬럼명 변경하기 (0)	2008.04.29
Table 변수 vs 임시 테이블 (Ver. 2000) (0)	2008.04.29
SQL Server 2005의 XQuery 소개 (0)	2008.04.29

SQL Server 2005의 XQuery 소개

2008. 4. 29. 09:17

728x90

SQL Server 2005의 XQuery 소개

Prasadarao K. Vithanala - Microsoft Corporation

요약: 이 백서에서는 FLWOR 문, XQuery의 연산자, if-then-else 구문, XML 생성자, 기본 제공되는 XQuery 함수, 형식 캐스팅 연산자, 이러한 각 기능을 사용하는 방법에 관한 예제 등을 비롯하여 SQL Server 2005에서 구현된 XQuery의 다양한 기능을 소개합니다. 또한 SQL Server 2005에서 지원되지 않는 XQuery 기능과 해결 방법이 설명되고 XQuery가 유용하게 사용되는 세 가지 시나리오가 제공됩니다.

목차

소개
SQL Server 2005의 XML 데이터 형식
XQuery 소개
XQuery 식 구조
XQuery의 연산자
if-then-else 구문
XQuery를 사용하여 XML 생성
기본 제공 XQuery 함수
형식 관련 식
관계형 열과 변수 액세스
지원되지 않는 기능과 해결 방법
최상의 실행 방법 및 지침
XML 데이터 수정
XQuery 사용 시나리오
결론

소 개

XML은 문서 형식으로 사용하도록 개발되었습니다. 그러나 확장성, 국제화 지원, 구조적 및 반구조적 데이터 표시 기능, 시스템 및 사용자를 위한 향상된 가독성 등을 비롯한 추가 기능으로 인해 XML은 플랫폼에 독립적인 데이터 표현 형식으로 매우 널리 사용되고 있습니다. XML이 널리 보급됨에 따라 사용자들은 데이터 통합과 관련된 문제와 같은 복잡한 비즈니스 문제를 해결하기 위해 XML을 적용하고 있습니다.

많은 시나리오에서 정보를 테이블에 저장한 다음 해당 정보에서 XML 메시지를 작성하는 것보다는 정보를 XML 형식으로 저장하는 것이 권장됩니다. 이러한 시나리오에 대한 자세한 내용은 MSDN 기사 Microsoft SQL Server 2005를 위한 XML 최상의 실행 방법을 참조하십시오. 문서를 저장하고 반구조적 데이터를 표현하기 위해 XML을 적용하는 방법은 서버의 데이터 관리를 단순화하는 데이터 저장 형식으로 XML이 발전하게 된 원동력이 되어 왔습니다.

그러나 관계형 테이블에 저장된 XML 데이터에서 정보를 BLOB로 추출하려면 정보를 추출하여 XML로 표현할 수 있는 쿼리 언어가 필요하다는 문제가 대두되었습니다. Microsoft SQL Server 2000에서는 쿼리에 사용할 수 있는 OpenXML이 제공되었습니다. 그러나 OpenXML은 XML 데이터를 관계형 형식에 매핑하도록 설계되었기 때문에 XML 데이터 모델을 완전하게 지원하지 못했습니다(표 1 참조).

관계형 데이터 모델과 XML 데이터 모델은 여러 측면에서 다릅니다. 다음 표에는 두 데이터 모델의 주요 차이점이 나와 있습니다.

관계형 데이터 모델이 XML 문서를 기본적으로 저장하도록 확장되는 것은 여러 요인 중에서 특히 유형이 다른 구조적 데이터를 더 많이 처리해야 하는 요구가 커지고 암시적 순서를 유지해야 할 필요성이 증가했기 때문입니다. 또한 반구조적 또는 태그 정보 처리에 관한 SQL 언어의 한계로 인해 XQuery 언어가 개발되었습니다. XQuery 언어는 XML 데이터의 특성과 XML 데이터 처리와 관련된 문제를 고려하여 처음부터 새롭게 디자인되었습니다.

SQL Server 2005에서는 XML데이터 형식을 사용한 XML 데이터의 네이티브 저장이 기본적으로 지원됩니다. XQuery 1.0은 XML 데이터에 대한 쿼리를 공식화하기 위해 W3C(World Wide Web Consortium) XML Query Working Group에서 정의한 언어입니다. SQL과 마찬가지로 XQuery는 SQL 및 Xpath에 대한 기본 지식만으로 쉽게 이해할 수 있는 선언적 쿼리 언어입니다.

표 1 관계형 데이터 모델과 XML 데이터 모델의 차이점

기 능	관계형 데이터 모델	XML 데이터 모델
평면 구조적 데이터	평면 테이블을 사용하여 데이터를 열 형식으로 저장합니다. 평면 구조적 데이터를 저장하는 데 권장되는 방법입니다.	문서 순서를 유지해야 하거나 스키마가 유연하거나 알려지지 않은 경우 유용합니다.
반구조적 데이터	관계형 모델을 사용하여 반구조적 데이터를 모델링하는 것이 어렵습니다.	변수 또는 확장 스키마를 사용하여 반구조적 데이터를 표현할 수 있는 기능을 지원합니다.
태그 데이터	BLOB 저장소를 벗어나 태그 데이터를 저장하는 데 적합하지 않습니다.	HTML, RTF 등과 같은 태그 데이터를 저장할 수 있는 기능을 지원합니다.
중첩 또는 계층 데이터 구조	외래 키로 여러 테이블을 연결하여 사용함으로써 중첩 데이터를 지원하지만 중첩 깊이가 증가하거나 알려지지 않은 경우에 관계형 형식으로 저장된 중첩 데이터를 검색하는 데 필요한 쿼리가 복잡해집니다.	중첩 또는 계층 데이터 구조를 표현할 수 있는 기능을 지원합니다.
데이터 순서	유지되지 않습니다.	유지됩니다.
입력 데이터	유형이 같습니다.	유형이 다릅니다.
결과 집합	유형이 같습니다.	유형이 다릅니다.

이 문서는 2004년 7월에 발표된 XQuery 초안에 기초하는 SQL Server 2005의 XQuery 1.0 구현을 기반을 두고 있습니다. 이 문서의 첫 번째 절에서는 새 XML 데이터 형식과 관련 기능에 대한 개요를 제공합니다. 뒤이어 나오는 절에서는 새 XQuery 언어와 이점, FLWOR 문, XQuery의 다양한 연산자, XQuery의 기본 제공 함수, 형식 관련 식, SQL Server 2005에서 지원되지 않는 기능 등을 소개합니다. 마지막 부분의 절에서는 최상의 실행 방법과 지침, XML 데이터 수정 및 XQuery 사용 시나리오를 제공합니다.

SQL Server 2005의 XML 데이터 형식

SQL Server 2005에서 소개된 새 XML 데이터 형식은 XML 문서와 단편을 데이터베이스에 저장할 수 있는 기능을 사용자에게 제공합니다. XML 데이터 형식을 사용하면 열, 저장 프로시저 또는 함수의 매개 변수, 변수 등을 만들 수 있습니다.

또한 사용자는 XML 형식 열을 XML 스키마 컬렉션과 연관시켜 형식 있는 XML 열을 만들 수 있습니다. 컬렉션의 XML 스키마는 XML 인스턴스의 유효성을 검사하고 형식화하는 데 사용됩니다.

형식 있는/형식 없는 XML 데이터 형식 비교

XML 데이터 형식을 XML 스키마 컬렉션과 연관시켜 XML 인스턴스에 스키마 제약 조건을 적용할 수 있습니다. XML 데이터가 XML 스키마 컬렉션과 연관된 경우 이를 형식 있는 XML이라고 부르고 그렇지 않은 경우 형식 없는 XML이라고 부릅니다.

SQL Server 2005 XML 데이터 형식은 ISO SQL-2003 표준 XML 데이터 형식을 구현합니다. 따라서 올바른 형식의 XML 1.0 문서뿐만 아니라 최상위 텍스트 노드와 임의 개수의 최상위 요소가 포함된 소위 XML 콘텐츠 단편도 저장할 수 있습니다. 데이터가 올바른 형식을 가지는지 검사되며(XML 데이터 형식이 XML 스키마에 바인딩될 필요는 없음) SQL-2003 콘텐츠 완화법에 따라 올바른 형식이 아닌 데이터는 거부됩니다.

스키마가 미리 알려지지 않은 경우에 형식 없는 XML이 유용합니다. 또한 스키마가 알려져 있지만 변경 속도가 빨라서 유지 관리하기 힘들거나 여러 스키마가 존재하며 외부 요구 사항에 기초하여 이러한 스키마가 나중에 데이터에 바인딩될 경우에 형식 없는 XML이 유용합니다. 이외에도 데이터베이스 엔진이 지원하지 않은 XML 스키마 구문(예: key/keyref, lax validation)이 XML 스키마에 포함된 경우에도 형식 없는 XML이 유용합니다. 이 경우 CLR(Common Language Runtime) 사용자 정의 함수에서 System.XML 유효성 검사기를 사용하여 유효성 검사를 제공할 수 있습니다.

XML 스키마 컬렉션에 XML 데이터를 설명하는 XML 스키마가 있을 경우 XML 스키마 컬렉션을 XML 열과 연관시켜 형식 있는 XML을 생성할 수 있습니다. XML 스키마는 데이터의 유효성을 검사하고 쿼리 및 데이터 수정 문의 컴파일 도중에 형식 없는 XML보다 정확한 형식 검사를 수행하며 저장소 및 쿼리 처리를 최적화하는 데 사용됩니다.

형식 있는 XML 열, 매개 변수 및 변수는 선언 시에 옵션으로 지정할 수 있는 XML 문서나 콘텐츠 단편을 저장할 수 있습니다(각각 DOCUMENT 또는 CONTENT 옵션으로 지정할 수 있으며 기본값은 CONTENT). 또한 XML 스키마 컬렉션을 제공해야 합니다. 각 XML 인스턴스에 정확하게 하나의 최상위 요소가 있을 경우 DOCUMENT를 지정하고 그렇지 않을 경우에는 CONTENT를 사용합니다. XQuery 컴파일러는 DOCUMENT 플래그 정보를 사용하여 정적 형식 유추 도중에 단일 최상위 요소를 유추합니다.

XML 데이터 형식의 메서드

XML 데이터 형식은 XML 인스턴스를 조작하는 데 사용할 수 있는 5개의 메서드를 지원합니다. XML 데이터 형식의 이러한 메서드는 다음과 같습니다.

query() 메서드는 XML 노드 목록으로 평가되는 XQuery 식을 가집니다. 이 메서드를 사용하면 XML 문서의 단편을 추출할 수 있습니다. 형식 없는 XML의 인스턴스가 결과로 반환됩니다.

value() 메서드는 XML 문서에서 스칼라 값을 관계형 값으로 추출하는 데 사용됩니다. 이 메서드는 단일 노드와 반환되는 원하는 SQL 형식을 식별하는 XQuery 식을 가집니다. XML 노드 값이 반환되어 지정된 SQL 형식으로 캐스팅됩니다.

exist() 메서드를 사용하면 XML 문서에서 검사를 수행하여 XQuery 식의 결과가 비어 있는지 여부를 확인할 수 있습니다. XQuery 식이 비어 있지 않은 결과를 반환하면 이 메서드의 결과는 1입니다. 결과가 비어 있으면 0이고 XML 인스턴스 자체가 NULL이면 결과도 NULL입니다.

XML 데이터 형식의 nodes() 메서드는 XML 문서를 관계형 데이터로 분해하는 작업을 용이하게 합니다. nodes() 메서드는 제공된 XQuery 식에서 식별하는 컨텍스트 노드를 각 행에서 나타내는 행 집합을 반환합니다. 또한 query(), value(), exist(), nodes() 등과 같은 XML 데이터 형식의 메서드를 nodes() 메서드에서 반환된 컨텍스트 노드에서 호출할 수 있습니다.

modify() 메서드를 사용하면 XML 문서의 내용을 수정할 수 있습니다. 이 메서드는 XML DML 문을 사용하여 XML 인스턴스에서 하나 이상의 노드를 삽입, 업데이트 또는 삭제합니다. NULL 값에 적용될 경우 오류가 발생합니다.

자세한 내용은 Microsoft SQL Server 2005의 XML 지원 백서를 참조하십시오.

XQuery 소개

XQuery는 XML 데이터를 쿼리하기 위한 새 언어이며 XPath 2.0에 기초한 탐색 액세스를 허용합니다. 이 절에서는 XQuery와 Xpath의 관계, XQuery를 사용할 경우의 이점, XQuery의 적용 영역, XQuery에서 XML 스키마의 역할 등을 비롯하여 XQuery 언어의 다양한 측면에 대한 개요를 제공합니다.

XPath 2.0 개요

W3C에 의해 정의된 XPath 1.0 (영문)은 XML 문서의 일부분을 찾기 위한 언어입니다. Xpath는 XML 문서에서 노드를 식별하기 위해 경로 기반 구문을 사용합니다. 또한 Xpath는 XSLT 1.0 (영문) 및 XPointer (영문)에 대한 핵심 구문을 정의합니다. XPath 1.0에는 문자열, 부울 값 및 부동 소수점 수를 처리하기 위한 기본 제공 함수가 있습니다. XPath 1.0은 필터링 기준을 지정하는 기능과 함께 노드 집합을 필터링하기 위한 구문을 정의합니다. 더 자세한 형식 시스템을 지원하고 더 많은 기능을 제공하기 위해 XPath 1.0은 XPath 2.0 (영문)으로 확장되고 있습니다. XQuery 1.0 (영문)은 XPath 2.0에 기반을 두며 XPath 2.0의 탐색 및 필터링 측면에 순서 지정, 형태 변경, 구성 및 유효성 검사 기능을 추가합니다.

XQuery 개요

XQuery는 XML 형식으로 저장된 데이터를 쿼리하려는 특별한 목적을 위해 XML Query Working Group에 의해 처음부터 새로 설계된 형식 있는 선언적 기능 언업니다. XQuery는 XPath 2.0 및 XSLT 2.0 (영문)과 같은 다른 XML 표준과 동일한 데이터 모델 및 XML 스키마 (영문) 기반 형식 시스템을 사용합니다. XQuery는 형식화되지 않았거나(즉, 데이터와 연관된 스키마가 없는) XML 스키마로 형식화된 XML 문서에서 작동하도록 설계되었습니다. 앞에서 언급한 것처럼 XQuery 1.0은 기본적으로 XPath 2.0의 상위 집합입니다. XPath 2.0의 기능 외에도 다음과 같은 기능이 있습니다.

* 문서 순서와 다른 순서로 정렬할 수 있도록 FLWOR 절에 order by 절을 추가합니다.

* 이후에 사용하기 위해 식 결과에 이름을 지정하기 위해 FLWOR 절에 let 절을 추가합니다(SQL Server 2005에서는 지원되지 않음).

* 쿼리 프롤로그에 정적 컨텍스트 항목을 지정하는 방법을 제공합니다(예: 네임스페이스 접두사 바인딩).

* 새 노드를 생성하는 기능을 제공합니다.

* 사용자 정의 함수를 정의하는 기능을 제공합니다(SQL Server 2005에서는 지원되지 않음).

* 모듈/라이브러리를 만드는 기능을 제공합니다(SQL Server 2005에서는 지원되지 않음).

XQuery의 이점

* SQL 및 Xpath에 대한 지식이 있는 경우 쉽게 배울 수 있습니다.

* 쿼리가 XQuery로 작성된 경우 XSLT로 작성된 쿼리보다 코드가 적게 필요합니다.

* XML 데이터가 형식화된 경우 XQuery를 강력한 형식의 언어로 사용할 수 있으며 이 경우 암시적 형식 캐스트를 방지하여 쿼리의 성능을 향상시키고 쿼리 최적화 수행 시에 사용할 수 있는 형식 보장을 제공할 수 있습니다.

* 형식 없는 데이터의 경우 높은 유용성을 제공하기 위해 Xquery를 약한 형식의 언어로 사용할 수 있습니다. SQL Server 2005는 강력한 형식 및 약한 형식 관계에 대한 지원을 사용하여 정적 형식 유추를 구현합니다.

* XQuery는 쿼리를 수행하는 데 필요한 코드가 XSLT보다 적기 때문에 유지 관리 비용이 적게 들어갑니다.

* XQuery는 W3C 권고가 되어가고 있으며 주요 데이터베이스 공급업체에 의해 지원될 것입니다.

이 문서를 작성할 시점에 XQuery 1.0 언어와 관련하여 다음과 같은 주의 사항이 있었습니다.

XQuery 사양은 현재 개발 중이며 이후에 변경될 수 있습니다. SQL Server 2005는 W3C 초안의 검증된 부분에 기반을 두고 구현되었습니다.

XQuery 적용 영역

XQuery의 적용 영역을 다음과 같이 넓게 구분할 수 있습니다.

* 쿼리/분석을 위한 XQuery : XQuery는 많은 양의 데이터를 쿼리하는 데 적합하며 필요한 정보를 필터링, 정렬, 순서 지정 및 재활용하는 기능을 제공합니다. 반구조적 정보를 나타내는 XML 문서, 이름/값 쌍 속성 모음, 잠재적 응용 프로그램 오류와 보안 문제를 식별하기 의한 응용 프로그램 로그, 트랜잭션 로그 및 감사 로그 분석 등을 쿼리하는 것을 일반적인 적용 예로 들 수 있습니다.

* 응용 프로그램 통합을 위한 XQuery : 많은 조직에서 독자적인 응용 프로그램 통합 접근 방법에서 벗어나 표준 기반의 응용 프로그램 통합 접근 방법을 채택하기 시작함에 따라 내부 응용 프로그램 특정 형식의 데이터를 표준 교환 형식으로 변환해야 하는 필요성이 중요해지고 있습니다. XML 데이터를 생성 및 변환하는 기능을 가진 XQuery는 이러한 요구를 충족합니다. 응용 프로그램 통합 영역에서 XQuery가 사용되는 일반적인 예로 네이티브 XML 데이터베이스/관계형 데이터 데이터 소스를 사용하는 특정 응용 프로그램에 사용되는 용어를 XML/관계형 데이터 형식을 사용하는 다른 응용 프로그램에 사용되는 언어로 변환하는 경우를 들 수 있습니다.

서버에서 XQuery를 사용할 경우의 이점

XQuery를 사용하여 서버에서 XML 처리를 수행하는 것은 클라이언트 쪽 XML 처리와 비교하여 많은 이점이 있습니다. 그 중에서 몇 가지 이점을 요약하면 다음과 같습니다.

* 네트워크의 트래픽 감소 : XML 데이터가 서버에서 처리될 경우 결과만 클라이언트에게 전달됩니다. 결과적으로 네트워크에서 트래픽이 줄어듭니다.

* 보안 향상: 필요한 데이터만 클라이언트에게 보내지므로 클라이언트 쪽 XML 처리를 사용할 경우처럼 전체 데이터가 네트워크에 노출되는 위험이 방지됩니다.

* 향상된 관리 효율성 : 서버에서 XML을 처리하므로 클라이언트에서 코드가 브라우저에 독립적입니다. 결과적으로 클라이언트 쪽에서 관리 효율성이 향상됩니다.

* 향상된 성능 : 서버에서 XQuery를 사용하여 작성하는 쿼리는 SQL 쿼리 엔진의 최적화를 따릅니다. 따라서 클라이언트에서 전체 데이터를 검색하고 필터링할 때와 비교하여 성능이 향상됩니다. 또한 XML 데이터 형식 열에서 인덱스를 작성하여 성능 향상을 실현할 수 있습니다.

XQuery 구현에서 XML 스키마가 사용되는 방법

XML 데이터 형식과 연관된 XML 스키마 컬렉션은 다음과 같이 관계형 엔진에 사용됩니다.

* 삽입 작업 도중에 XML 인스턴스의 유효성을 검사하기 위해 사용됩니다.

* 수정 작업 도중에 XML 인스턴스의 유효성을 검사하기 위해 사용됩니다.

* 더 나은 쿼리 계획을 생성하고 많은 런타임 검사를 방지하여 오류를 조기에 감지하고 쿼리 성능을 향상시키기 위해 정적 형식 검사 도중에 XML 스키마에 포함된 형식 정보가 사용됩니다.

* SQL Server는 XML 스키마에 존재하는 형식 정보를 사용하여 저장소를 최적화합니다.

XQuery 식 구조

SQL Server 2005의 XQuery 식은 두 개의 섹션, 즉 프롤로그와 본문으로 구성됩니다. 프롤로그에는 네임스페이스 선언 하위 섹션이 포함될 수 있습니다. 네임스페이스 선언은 접두사와 네임스페이스 URI 간의 매핑을 정의하는 데 사용되므로 쿼리 본문에서 네임스페이스 URI 대신에 접두사를 사용할 수 있게 합니다. 또한 declare default namespace 선언을 사용하여 요소 이름에 대한 기본 네임스페이스를 바인딩하면 접두사 없이 요소 이름을 참조할 수 있습니다.

XQuery 식의 본문에는 쿼리의 결과를 정의하는 쿼리 식이 포함됩니다. 예를 들어, 본문은 서명 FLWOR 식(이 문서의 FLWOR 문 참조), XPath 2.0 식(이 문서의 XPath 2.0 식 참조) 또는 생성 식이나 산술 식과 같은 다른 XQuery 식이 될 수 있습니다.

예제: XQuery의 프롤로그 섹션에 기본 네임스페이스 지정

다음 쿼리는 JobCandidateID가 3인 후보의 모든 Employment 노드를 선택합니다. 이 쿼리는 기본 네임스페이스를 정의하며 네임스페이스 접두사가 사용되지 않습니다.

SELECT Resume.query('
declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Employment
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: "WITH XMLNAMESPACES" 절을 사용하여 네임스페이스 지정

또한 SQL Server는 사용자가 SQL 쿼리 단위로 SQL WITH 절에서 XML 네임스페이스 바인딩을 선언할 수 있게 하는 SQL 2003 표준 확장을 지원합니다. 따라서 여러 XML 데이터 형식 메서드 호출에서 선언을 반복할 필요가 없습니다. 다음 쿼리는 앞 예제에 나온 쿼리의 수정된 버전을 보여 줍니다. 이 쿼리는 WITH XMLNAMESPACES 절을 사용하여 네임스페이스를 선언합니다.

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS "RES")
SELECT Resume.query('
/RES:Resume/RES:Employment
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

XPath 2.0 식

XQuery는 XPath 2.0 식을 사용하여 문서의 노드를 찾고 단일 문서나 여러 문서의 특정 위치에서 다른 위치로 탐색합니다. Xpath를 사용하여 정의하는 탐색 경로는 /로 구분된 일련의 단계로 구성됩니다. 단일 단계는 축, 노드 테스트 및 0개 이상의 단계 한정자로 구성됩니다.

축은 컨텍스트 노드를 기준으로 이동 방향을 지정합니다. SQL Server 2005에서 지원되는 축은 child, descendant, parent, attribute, self 및 descendant-or-self입니다.

노드 테스트는 단계에서 선택된 모든 노드가 충족해야 하는 조건을 지정합니다. 노드 이름이나 노드 유형에 기초하여 노드 조건을 지정할 수 있습니다.

단계 한정자는 조건자 또는 역참조를 사용하여 정의할 수 있습니다. 조건자는 노드 시퀀스에서 필터로 작동하고 대괄호 안에 지정되는 식입니다. 역참조는 노드 시퀀스의 요소 및/또는 특성을 참조되는 노드에 매핑합니다. 역참조에 입력으로 전달되는 노드 시퀀스는 IDREF 또는 IDREFS 형식의 요소나 특성을 포함해야 합니다. 역참조는 입력 시퀀스의 요소 및 특성에서 추출된 IDREF 값과 일치하는 ID 형식 특성 값을 가지는 요소 노드로 구성된 새 시퀀스를 생성합니다.

Xpath 식의 단계는 왼쪽에서 오른쪽으로 평가됩니다. 단계가 실행되면 다음 단계의 평가 컨텍스트 항목이 설정됩니다. 경로 식의 컨텍스트 항목은 Xpath 식의 단계가 실행된 결과로 선택된 노드입니다. 단계는 이전 단계에서 얻어진 컨텍스트 항목을 기준으로 평가됩니다. XPath 식의 결과는 경로 식에서 왼쪽에서 오른쪽으로 모든 단계를 순서대로 실행한 후에 얻어진 문서 순서를 따르는 노드 시퀀스입니다.

다음 예제 식은 경로 식의 개념을 보여 주기 위해 AdventureWorks 데이터베이스의 [HumanResources].[JobCandidate] 테이블에서 XML 형식의 Resume 열을 사용합니다. 이 경로 식에서는 주소 형식이 Home으로 설정된 모든 주소 노드가 선택됩니다.

//child::ns:Addr.Type[.="Home"]/parent::node()

위 경로 식에서

또한 XQuery는 축을 지정하기 위한 축약 구문을 지원합니다. 다음 표에는 축과 해당 축약 구문이 나와 있습니다.

표 2 축의 축약 구문

축	축약 형식
Attribute	@
Child
descendant-or-self::node()	//
parent::node()	..
self::node()	.

예제: 고용 기록에서 조직 이름 선택

다음 XPath 식은 Resume/Employment 노드의 자식 노드인 Emp.OrgName 요소의 자식 텍스트 노드를 선택합니다. 여기서 Emp.OrgName 요소의 텍스트 노드를 선택하기 위해 text()가 사용됩니다.

/Resume/Employment/Emp.OrgName/text()

FLWOR 문

FLWOR 문은 XQuery의 핵심 식이며 SQL의 SELECT 문과 비슷합니다. "flower"라고 발음되는 머리 글자어 FLWOR는 FOR, LET, WHERE, ORDER BY, RETURN을 나타냅니다. XQuery의 FLWOR 식을 사용하면 선언적 반복, 변수 바인딩, 결과 필터링, 정렬 및 반환 등과 같은 작업을 지정할 수 있습니다. SQL Server 2005에서는 FOR, WHERE, ORDER BY 및 RETURN이 지원됩니다.

For

FLWOR 식의 for 절을 사용하면 입력 시퀀스에서 바인딩된 변수의 선언적 반복을 정의할 수 있습니다. XPath 식, 원자 값 시퀀스, 리터럴을 사용하여 생성된 시퀀스, 생성자 함수 등을 사용하여 입력 시퀀스를 지정할 수 있습니다. 따라서 SQL SELECT FROM 절과 비슷하지만 프로그래밍 언어 "for" 구문과는 다릅니다.

또한 변수 바인딩이 for 절에 지정됩니다.

예제: for 절을 사용하여 이력서에서 모든 집 주소 요소 선택

다음 쿼리는 주소 형식이 Home으로 설정되었으며 JobCandidateID가 3인 모든 Address 노드를 선택합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $A in /RES:Resume/RES:Address/RES:Addr.Type[.="Home"]/..
return
$A
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

where

where 절은 where 절을 사용하여 지정된 식을 적용하여 반복 결과를 필터링합니다.

예제: where 절을 사용하여 모든 집 주소 요소 선택

다음 쿼리는 주소 형식이 Home으로 설정되었으며 JobCandidateID가 3인 모든 Address 노드를 선택합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $A in /RES:Resume/RES:Address
where $A/RES:Addr.Type[.="Home"]
return
$A
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

order by

order by 키워드를 사용하면 반환된 결과 집합에서 값을 정렬할 수 있습니다. order by 키워드에는 원자 값을 반환해야 하는 정렬 식이 허용됩니다. 원하는 경우 오름차순 또는 내림차순 정렬 순서를 지정할 수도 있습니다. 기본 정렬 순서는 오름차순입니다.

예제: order by 절을 사용하여 고용 기록을 오름차순으로 선택

다음 쿼리는 JobCandidateID가 3인 후보에 대해 고용 시작 날짜의 오름차순으로 모든 Employment 노드를 선택합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $EMP in /RES:Resume/RES:Employment
order by $EMP/RES:Emp.StartDate
return
$EMP
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

return

SQL의 SELECT 절과 비슷한 return 절을 사용하면 쿼리 결과를 정의할 수 있습니다. 모든 유효한 XQuery 식을 return 절에 지정할 수 있습니다. 또한 요소, 특성 등에 대한 생성자를 지정하여 return 섹션에서 XML 구조를 생성할 수 있습니다.

예제: return 절을 사용하여 고용 기록의 특정 요소 선택

다음 쿼리는 JobCandidateID가 3인 후보에 대해 Employment 노드의 StartDate, EndDate, OrgName, JobTitle 요소를 선택합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $EMP in /RES:Resume/RES:Employment
order by $EMP/RES:Emp.StartDate
return

{ $EMP/RES:Emp.StartDate }
{ $EMP/RES:Emp.EndDate }
{ $EMP/RES:Emp.OrgName }
{ $EMP/RES:Emp.JobTitle }

') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

현재 SQL Server 2005에서 구현된 XQuery는 let 절을 지원하지 않습니다. 이에 대한 자세한 내용은 이 문서의 뒤에 나오는 지원되지 않는 기능 및 해결 방법 부분에서 설명합니다.

FLWOR 식 및 XPath 식 비교

XPath 식을 사용하여 결과 시퀀스를 표현할 수 있는 경우 FLWOR 식을 사용하여 결과 시퀀스를 정의하면 성능이 저하됩니다. 이는 쿼리 계획에서 for 절의 본문과 for 변수 사이에 JOIN 작업이 포함되어 있기 때문입니다. 다음 조건 중 하나 이상을 충족할 경우에만 FLWOR 식을 사용하기에 적합합니다.

식 결과로 반환되는 값 시퀀스를 반복하려는 경우. 결과 집합의 연속된 값에 변수를 바인딩하는 for 절을 사용하여 이 작업을 수행합니다. for 절의 범위 내에서 새 요소를 생성하고 중복 요소를 유지하는 것을 예로 들 수 있습니다.
간단한 XPath 식을 사용하여 정의할 수 없는 조건자에 기초하여 for 절의 결과 집합을 필터링하려는 경우. 결과 집합에서 원치 않는 값을 제거하기 위해 where 절이 사용됩니다. 예를 들면 다음과 같습니다.

DECLARE @Result xml
SET @Result = ''
SELECT @Result.query('
for $i in (1, 2, 3), $j in (3, 4, 5)
where $i < $j
return sum($i + $j)
') as Result

정렬 식에 기초하여 결과 집합을 정렬하려는 경우. order by 절을 사용하여 결과 집합에서 정렬을 정의합니다.
for 절에서 얻은 결과를 사용하여 반환된 결과 집합의 형태를 정의하려는 경우. 결과 집합의 형태 정의를 수행하기 위해 return 문이 사용됩니다.
다른 모든 경우에는 XPath 식을 사용하는 것이 좋습니다.

XQuery의 연산자

기능적 언어인 SQL Server 2005의 XQuery는 다음 범주로 그룹화할 수 있는 다양한 유형의 함수와 연산자를 지원합니다.

산술 연산자

비교 연산자

논리 연산자

표 3 SQL Server 2005에서 지원되는 연산자

유형	연산자
산술 연산자	+,-,*,div, mod
일반 비교 연산자	=, !=, <, >, <=, >=
값 비교 연산자	eq, ne, lt, gt, le, ge
노드 비교 연산자	is
노드 순서 비교 연산자	>>, <<
논리 연산자	and, or

산술 연산자

SQL Server 2005에서는 5개의 산술 연산자인 +, b, *, div 및 mod가 지원됩니다. 현재 idiv는 지원되지 않습니다.

예제: 상점 조사 정보의 선택된 값 변환

다음 쿼리는 AdventureWorks 데이터베이스의 [Sales].[Store] 테이블에 기초합니다. 이 쿼리는 CustomerID가 3인 상점의 AnnualSales 및 AnnualRevenue 값을 엔화로 반환하고 상점 면적을 평방 미터로 반환합니다.

SELECT Demographics.query('
declare namespace ST="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
for $S in /ST:StoreSurvey
return
SalesInYen ="{ $S/ST:AnnualSales*106.8100 }"
RevenueInYen = "{ $S/ST:AnnualRevenue*106.8100 }"
StoreAreaInSqMeters = "{ $S/ST:SquareFeet*0.0929 }">

') as Result
FROM [Sales].[Store]
WHERE CustomerID = 3

비교 연산자

SQL Server 2005에서는 네 가지 유형의 비교 연산자가 지원됩니다. 이러한 연산자는 일반 비교 연산자, 값 비교 연산자, 노드 비교 연산자 및 노드 순서 비교 연산자입니다.

일반 비교 연산자

일반 비교 연산자를 사용하면 원자 값 또는 시퀀스나 이러한 두 값의 조합을 비교할 수 있습니다. 일반 비교 연산자는 =, !=, <, >, <=, 및 >=입니다. 일반 비교는 존재 여부를 수량화되는데 이는 일치하는 항목이 있을 경우 true가 반환된다는 것을 의미합니다.

예제: 주소 형식이 Home으로 설정되지 않은 모든 주소 요소 선택

다음 쿼리는 주소 형식이 Home으로 설정되지 않았으며 JobCandidateID가 3인 모든 Address 노드를 선택합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $A in /RES:Resume/RES:Address
where $A/RES:Addr.Type[.!="Home"]
return
$A
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

값 비교 연산자

값 비교 연산자를 사용하면 원자 값을 비교할 수 있습니다. SQL Server 2005에서 지원되는 값 비교 연산자는 eq, ne, lt, gt, le, 및 ge입니다. 현재의 XQuery 구현은 형식 없는 원자 값 승격과 관련하여 2004년 7월 발표된 XQuery 사양 초안을 따릅니다. 형식 없는 원자 형식은 XQuery 사양에 지정된 대로 xs:string 대신에 다른 피연산자 형식으로 승격됩니다. 이는 값 비교를 전이적인 것으로 만드는 것보다 일반 및 값 비교 연산자 사이에서 일관성을 유지하는 것이 더 중요하다고 간주되기 때문입니다.

예제: GPA가 3.5보다 큰 모든 education 요소 선택

다음 쿼리는 JobCandidateID가 2인 후보에 대해 GPA가 3.5보다 큰 모든 Education 노드를 선택합니다.

노드 비교 연산자

노드 비교 연산자 is를 사용하여 두 개의 노드를 비교하면 해당 노드가 동일한 노드인지 여부를 확인할 수 있습니다. 노드 비교 연산자에는 노드 형식의 피연산자 두 개가 허용됩니다.

예제: 두 개의 주소 노드를 비교하여 ID 검사

다음 쿼리는 두 개의 address 노드를 비교하여 문서의 동일한 노드를 나타내는지 여부를 검사합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
if ( (/RES:Resume/RES:Address)[1] is (//RES:Address)[1] )
then
Nodes are equal
else
Nodes are not equal
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

노드 순서 비교 연산자

노드 순서 비교 연산자를 사용하여 문서에 있는 두 노드의 순서를 확인할 수 있습니다. SQL Sever 2005에서 지원되는 노드 순서 비교 연산자는 >> 및 << 이며 두 연산자는 모두 두 개의 피연산자를 허용합니다. 문서 순서에서 왼쪽 피연산자가 오른쪽 피연산자보다 앞에 올 경우 >> 연산자는 true를 반환하고 문서 순서에서 왼쪽 피연산자가 오른쪽 피연산자 뒤에 올 경우<< 연산자는 true를 반환합니다.

예제: 두 주소 노드의 순서 비교

다음 쿼리는 JobCandidateID가 3인 후보에 대해 두 address 노드의 순서를 비교합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
if ( (/RES:Resume/RES:Address/RES:Addr.Type[.="Home"])[1] << (/RES:Resume/RES:Address/RES:Addr.Type[.="Permanent"])[1] )
then
Home address precedes Permanent address
else
Home address follows Permanent address
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

논리 연산자

SQL Server 2005의 XQuery에서 지원되는 논리 연산자는 and 및 or입니다. 이러한 연산자를 사용하여 작성한 논리 식의 값은 true 또는 false가 될 수 있습니다.

예제: and 연산자를 사용하여 논리 식 만들기

다음 쿼리는 bachelor level business degree를 포함하는 후보의 education 요소를 반환합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/RES:Resume/RES:Education[RES:Edu.Level="Bachelor" and RES:Edu.Major="Business"]
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

if-then-else 구문

다른 기능적 언어와 마찬가지로 XQuery는 if-then-else 구문을 지원합니다. if-then-else 문을 사용하여 조건식 값에 기초한 작업을 수행할 수 있습니다.

예제: 조건식 사용

다음 쿼리는 JobCandidateID가 3인 후보의 이력서에 지정된 주소 유형을 표시합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $A in /RES:Resume/RES:Address
return
if ( $A/RES:Addr.Type eq "Home" )
then
Home Address
else
Other Address
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

XQuery를 사용하여 XML 생성

XQuery 생성자를 사용하면 쿼리 내에서 XML 구조를 만들 수 있습니다. 요소, 특성, 처리 명령, 텍스트 노드 및 주석에 생성자를 사용할 수 있습니다.

다음 예제는 XML을 생성하는 이러한 접근 방법을 보여 줍니다.

예제: 상수 식 사용

다음 쿼리는 상수 식을 사용하여 생성된 고용 기록 세부 정보를 표시합니다.

SELECT Resume.query('

ABC Technologies
Software Engineer
2001-10-01
2003-05-09

') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: 동적으로 얻은 데이터 사용

다음 쿼리는 JobCandidateID가 3인 후보에 대한 쿼리에서 얻은 결과를 사용하여 생성된 고용 기록 세부 정보를 표시합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $EMP in /RES:Resume/RES:Employment
return

{ $EMP/RES:Emp.StartDate }
{ $EMP/RES:Emp.EndDate }
{ $EMP/RES:Emp.JobTitle }

') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: 계산된 요소 및 특성 생성자에 상수 이름 사용

다음 쿼리는 JobCandidateID가 3인 후보의 고용 기록을 표시합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $EMP in /RES:Resume/RES:Employment
return
element Employer
{
attribute Organization { $EMP/RES:Emp.OrgName },
element StartDate { string($EMP/RES:Emp.StartDate) },
element EndDate { string($EMP/RES:Emp.EndDate) },
element JobTitle { string($EMP/RES:Emp.JobTitle) }
}
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

XQuery 생성 수행 및 FOR XML을 사용한 형태 정의 비교

일부 응용 프로그램에서는 행 집합에서 XML을 생성하는 것이 요구됩니다. 서버에서는 FOR XML 절이나 XQuery 생성 또는 XML DML 작업을 사용하여 XML을 생성할 수 있습니다. FOR XML 및 XQuery 생성자를 사용하여 XML을 생성할 경우의 권장 사항은 다음과 같습니다.

여러 열과 여러 행에서 XML을 집계하려는 경우 FOR XML만 사용할 수 있습니다.
단일 XML 인스턴스의 형태를 변경하려는 경우 FOR XML뿐만 아니라 XQuery를 사용할 수 있습니다. FOR XML의 경우 XML 인스턴스에서 XML 데이터 형식 메서드를 여러 번 호출해야 하므로 XQuery가 더 빠를 수 있습니다.
여러 XML DML 문을 사용하여 XML 인스턴스를 생성할 수 있습니다. 이 방법은 XQuery 생성보다 훨씬 느립니다.
FOR XML 쿼리의 결과를 XML 데이터 형식의 인스턴스로 생성하려면 SQL Server 2005의 FOR XML 절에서 사용할 수 있는 새 TYPE 지시문을 사용합니다.

기본 제공 XQuery 함수

SQL Server 2005의 XQuery 구현에서는 XQuery 1.0 및 XPath 2.0 기본 제공 함수의 하위 집합이 지원됩니다. 이러한 함수에는 데이터 접근자 함수, 문자열 조작 함수, 집계 함수, 컨텍스트 함수, 숫자 함수, 부울 함수, 노드 함수, 시퀀스 함수 등이 있습니다. 다음 절에서는 이러한 함수 중 몇 가지에 대해 설명합니다.

데이터 접근자

데이터 접근자 함수를 사용하여 노드의 값을 문자열 또는 형식 있는 값으로 추출할 수 있습니다. XQuery는 두 가지 유형의 데이터 접근자 함수를 지원합니다. 그 중 하나인 string()은 항목의 문자열 값을 추출하며 다른 하나인 data()는 형식 있는 값을 가져옵니다. 노드가 텍스트 노드, 특성 노드 또는 요소 노드가 아닌 경우 data() 함수는 정적 오류를 발생시킵니다. 노드가 형식 없는 XML 인스턴스의 문서 노드인 경우 data()는 문서의 문자열 값을 반환합니다. 노드가 복합 형식 요소인 경우 data() 함수는 정적 오류를 반환합니다.

예제: string() 함수 사용

string() 함수 및 계산된 요소 생성자를 사용하여 후보의 고용 기록을 생성하는 쿼리는 이 문서의 "XQuery를 사용하여 XML 생성" 절에서 예제: 계산된 요소 및 특성 생성자에 상수 이름 사용 예제를 참조하십시오.

예제: data() 함수 사용

다음 쿼리는 data() 함수 및 계산된 요소 생성자를 사용하여 후보의 고용 기록을 생성합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
where xs:decimal( data($ED/RES:Edu.GPA) ) gt 3.5
return
element Education
{
element Level { data($ED/RES:Edu.Level) },
element Degree { data($ED/RES:Edu.Degree) },
element GPA { data($ED/RES:Edu.GPA) },
element GPAScale { data($ED/RES:Edu.GPAScale) }
}
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

문자열 조작

XQuery는 다음 네 개의 문자열 조작 함수를 지원합니다.

concat()를 사용하여 두 개 이상의 문자열을 연결할 수 있습니다.
contains()를 사용하면 첫 번째 피연산자로 지정된 문자열이 두 번째 피연산자로 지정된 다른 문자열을 포함하는지 여부를 결정할 수 있습니다. 검색 문자열의 길이는 4,000개의 유니코드 문자로 제한됩니다.
substring()을 사용하면 소스 문자열로 알려진 다른 문자열에서 문자열의 일부를 추출할 수 있습니다.
string-length()를 사용하면 문자열 길이를 계산할 수 있습니다.
현재 릴리스의 SQL Server 2005에서는 유니코드 코드 포인트 데이터 정렬만 지원됩니다.

예제: concat() 및 substring() 함수 사용/p>

다음 쿼리는 start date, end date, organization name 및 job title의 값을 연결하여 후보의 고용 기록을 생성합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $EMP in /RES:Resume/RES:Employment
return

{
concat( substring(string($EMP/RES:Emp.StartDate),1,10)," to ",
substring(string($EMP/RES:Emp.EndDate),1,10), ", ",
string($EMP/RES:Emp.OrgName), ", ",
string($EMP/RES:Emp.JobTitle) )
}

') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: contains() 함수 사용

다음 쿼리는 Edu.Degree 요소의 값에 science 문자열을 포함하는 노드에 대한 Education 세부 정보를 표시하여 contains() 함수가 사용되는 방법을 보여 줍니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
where contains($ED/RES:Edu.Degree, "Science")
return
element Education
{
element Level { data($ED/RES:Edu.Level) },
element Degree { data($ED/RES:Edu.Degree) },
element GPA { data($ED/RES:Edu.GPA) },
element GPAScale { data($ED/RES:Edu.GPAScale) }
}
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

집계 함수

집계 함수는 항목 시퀀스에서 작동하여 시퀀스의 집계 값을 반환합니다. 현재 SQL Server 2005의 XQuery에서 지원되는 집계 함수는 count(), min(), max(), avg() 및 sum()입니다. min() 및 max() 함수에는 gt 연산자를 지원하는 기본 형식만 허용됩니다(예: 세 가지 기본 제공 숫자 기본 형식, 날짜/시간 기본 형식, xs:string, xs:boolean 및 xdt:untypedAtomic). 혼합된 형식의 시퀀스는 이러한 함수에서 지원되지 않습니다. 또한 xdt:untypedAtomic은 xs:double로 간주됩니다.

avg() 및 sum()의 경우 전달된 식의 형식은 기본 제공되는 세 가지 숫자 기본 형식 중 하나 또는 untypedAtomic의 하위 형식이어야 합니다(혼합 형식은 허용되지 않으며 xdt:untypedAtomic은 xs:double로 간주됨).

count() 함수는 시퀀스의 항목 수를 반환합니다.

예제: count() 함수 사용

다음 쿼리는 count() 함수를 사용하여 문서에 존재하는 employment, education 및 address 요소의 수를 표시합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
Element count is { count(/RES:Resume/RES:Address) },
Element count is { count(/RES:Resume/RES:Education) },
Element count is { count(/RES:Resume/RES:Address) }
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: min() 함수 사용

쿼리는 min() 함수를 사용하여 GPA 값아 최소인 education 요소를 표시합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
where $ED/RES:Edu.GPA = min(/RES:Resume/RES:Education/RES:Edu.GPA)
return
$ED
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

예제: max() 함수 사용

다음 쿼리는 max() 함수를 사용하여 GPA 값이 최대인 education 요소를 표시합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
where $ED/RES:Edu.GPA = max(/RES:Resume/RES:Education/RES:Edu.GPA)
return
$ED
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

예제: avg() 함수 사용

다음 쿼리는 avg() 함수를 사용하여 일주일 동안의 New York과 Boston의 최고 및 최저 온도 평균을 계산합니다.

DECLARE @Weather xml
SET @Weather = '

'

SELECT @Weather.query('

{ avg(/WeatherInfo/NewYork/Temp/@High) }
{ avg(/WeatherInfo/NewYork/Temp/@Low) }

{ avg(/WeatherInfo/Boston/Temp/@High) }
{ avg(/WeatherInfo/Boston/Temp/@Low) }

') as Result

예제: sum() 함수 사용

다음 쿼리는 sum() 및 count() 함수를 사용하여 일주일 동안의 New York과 Boston의 최고 및 최저 온도 평균을 계산합니다.

DECLARE @Weather xml
SET @Weather = '

'

SELECT @Weather.query('

{ sum(/WeatherInfo/NewYork/Temp/@High) div count(/WeatherInfo/NewYork/Temp/@High) }
{ sum(/WeatherInfo/NewYork/Temp/@Low) div count(/WeatherInfo/NewYork/Temp/@Low) }

{ sum(/WeatherInfo/Boston/Temp/@High) div count(/WeatherInfo/Boston/Temp/@High) }
{ sum(/WeatherInfo/Boston/Temp/@Low) div count(/WeatherInfo/Boston/Temp/@Low) }

') as Result

컨텍스트 함수

컨텍스트 함수를 사용하여 컨텍스트 항목의 컨텍스트 속성을 얻을 수 있습니다. SQL Server 2005는 두 개의 컨텍스트 함수인 last() 및 position()을 구현합니다. last() 함수를 사용하면 시퀀스의 항목 수를 확인할 수 있으며 position() 함수를 사용하면 컨텍스트 항목의 위치를 얻을 수 있습니다. 인수가 없는 last() 및 position() 함수는 SQL Server 2005에서 컨텍스트에 무관한 조건자의 컨텍스트(예: [] 안쪽)에서만 사용할 수 있습니다.

예제: last() 함수 사용

다음 쿼리는 last() 함수를 사용하여 후보의 마지막 address 요소를 검색합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/RES:Resume/RES:Address[last()]
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: position() 함수 사용

다음 쿼리는 position() 함수를 사용하여 후보의 처음 두 개 address 요소를 검색합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/RES:Resume/RES:Address[position()<=2]
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

형식 관련 식

XQuery는 형식 정보에 기초하는 다양한 유형의 식이나 연산자를 지원합니다. 이러한 식은 형식 어설션 식, 형식 조사 식 및 형식 캐스팅 식으로 분류됩니다. 다음 절에서는 이러한 식에 대해 간략하게 설명합니다.

형식 어설션 식

as xs:for 문의TYPE 절

as 절을 사용하여 for 문에 사용되는 바인딩 변수의 형식을 지정할 수 있습니다.

바인딩 변수에 대한 형식이 선언된 경우 선언된 형식이 아닌 바인딩 값은 형식 오류를 야기합니다. xs:TYPE 절은 캐스트 식이 아니지만 형식 어설션으로 사용됩니다.

예제: for 문과 함께 "as xs:TYPE" 절 사용

다음 쿼리는 형식 요소(RES:Address)로 정의되는 변수 $A에 주소 노드 시퀀스를 바인딩합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $A as element(RES:Address) in /RES:Resume/RES:Address
return
$A
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

형식 조사 식

instance of xs:TYPE 연산자

instance of 연산자를 사용하면 XML 문서에 있는 항목의 런타임 형식을 식별할 수 있습니다.

예제: "instance of xs:TYPE" 연산자 사용

다음 쿼리는 XPath 식으로 식별된 주소 노드의 형식이 element() 형식과 일치하는지 여부를 검사합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
if ( (/RES:Resume/RES:Address)[1] instance of element() )
then
Selected node is an Element
else
Selected node is not an Element
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

형식 캐스팅 식

암시적 형식 캐스팅

XQuery 엔진은 산술 연산 또는 함수 호출을 포함하는 식의 숫자 형식 및 untypedAtomic 값에 대한 암시적 형식 캐스팅을 수행합니다. 이 프로세스를 형식 승격이라고 합니다. 예상 숫자 형식과 호환되지 않은 숫자 형식이 식 결과로 얻어질 경우 형식 승격이 발생합니다. 결과 식을 필요한 형식으로 캐스팅하는 방법으로 형식 승격이 수행됩니다.

예제: 암시적 형식 캐스팅

다음 쿼리는 10진수 값과 double 값에서 산술 연산을 수행합니다. 현재 시나리오에서는 xs:decimal 값을 xs:double로 승격한 후에만 식 값이 추가됩니다.

DECLARE @Result xml
SET @Result = ''
SELECT @Result.query('
{ xs:decimal("10.55") + xs:double(1.5e1) }
') as Result

명시적 형식 캐스팅
형식 있는 값 생성자

XQuery는 XML 스키마 사양에 정의된 모든 기본 제공 형식에 대한 생성자 함수를 제공합니다. 이러한 생성자는 형식 있는 값을 생성하는 경우와 특정 형식의 값을 다른 형식으로 캐스팅하는 경우에 유용하게 사용됩니다. 또한 XQuery에서는 가져온 스키마에 정의된 형식에 대해 생성자를 사용할 수 있습니다.

예제: 값 생성자를 사용하여 값 생성

다음 쿼리는 xs:date 형식에 대한 생성자를 사용하여 생성한 값보다 큰 StartDate를 가지는 모든 Employment 노드를 반환합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $EMP in /RES:Resume/RES:Employment
where $EMP/RES:Emp.StartDate gt xs:date("1995-01-01")
return
$EMP
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3

예제: 값 생성자를 사용하여 형식 캐스팅

다음 쿼리는 JobCandidateID가 2인 후보에 대해 GPA가 3.8보다 크거나 같은 모든 Education 노드를 선택합니다. 이 쿼리는 Edu.GPA 값을 xs:string에서 xs:decimal로 형식 캐스팅하기 위해 xs:decimal에 대한 값 생성자를 사용합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
where xs:decimal( data($ED/RES:Edu.GPA) ) ge 3.8
return
element Education
{
element Level { string($ED/RES:Edu.Level)},
element StartDate { string($ED/RES:Edu.StartDate)},
element EndDate { string($ED/RES:Edu.EndDate)},
element Degree { string($ED/RES:Edu.Degree)},
element GPA { string($ED/RES:Edu.GPA)},
element GPAScale { string($ED/RES:Edu.GPAScale)}
}
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

cast as xs:TYPE ? 연산자

SQL Server 2005의 XQuery는 명시적 형식 캐스팅을 수행하는 데 유용한 cast as TYPE ? 연산자를 지원합니다. 또한 cast as TYPE ? 연산자보다 편리하게 작성할 수 있는 xs:TYPE() 생성자를 사용하여 명시적 형식 캐스팅을 수행할 수도 있습니다.

예제: "cast as xs:TYPE ?" 연산자 사용

다음 쿼리는 JobCandidateID가 3인 후보에 대해 Education 노드 집합에서 선택한 요소의 형식 있는 값을 포함하는 XML을 생성합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
return
element Education
{
element Level { $ED/RES:Edu.Level cast as xs:string? },
element StartDate { $ED/RES:Edu.StartDate cast as xs:date? },
element EndDate { $ED/RES:Edu.EndDate cast as xs:date? },
element Degree { $ED/RES:Edu.Degree cast as xs:string? },
element GPA { $ED/RES:Edu.GPA cast as xs:decimal? },
element GPAScale { $ED/RES:Edu.GPAScale cast as xs:decimal? }
}
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

예제: "xs:TYPE()" 연산자 사용

다음 쿼리는 cast as xs:TYPE ? 연산자 대신에 xs:TYPE() 연산자를 사용하여 앞의 예제 쿼리와 동일한 결과를 생성합니다.

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ED in /RES:Resume/RES:Education
return
element Education
{
element Level { xs:string($ED/RES:Edu.Level) },
element StartDate { xs:date($ED/RES:Edu.StartDate) },
element EndDate { xs:date($ED/RES:Edu.EndDate) },
element Degree { xs:string($ED/RES:Edu.Degree) },
element GPA { xs:decimal($ED/RES:Edu.GPA) },
element GPAScale { xs:decimal($ED/RES:Edu.GPAScale) }
}
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = 2

관계형 열과 변수 액세스

XQuery를 사용하여 쿼리를 작성할 경우 쿼리에서 관계형 열과 변수에 액세스하는 것이 일반적으로 요구됩니다. SQL Server 2005는 두 개의 함수인 sql:column() 및 sql:variable()을 구현하여 이 요구 사항을 충족합니다.

sql:column() 함수를 사용하면 쿼리에서 관계형 테이블에 있는 비 XML 열에 액세스할 수 있습니다. 이 함수는 하나 이상의 테이블에 있는 XML 및 비 XML 열에서 정보를 집계하거나 비 XML 열의 값을 사용하여 XQuery 결과를 필터링하는 등의 상황에 유용합니다. sql:column() 및 sql:variable() 함수는 datetime, CLR 사용자 정의 함수 또는 XML과 함께 사용할 수 없습니다.

예제: sql:column() 함수 사용

다음 쿼리는 비 XML 데이터 형식의 CustomerID 및 Name 열에 있는 값과 Demographics 열에 있는 YearOpened, NumberOfEmployees, AnnualSales 및 AnnualRevenue 요소의 값을 포함하는 XML을 생성합니다.

SELECT Demographics.query('
declare namespace ST="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
element CustomerInfo
{
element CustomerID { sql:column("Store.CustomerID") },
element Name { sql:column("Store.Name") },
element YearOpened { string((/ST:StoreSurvey/ST:YearOpened)[1]) },
element NumberOfEmployees { string((/ST:StoreSurvey/ST:NumberEmployees)[1]) },
element AnnualSales { string((/ST:StoreSurvey/ST:AnnualSales)[1]) },
element AnnualRevenue { string((/ST:StoreSurvey/ST:AnnualRevenue)[1]) }
}
') as Result
FROM [Sales].[Store] Store
WHERE Store.CustomerID = 4

예제: sql:variable() 함수 사용

다음 저장 프로시저는 XQuery where 절의 @AddrType 변수 값을 사용하여 쿼리 결과를 필터링함으로써 지정된 후보의 home address 노드를 반환합니다.

CREATE PROCEDURE [GetCandidateAddress]
@JobCandidateID [int],
@AddrType [varchar](20)
AS
BEGIN
SET NOCOUNT ON;

SELECT Resume.query('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $A in /RES:Resume/RES:Address
where $A[ RES:Addr.Type = sql:variable("@AddrType") ]
return
$A
') as Result
FROM [HumanResources].[JobCandidate]
WHERE JobCandidateID = @JobCandidateID

END

예제: XML 데이터 형식의 exist() 메서드와 함께 sql:variable() 함수 사용

다음 쿼리는 경영학 학사 학위를 가진 후보의 이력서를 반환합니다.

DECLARE @EducationLevel varchar(20)
SET @EducationLevel = 'Bachelor'

DECLARE @Major varchar(20)
SET @Major = 'Business'

SELECT JobCandidateID, Resume
FROM [HumanResources].[JobCandidate]
WHERE Resume.exist ('
declare namespace RES="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/RES:Resume/RES:Education[ RES:Edu.Level = sql:variable("@EducationLevel") and RES:Edu.Major = sql:variable("@Major") ]') = 1

지원되지 않는 기능과 해결 방법

현재 SQL Server 2005에서 구현된 XQuery는 다음 기능을 지원하지 않습니다.

* let 절: FLWOR 식의 일부인 let 절은 변수를 식 결과에 바인딩하는 데 유용합니다. 해결 방법 - let 절을 사용하는 대신에 인라인 식을 사용합니다.

* 범위 식(to 연산자): 범위 식을 사용하면 to 연산자를 사용하여 연속된 정수 시퀀스를 생성할 수 있습니다. 예를 들어, (6 to 10)와 같은 범위 식을 사용하면 (6, 7, 8, 9, 10) 시퀀스를 생성할 수 있습니다. 해결 방법 - to 연산자를 사용하는 대신에 시퀀스의 모든 항목을 나열합니다.

* 형식 정보: typeswitch, treat as, castable 및 validate 식과 같은 형식 시스템에 기반을 두는 일부 기능은 현재 지원되지 않습니다.

typeswitch 식 기능은 다른 프로그래밍 언어에서 사용할 수 있는 switch-case 구문과 비슷합니다. switch-case 문에서 branch/case는 스위치에 전달된 인수 값에 따라 선택됩니다. typeswitch 식에서 branch는 typeswitch에 전달된 인수 유형에 따라 선택됩니다. 해결 방법 ? if then else 및 instance of를 사용합니다.
treat as 식을 사용하면 식 결과의 정적 형식을 특정 정적 형식으로 변경할 수 있으며 식의 정적 형식이 지정된 형식과 일치하지 않을 경우 정적 형식 오류가 발생합니다. 식의 동적 형식이나 값은 변경되지 않습니다. 해결 방법 ? 없음
castable 식은 원자 값을 지정된 형식으로 캐스팅할 수 있는지 여부를 검사하는 데 유용합니다. 해결 방법 - "$x castable as T?" 식을 사용하는 대신에 "empty(data($x)) or not(empty(T($x)))" 식을 사용합니다.
validate 식은 현재 범위에 존재하는 스키마 정의에 기초하여 인수에 대한 유효성 검사를 수행합니다. 해결 방법 - validate 식을 사용하는 대신에 요청된 스키마 컬렉션에 대한 Transact-SQL 캐스팅을 사용합니다.

* 재사용성: 다른 프로그래밍 언어에서와 마찬가지로 유용하고 복잡한 쿼리를 포함하는 사용자 정의 함수로 알려진 재사용 가능한 함수를 작성할 수 있습니다. 또한 사용자 정의 함수 컬렉션을 모듈로 패키지화할 수 있습니다. 쿼리는 모듈을 가져와 모듈에서 제공되는 함수를 사용할 수 있습니다. 쿼리의 프롤로그 섹션에 포함하여 모듈을 쿼리로 가져올 수 있습니다. 현재 SQL Server 2005에서는 해결 방법이 없습니다.

* 기본 제공 함수: 현재 SQL Server 2005에서는 다음 기본 제공 함수가 지원되지 않습니다. 이러한 함수에 대한 자세한 내용은 W3C.org 웹 사이트에서 XQuery 1.0 및 XPath 2.0 함수 및 연산자 (영문)를 참조하십시오.

접근자: fn:node-name(), fn:nilled(), fn:base-uri(), fn:document-uri().
오류 함수: fn:error().
추적 함수: fn:trace().
숫자 값에 대한 함수: abs(), round-half-to-even().
문자열 처리 함수: codepoints-to-string(), string-to-codepoints(), compare(), string-join(), normalize-space(), normalize-unicode(), upper-case(), lower-case(), translate(), escape-uri(), starts-with(), ends-with(), substring-before(), substring-after(), matches(), replace(), tokenize().
anyURI에 대한 함수 및 연산자: resolve-uri().
기간, 날짜 및 시간에 대한 함수 및 연산자: years-from-duration(), months-from-duration(), days-from-duration(), hours-from-duration(), minutes-from-duration(), seconds-from-duration(), year-from-dateTime(), month-from-dateTime(), month-from-dateTime(), day-from-dateTime(), hours-from-dateTime(), minutes-from-dateTime(), seconds-from-dateTime(), timezone-from-dateTime(), year-from-date(), month-from-date(), day-from-date(), timezone-from-date(), hours-from-time(), minutes-from-time(), seconds-from-time(), timezone-from-time(), adjust-dateTime-to-timezone(), adjust-date-to-timezone(), adjust-time-to-timezone(), subtract-dateTimes-yielding-yearMonthDuration(), subtract-dateTimes-yielding-dayTimeDuration(), subtract-dates-yielding-yearMonthDuration(), subtract-dates-yielding-dayTimeDuration(). 또한 xdt:dayTimeDuration 및 xdt:yearMonthDuration 형식도 지원되지 않습니다.
Qnames와 관련된 함수: resolve-QName(), QName(), namespace-uri-for-prefix(), in-scope-prefixes().
노드에 대한 함수: name(), lang(), root(). 해결 방법 - root() 대신 /을 사용합니다.
시퀀스에 대한 함수 및 연산자: fn:boolean(), fn:index-of(), fn:exists(), insert-before(), remove(), reverse(), subsequence(), unordered(), zero-or-one(), one-or-more(), exactly-one(), deep-equal(), 두 개의 인수를 가지는 id(), idref(), doc(), collection() 함수 및 union, intersect 및 except 연산자. 해결 방법 ? fn:boolean() 대신 not(not())을, fn:exists() 대신 not(empty())을, zero-or-one() 또는 exactly-one() 대신 명시적 for iteration 또는 [1]을 사용합니다.
컨텍스트 함수: current-dateTime(), current-date(), current-time(), default-collation(), implicit-timezone().

*위치 변수: at 절을 사용하여 FLWOR 문의 일부로 위치 변수를 정의할 수 있습니다. 위치 변수는 식 결과에서 항목 위치를 식별하는 데 유용합니다.

*순서 한정자: order by 절을 사용하여 지정된 순서 한정자 "empty greatest | least"는 지원되지 않습니다. ? 기타 기능: 다음 기능은 지원되지 않습니다.

*기타 기능: The following features are not supported:

idiv 연산자.
명시적 스키마 가져오기는 지원되지 않습니다.
외부 변수는 지원되지 않습니다.
경계 공백 유지 옵션은 지원되지 않습니다.
노드 및 값과 같은 유형이 다른 시퀀스를 연결하는 기능은 지원되지 않습니다.
표준 시간대 유지는 지원되지 않습니다.
단순 형식 콘텐츠를 가진 요소의 텍스트 노드에 액세스하는 기능은 지원되지 않습니다.
형식 있는 XML 데이터 형식 인스턴스의 xsi:* 특성에 액세스하는 기능은 지원되지 않습니다.

최상의 실행 방법 및 지침

형식 정보가 있는 경우 활용합니다. 형식 정보를 활용하면 성능이 향상되며 정적 형식 검사를 수행하여 오류를 발견할 수 있습니다.

XML 데이터 형식에서 일부 속성 값을 추출하여 관계형 쿼리에서 사용하려면 속성 승격을 위해 XQuery를 사용합니다. 성능 향상을 위해 자주 사용되는 속성이 관계형 열로 승격되고 인덱싱됩니다.

카디널리티 불일치로 인한 정적 오류를 방지하기 위해 서수(예: [1]) 또는 명시적 FLWOR을 사용합니다.

정적 형식 오류를 방지하기 위해 명시적 캐스트를 사용합니다.

인덱스 사용: Xpath 식을 많이 사용하는 쿼리가 있을 경우 PATH 인덱스를 만듭니다. 부정확하게 알려진 경로(예: /a/* 또는 //b)를 가진 요소 또는 특성 값을 검색하는 작업이 포함된 Xpath 식이 XQuey 쿼리에 있는 경우 VALUE 인덱스를 사용합니다. XML 인스턴스에서 알려진 속성의 모든 경우를 쿼리에서 검색하는 경우에 PROPERTY 인덱스가 유용합니다.

참조된 대부분의 형식이 단일 네임스페이스의 일부이면 기본 네임스페이스를 사용하여 그렇지 않으면 접두사를 사용합니다.

최상의 실행 방법에 대한 자세한 내용은 MSDN 기사 Microsoft SQL Server 2005를 위한 XML 최상의 실행 방법 및 XML 데이터 형식을 위한 성능 최적화를 참조하십시오.

XML 데이터 수정

SQL Server 2005는 데이터베이스에 저장된 XML 인스턴스를 수정하는 것을 지원합니다. 최신 버전의 W3C XQuery 초안에는 XML 문서를 수정하기 위한 구문이 정의되어 있지 않습니다. XML 문서를 수정하기 위한 메커니즘을 제공하고 위해 Microsoft는 XML DML(Data Modification Language)을 개발했습니다. XML 데이터 형식의 modify 메서드를 사용하고 XML DML 문으로 수정을 지정하여 XML 문서를 수정할 수 있습니다.

XML DML은 insert, delete 및 replace value of 키워드를 사용하여 XML 문서에서 삽입, 삭제 및 업데이트 작업을 지원합니다. 형식 있는 XML 인스턴스의 수정은 XML 데이터 형식에 정의된 스키마 제약 조건에 기반을 두는 유효성 검사를 따릅니다.

다음 테이블과 XML 인스턴스는 XML DML 작업을 보여 줍니다.

테이블:

CREATE TABLE [CandidateInfoXMLDataType]
(
[JobCandidateID] [int] IDENTITY(1,1) NOT NULL,
[Resume] [xml] NOT NULL,
[ModifiedDate] [datetime] NOT NULL DEFAULT (getdate())
)

샘플 XML 인스턴스:

Mike

Chen

34 181st Place SE
Apt 3344
Redmond
WA
US
9870909023

BS
MS

ASP.NET
SQL

ABC Technologies
NY, US
20/02/2000
10/04/2004
Project Leader
Responsible for design,development,testing activities

삽입 작업

insert 키워드를 사용하여 하나 이상의 노드를 XML 문서에 삽입할 수 있습니다. insert 키워드에는 삽입할 노드를 식별하는 XQuery 식과 참조 노드를 지정하는 다른 XQuery 식이 허용됩니다.

또한 참조 노드와 관련하여 새 노드를 위치를 지정하기 위해 into, after 및 before와 같은 키워드를 포함할 수 있습니다. into 키워드를 지정할 경우 새 노드가 참조 노드의 자식으로 삽입됩니다. into 키워드를 포함할 경우 참조 노드의 기존 자식 노드와 관련하여 삽입된 노드의 위치를 나타내는 as first 또는 as last 키워드도 지정해야 합니다. 또한 after 또는 before 키워드를 지정하여 참조 노드 뒤쪽이나 앞쪽에 새 노드를 형제 노드로 삽입할 수 있습니다.

대상 식(Expression2)이 단일 노드를 정적으로 식별하지 않은 경우 삽입 작업이 실패하고 정적 오류가 발생합니다.

구문:

insert
Expression1 (
{{{as first | as last} into} | after | before}
Expression2 )

예제: 기술 삽입

다음 저장 프로시저를 사용하면 지정된 후보에 대한 새 기술을 삽입할 수 있습니다. 이 저장 프로시저는 sql:variable() 함수를 사용하여 XML DML 문 안에 있는 Transact-SQL 변수에 액세스합니다. XML 안에서 비 XML 관계형 데이터를 바인딩하는 방법에 대한 자세한 내용은 관계형 열과 변수 액세스를 참조하십시오.

다음 저장 프로시저는 사용자가 특정 기술의 문자열 값을 두 번째 인수로 저장 프로시저에 전달한다는 가정 하에 작성되었습니다. 하나 이상의 기술 요소를 포함하는 XML 단편을 허용하도록 이 저장 프로시저를 수정할 수 있습니다. 이렇게 하면 사용자는 한 번의 호출로 여러 기술 노드를 저장 프로시저에 삽입할 수 있습니다.

/* Stored procedure to insert a new skill element for a candidate */
CREATE PROCEDURE [InsertSkillInfo]
@JobCandidateID [int],
@Skill [varchar](200)
AS
BEGIN
SET NOCOUNT ON;

UPDATE [CandidateInfoXMLDataType]
SET Resume.modify('
insert element Skill {sql:variable("@Skill")}
as last
into (/JobCandidate/Skills)[1]
')
WHERE JobCandidateID = @JobCandidateID
END

삭제 작업

delete 키워드를 사용하면 XML 인스턴스에서 하나 이상의 노드를 삭제할 수 있습니다. delete 키워드에서는 XML 문서에 삭제할 하나 이상의 노드를 식별하는 XQuery 식이 허용됩니다.

구문:

delete Expression

예제: 기술 삭제

다음 예제는 delete 키워드를 사용하여 지정된 후보의 기술을 삭제하는 방법을 보여 줍니다.

다음 저장 프로시저는 사용자가 특정 기술의 문자열 값을 두 번째 인수로 저장 프로시저에 전달한다는 가정 하에 작성되었습니다. 하나 이상의 기술 요소를 포함하는 XML 단편을 허용하도록 이 저장 프로시저를 수정할 수 있습니다. 이렇게 하면 사용자는 저장 프로시저를 한 번 호출하여 여러 기술 노드를 삭제할 수 있습니다.

/* Stored procedure to delete a specified skill element for a candidate */
CREATE PROCEDURE [DeleteSkillInfo]
@JobCandidateID [int],
@Skill [varchar](200)
AS
BEGIN
SET NOCOUNT ON;

UPDATE [CandidateInfoXMLDataType]
SET Resume.modify('
delete (/JobCandidate/Skills/Skill[.=sql:variable("@Skill")])
')
WHERE JobCandidateID = @JobCandidateID
END

하나 이상의 기술 요소를 포함하는 XML 단편을 허용하도록 이 저장 프로시저를 쉽게 수정할 수 있습니다. 이 경우 사용자는 저장 프로시저를 한 번 호출하여 여러 기술 노드를 삭제할 수 있습니다.

업데이트 작업

replace value of 키워드를 사용하면 기존 노드의 값을 수정할 수 있습니다. 기존 노드의 값을 수정하려면 값을 업데이트할 노드를 식별하는 하나의 XQuery 식과 노드의 새 값을 지정하는 또 다른 식을 지정해야 합니다.

형식 없는 XML 인스턴스를 수정하는 동안 대상 식은 단순 형식 노드를 반환해야 합니다. 형식 있는 XML 인스턴스의 대상 식은 소스 식과 동일한 형식 또는 하위 형식으로 평가되어야 합니다.

구문:

replace value of
Expression1
with
Expression2

예제: 기술 업데이트

다음 예제는 replace value of 키워드를 사용하여 지정된 후보의 기존 기술 값을 업데이트하는 방법을 보여 줍니다.

/* Stored procedure to update a specified skill element for a candidate */
CREATE PROCEDURE [UpdateSkillInfo]
@JobCandidateID [int],
@SkillOld [varchar](200),
@SkillNew [varchar](200)
AS
BEGIN
SET NOCOUNT ON;

UPDATE [CandidateInfoXMLDataType]
SET Resume.modify('
replace value of (/JobCandidate/Skills/Skill[.=sql:variable("@SkillOld")]/text())[1]
with sql:variable("@SkillNew")
')
WHERE JobCandidateID = @JobCandidateID
END

delete 작업과 달리 update 및 insert 작업은 한 번의 작업으로 하나의 노드에만 영향을 줄 수 있습니다.

XQuery 사용 시나리오

시나리오 1: 성과 평가 시스템

회사의 인사부에는 일반적인 평가 관련 활동을 처리하는 성과 평가 시스템이 필요합니다. 일반적으로 평가 레코드에는 평가 기간의 주요 목표와 비교하여 측정된 직원 성과, 다음 평가 기간에 사용할 주요 목표 정의, 직원 교육 요구 식별 등과 같이 주로 설명적인 특성을 가지는 정보가 포함됩니다. XML은 이러한 정보를 처리하는 데 가장 적합합니다. 직원에 대한 평가 정보가 XML 형식 열에 저장될 수 있으며 시스템 사용자는 XQuery를 사용하여 직원의 성과 기록을 쿼리 및 분석할 수 있습니다. 또한 XML DML을 사용하여 평가 레코드를 수정할 수 있습니다.

회사의 교육 부서가 ASP.NET에 대한 교육 세션을 수행하고 있으며 이러한 교육을 요청했던 모든 직원을 초대하려고 한다고 가정해 보십시오. 다음 쿼리는 평가 프로세스 도중에 ASP.NET에 대한 교육 세션을 선택했던 모든 직원을 선택합니다.

SELECT Appraisal.query('
for $PA in /PerformanceAppraisal,
$Skill in $PA/TrainingNeeds/Technical/Skill
where contains($Skill, "ASP.NET")
return
element Employee
{
element EmpID { data($PA/Employee/EmployeeID) },
element EmpName { data($PA/Employee/EmployeeName) },
element EMail { data($PA/Employee/EMailID) },
element Skill { data($Skill) }
}
') as Result
FROM [EmployeePerformanceAppraisal]
WHERE Appraisal.exist('/PerformanceAppraisal/TrainingNeeds/Technical/Skill/text()[contains(.,"ASP.NET")]') = 1

시나리오 2: 의료 레코드 시스템

병원에서는 환자와 관련된 의료 정보를 캡처할 수 있는 시스템이 필요합니다. 이 정보에는 환자 세부 정보, 보험 정보, 입원 세부 정보, 진단 정보, 치료 정보 등이 포함됩니다. 이 정보는 연구 또는 참조 목적에 사용될 것입니다. 증상, 실험실 보고서 및 치료 정보와 같은 환자 의료 데이터에는 설명 정보가 포함되어 있으므로 이 정보를 저장하기 위한 형식으로 XML이 선택됩니다. 환자 데이터는 XML 형식 열에 저장될 수 있습니다. 병원에서는 이 정보를 분석하기 위해 XQuery를 사용할 수 있습니다.

다음 테이블과 XML 인스턴스는 환자 의료 레코드 시나리오에서 사용되는 XQuery를 보여 줍니다.

테이블:

CREATE TABLE [MedicalRecords](
[PatientID] [int] IDENTITY(1,1) NOT NULL,
[PatientRecord] [xml] NOT NULL,
[ModifiedDate] [datetime] NOT NULL DEFAULT (getdate())
,
PRIMARY KEY CLUSTERED
(
[PatientID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PRIMARY XML INDEX idx_PatientRecord on [MedicalRecords] (PatientRecord)
GO
CREATE XML INDEX idx_PatientRecord_Path on [MedicalRecords] (PatientRecord) USING XML INDEX idx_PatientRecord FOR PATH

샘플 XML 인스턴스:

Robert
Male
5

Blue Cross Blue Shield
D8456798

KK Hospital
Pediatrics

D4321
2004-05-02
2004-05-08

Abdominal pain
Dehydration

Diarrhea

Oral Rehydration Therapy

Pepto-Bismol
Electrolyte Solutions

“고열”과 “복통” 증상으로 입원했던 환자에 대한 의료 레코드를 의사가 보고 싶어한다고 가정해 보십시오. 다음 쿼리는 “고열”과 “복통” 증상으로 입원했던 환자의 레코드를 검색합니다.

SELECT PatientID, PatientRecord.query('
element PatientName { data(/PatientRecord/PatientDetails/Name) },
element MedicalInfo { /PatientRecord/ProblemDetails }
') as Result
FROM [MedicalRecords]
WHERE PatientRecord.exist
('/PatientRecord/ProblemDetails/Symptoms/Symptom/text()[contains(.,"Fever")]') = 1
AND PatientRecord.exist
('/PatientRecord/ProblemDetails/Symptoms/Symptom/text()[contains(.,"Abdominal Pain")]') = 1

시나리오 3: 자산 관리 시스템

회사의 IT 부서는 하드웨어 및 소프트웨어와 같은 자산을 관리할 수 있는 응용 프로그램을 개발해야 합니다. 이 응용 프로그램은 자산 ID, 사용자 세부 정보, 시스템 정보(예: 프로세서, 메모리, BIOS, 마더보드 및 비디오 카드 세부 정보), 시스템에 설치된 소프트웨어, 구매 정보, 보증 정보 등을 비롯하여 하드웨어 자산과 관련된 정보를 추적할 수 있어야 합니다.

또한 이 응용 프로그램은 소프트웨어 유형, 구매한 라이센스 수 등과 같은 조직의 소프트웨어 자산과 관련된 정보를 유지 관리해야 하며 미래에 정의될 새 자산 유형을 처리하도록 확장할 수 있어야 합니다. 이 자산 관리 시스템은 하드웨어 사용 정보, 소프트웨어 라이센스 사용 정보, 유지 관리해야 할 하드웨어 항목 등과 같은 보고서를 생성하는 데 사용됩니다. 현재의 시나리오에서는 동일한 테이블 열의 다른 스키마를 따르는 정보를 저장하는 것이 필요합니다. 형식 없는 XML을 사용하여 다양한 스키마를 가진 정보를 저장할 수 있습니다. 또한 스키마 컬렉션이 있는 형식 있는 XML을 사용하여 이러한 정보를 저장할 수 있습니다. XML 데이터 형식으로 저장된 자산 정보는 XQuery를 사용하여 쿼리할 수 있습니다.

IT 부서에서 Microsoft Windows XP와 Microsoft Office 2000이 모두 설치된 컴퓨터 시스템 목록을 선택하고 싶어한다고 가정해 보십시오. 다음 쿼리는 Windows XP와 Office 2000이 모두 설치된 하드웨어 자산의 레코드를 선택합니다.

SELECT AssetID, AssetDetails.query('

{
element UserName { data(/AssetInfo/UserInfo/Name) },
element ComputerName { data(/AssetInfo/SystemInfo/ComputerName) },
element OS { data(/AssetInfo/SystemInfo/OS) }
}

') as Result
FROM [Assets]
WHERE Category = 'Hardware'
AND AssetDetails.exist('/AssetInfo/SystemInfo/OS/text()[contains(.,"Windows XP")]') = 1
AND AssetDetails.exist('/AssetInfo/SoftwaresInstalled/Software/text()[contains(.,"Office 2000")]') = 1

결론

SQL Server 2005와 관련하여 XQuery의 기본적인 내용을 배우고 싶은 사람들은 이 문서를 기초서로 사용할 수 있을 것입니다. 이 문서에서는 SQL Server 2005에서 구현된 XQuery 언어의 여러 기능과 지원되지 않는 기능에 대해 설명하고 XQuery 언어의 여러 기능을 사용하는 예제를 제공했습니다. 이 문서에 포함된 XQuery 사용 시나리오는 XQuery를 사용하기에 적합한 상황을 식별하는 데 도움이 될 것입니다.

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

[출처] SQL Server 2005의 XQuery 소개|작성자 윈마스터

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

컬럼명 변경하기 (0)	2008.04.29
Table 변수 vs 임시 테이블 (Ver. 2000) (0)	2008.04.29
SQL Server 2005의 분할된 테이블 및 인덱스 (0)	2008.04.29
검색결과 xml 생성하기 (0)	2008.04.29
SQL 2005에서 행번호 매기기 (0)	2008.04.29

SQL Server 2005의 분할된 테이블 및 인덱스

2008. 4. 29. 09:16

728x90

Kimberly L. l Tripp SQLskills.com 설립자

SQL Server 2005의 테이블 기반 분할 기능은 분할된 테이블을 간단하게 만들고 유지 관리할 수 있도록 하는 융통성 및 성능을 제공합니다. 논리적이고 수동적인 테이블 분할 작업에서 최신 분할 기능에 이르기까지 여러 기능들의 발전 사항을 살펴보고, SQL Server 2005를 사용하여 분할된 테이블을 디자인, 구현 및 유지 관리하는 이유, 시기 및 방법에 대해 알아봅니다

목차

분할을 사용하는 이유
분할의 역사
SQL Server 7.0 이전 릴리스에서의 개체 분할
SQL Server 7.0의 분할된 뷰
SQL Server 2000의 분할된 뷰
SQL Server 2005의 분할된 테이블
정의 및 용어
범위 분할
분할 키 정의
인덱스 분할
분할의 특수 조건: 분할(split), 병합 및 전환
분할된 테이블을 만드는 단계
개체 분할 여부 결정
분할 키 및 분할 수 결정
다중 파일 그룹 사용 여부 결정
파일 그룹 만들기
범위 분할을 위한 분할 함수 만들기
분할 스키마 만들기
분할된 테이블 만들기
인덱스 만들기: 분할 여부 지정
전체 통합: 사례 연구
범위 분할: 판매 데이터
분할된 테이블 조인
슬라이딩 윈도우(Sliding-Window) 시나리오
목록 분할: 지역 데이터
요약
이 백서의 스크립트

분할을 사용하는 이유

분할은 무엇이며, 분할을 사용하는 이유는 무엇일까요? 간단하게 답하자면, 대형 테이블 및 다양한 액세스 패턴을 가진 테이블의 확장성 및 관리 용이성을 향상시키기 위해서입니다. 일반적으로 고객이나 판매 등 엔터티에 대한 정보를 저장하기 위해 테이블을 만들며, 각 테이블에는 해당 엔터티만을 설명하는 특성이 있습니다. 각 엔터티마다 하나의 테이블을 만들면 가장 쉽게 디자인하고 이해할 수 있지만, 이러한 테이블은 성능, 확장성 및 관리 용이성에 대해 최적화되어 있지 않습니다. 특히 테이블의 크기가 커질수록 더욱 그러합니다.

큰 테이블을 구성하는 것은 무엇일까요? VLDB(초대형 데이터베이스)의 크기는 수백 기가바이트, 심지어는 테라바이트에 달하기도 하지만 이 'VLDB'라는 용어가 반드시 데이터베이스 내의 개별 테이블 크기를 가리키는 것은 아닙니다. 대형 데이터베이스는 원하는 동작을 수행하지 않는 데이터베이스 또는 작업이나 유지 관리 비용이 미리 정의한 유지 관리 또는 예산 요구 사항을 초과하는 데이터베이스를 말합니다. 이러한 요구 사항은 테이블에도 적용됩니다. 다른 사용자의 작업 또는 유지 관리 작업으로 인해 가용성이 제한되는 경우 테이블은 대형으로 간주됩니다. 예를 들어, 성능이 크게 저하되거나 매일, 매주 또는 매달 두 시간의 유지 관리 시간 동안 테이블에 액세스할 수 없는 경우 판매 테이블은 대형으로 간주됩니다. 일부 경우 주기적인 가동 중지 시간은 허용되지만, 이는 보다 뛰어난 디자인 및 분할 구현을 통해 피하거나 최소화할 수 있습니다. VLDB라는 용어는 데이터베이스에만 적용되는 반면, 분할에 있어서는 테이블 크기에 더 유의해야 합니다.

크기 외에도, 테이블 내 여러 행 집합이 서로 다른 사용 패턴을 사용하는 경우 액세스 패턴이 다양한 테이블 또한 성능 및 가용성 면에 영향을 줄 수 있습니다. 사용 패턴이 항상 여러 가지인 것은 아니지만(분할의 요구 사항이 아님), 사용 패턴이 다양한 경우 분할을 수행하면 관리, 성능 및 가용성을 추가로 향상시킬 수 있습니다. 또한 판매 테이블 예제를 사용할 수 있도록 당월의 데이터는 읽기/쓰기가 가능한 반면 보통 테이블의 대부분을 차지하는 전월의 데이터는 읽기 전용입니다. 이와 같이 데이터가 여러 가지로 사용되는 경우나 데이터가 테이블 안팎으로 이동할 때의 유지 관리 부담이 매우 큰 경우, 테이블이 사용자 요청에 응답하는 기능에 영향이 있을 수 있습니다. 그 결과 서버의 가용성 및 확장성이 모두 제한됩니다.

또한 대형 데이터 집합을 여러 가지 방식으로 사용하는 경우 빈번한 유지 관리 작업이 정적 데이터에서 수행됩니다. 이로 인해 성능 문제, 차단 문제, 백업(공간, 시간 및 작업 부담) 등의 문제가 초래될 수 있을 뿐 아니라 서버의 전체 확장성에도 부정적인 영향을 줍니다.

분할이 이 문제를 어떻게 해결할 수 있을까요? 테이블과 인덱스가 매우 커지면 분할 기능을 통해 데이터를 크기가 작고 관리가 보다 용이한 섹션으로 분할할 수 있습니다. 이 백서에서는 대형 행 그룹이 여러 개별 분할에 저장되는 수평 분할을 주로 다룹니다. 분할된 집합 정의를 필요에 따라 사용자 지정, 정의 및 관리할 수 있습니다. Microsoft SQL Server 2005에서는 정의된 범위 또는 목록을 사용하여 특정 데이터 사용 패턴에 따라 테이블을 분할할 수 있습니다. SQL Server 2005에는 또한 새 테이블 및 인덱스 구조에 따라 디자인된 기능이 추가되어 있기 때문에 분할된 테이블 및 인덱스의 장기적인 관리를 위한 다양한 옵션이 제공됩니다.

또한, 여러 개의 CPU가 있는 시스템에 대형 테이블이 있는 경우 해당 테이블을 분할하면 병렬 작업을 통해 성능을 향상시킬 수 있습니다. 개별 하위 집합에 대해 여러 작업을 병렬로 수행하면 수백만 개의 행과 같이 매우 큰 데이터 집합에 걸쳐 수행하는 대규모 작업 성능을 향상시킬 수 있습니다. 분할로 인한 성능 향상의 예로는 이전 릴리스에서의 집계를 들 수 있습니다. 예를 들어, 단일 대형 테이블을 집계하는 대신 SQL Server는 분할에서 독립적으로 작업을 수행한 다음에 집계를 집계할 수 있습니다. SQL Server 2005에서는 대형 데이터 집합을 조인하는 쿼리가 분할의 이점을 직접적으로 활용할 수 있습니다. SQL Server 2000에서도 하위 집합에서의 병렬 조인 작업을 지원했지만, 이 경우 하위 집합을 즉석에서 만들어야 했습니다. SQL Server 2005에서 동일한 분할 키 및 분할 함수로 분할되는 관련 테이블(예: Order 및 OrderDetails 테이블)은 '맞춰진다'고 합니다. 최적화 프로그램에서 두 개의 분할 및 맞춰진 테이블이 조인되었음을 감지하면 SQL Server 2005는 같은 분할에 있는 데이터를 먼저 조인한 다음 결과를 결합할 수 있습니다. 이로 인해 SQL Server 2005는 여러 개의 CPU가 있는 컴퓨터를 보다 효율적으로 사용할 수 있습니다.

분할의 역사

분할이라는 개념은 SQL Server에서는 새로운 것이 아닙니다. 사실 SQL Server 제품의 모든 릴리스에서 여러 가지 형태의 분할이 가능했습니다. 그러나 분할 스키마를 만들고 유지 관리하기 위해 특수하게 디자인된 기능이 없었기 때문에 분할은 번거로웠으며 활용도가 낮았습니다. 또한 사용자 및 개발자는 보다 복잡한 데이터베이스 디자인으로 인해 스키마를 잘못 이해했기 때문에 이점이 줄어들었습니다. 그러나 이 개념은 기본적으로 상당한 성능상의 이점을 제공하므로 SQL Server 7.0에서는 분할된 뷰를 통해 분할의 형태를 사용 가능하도록 함으로써 이 기능을 향상시켰습니다. 그리고 이제 SQL Server 2005는 분할된 테이블을 통해 대형 데이터 집합을 분할하기 위한 매우 뛰어난 고급 기능을 제공합니다.

SQL Server 7.0 이전 릴리스에서의 개체 분할

SQL Server 6.5 이전 버전에서 분할은 디자인의 일부여야 했으며 모든 데이터 액세스 코딩 및 쿼리 방법에서 기본적으로 제공되어야 했습니다. 여러 개의 테이블을 만든 다음 저장된 프로시저, 뷰 또는 클라이언트 응용 프로그램을 통해 올바른 테이블에 대한 액세스를 관리함으로써 일부 작업에 대한 성능을 향상시킬 수는 있었지만, 이렇게 하면 디자인이 복잡해지는 단점이 있었습니다. 각 사용자 및 개발자는 올바른 테이블을 파악하고 있어야 하며 제대로 참조해야 했습니다. 각 분할은 별도로 만들어져 관리되었으며 뷰는 액세스를 단순화하는 데 사용되었습니다. 그러나 이 솔루션을 사용해도 성능은 거의 향상되지 않았습니다. 사용자 및 응용 프로그램 액세스를 단순화하기 위해 UNION이 지정된 뷰가 있으면 쿼리 프로세서는 모든 원본 테이블에 액세스하여 결과 집합에 필요한 데이터를 결정해야 했습니다. 원본 테이블의 제한된 하위 집합만 필요한 경우 각 사용자 및 개발자는 적합한 테이블만을 참조하기 위해 디자인을 파악하고 있어야 했습니다.

SQL Server 7.0의 분할된 뷰

SQL Server 7.0 이전 릴리스에서 수동으로 분할을 만드는 작업의 문제점은 주로 성능과 관련되어 있었습니다. 뷰로 인해 응용 프로그램 디자인, 사용자 액세스 및 쿼리 작성은 단순화되었지만 성능은 향상되지 않았습니다. SQL Server 7.0이 릴리스되면서 뷰는 제약 조건과 결합되어 쿼리 최적화 프로그램이 쿼리 계획에서 관련이 없는 테이블을 제거(분할 제거)할 수 있도록 했으며 UNION이 지정된 뷰가 여러 테이블에 액세스할 때 전체 계획 부담을 크게 줄였습니다.

그림 1에서 YearlySales 뷰를 살펴보십시오. 모든 판매 정보를 하나의 대형 테이블에 포함하는 대신 12개의 개별 테이블(SalesJanuary, SalesFebruary 등)을 정의한 다음 각 분기의 뷰를 비롯하여 전체 연도의 뷰인 YearlySales를 정의할 수 있습니다.

그림 1. SQL Server 7.0/2000의 분할된 뷰

다음 쿼리로 YearlySales 뷰에 액세스하는 사용자는 SalesJanuary2003 테이블로만 이동됩니다.

SELECT ys.*
FROM dbo.YearlySales AS ys
WHERE ys.SalesDate = '20030113'

제약 조건이 트러스트되고 뷰에 대한 쿼리가 WHERE 절을 사용하여 분할 키(제약 조건이 정의된 열)를 기반으로 결과를 제한하는 한, SQL Server는 필요한 기본 테이블에만 액세스합니다. 트러스트된 제약 조건은 모든 데이터가 제약 조건에 의해 정의된 속성을 준수함을 SQL Server가 보장할 수 있는 제약 조건입니다. 제약 조건을 만들 때의 기본 동작은 해당 제약 조건에 WITH CHECK를 지정하는 것입니다. 이 설정에 의해 테이블에서 스키마 잠금이 수행되어 데이터를 제약 조건에 대해 확인할 수 있습니다. 확인 작업에 의해 기존 데이터의 유효성이 검사되면 제약 조건이 추가됩니다. 스키마 잠금이 제거된 후의 추가 삽입, 업데이트 및 삭제는 적용되는 제약 조건을 따라야 합니다. 이 프로시저를 사용하여 트러스트된 제약 조건을 만들면 개발자는 뷰를 사용하여 디자인을 훨씬 단순하게 만들 수 있습니다. 이때 원하는 테이블에 직접적으로 액세스하지 않아도 되며, 심지어 해당 테이블이 있는지조차 몰라도 됩니다. 트러스트된 제약 조건을 통해 SQL Server는 실행 계획에서 불필요한 테이블을 제거함으로써 성능을 향상시킵니다.

참고 제약 조건이 "트러스트되지 않은" 상태가 되는 경우는 다양합니다. 예를 들면 CHECK_CONSTRAINTS 인수를 지정하지 않고 대량 삽입 작업을 수행하거나 NOCHECK를 통해 제약 조건을 만드는 경우가 있습니다. 제약 조건이 트러스트되지 않는 상태가 되면 쿼리 프로세서는 요청된 데이터가 실제로 올바른 기본 테이블에 있는지를 확인할 수 있는 방법이 없으므로 모든 기본 테이블을 검색하는 방식으로 되돌아갑니다.

SQL Server 2000의 분할된 뷰

SQL Server 7.0은 디자인을 크게 단순화하고 SELECT 문에 대해서는 성능을 향상시켰지만, 데이터 수정 문에 대해서는 이점을 제공하지 않습니다. INSERT, UPDATE 및 DELETE 문이 기본 테이블에 대해서만 지원되었으며 테이블에 UNION을 지정하는 뷰에 대해 직접 지원되지는 않았기 때문입니다. SQL Server 2000에서는 데이터 수정 문 또한 SQL Server 7.0에서 도입되었던 분할된 뷰 기능의 이점을 활용합니다. 데이터 수정 문은 동일한 분할된 뷰 구조를 사용할 수 있으므로, SQL Server는 뷰를 통해 수정 사항을 적합한 기본 테이블로 이동시킬 수 있습니다. 이를 올바르게 구성하려면 분할 키 및 분할 키 만들기에 대해 추가 제한 사항이 필요합니다. 그러나 SELECT 쿼리뿐 아니라 수정 내용도 적절한 기본 테이블로 직접 전송된다는 점에서, 기본적인 원칙은 동일합니다. SQL Server 2000에서의 분할 작업 제한 사항, 설정, 구성 및 가장 효율적인 분할 방법에 대한 자세한 내용은 Microsoft SQL Server 2000 데이터 웨어하우스에서 파티션 사용을 참조하십시오.

SQL Server 2005의 분할된 테이블

SQL Server 7.0 및 SQL Server 2000의 향상된 기능으로 인해 분할된 뷰를 사용할 때의 성능이 크게 향상되기는 했지만 분할된 데이터 집합의 관리, 디자인 또는 개발은 단순화되지 않았습니다. 분할된 보기를 사용할 때는 뷰가 정의되는 기본 테이블을 모두 만들어 개별적으로 관리해야 합니다. 응용 프로그램 디자인이 보다 쉬워졌으며 사용자는 직접 액세스할 기본 테이블이 무엇인지 몰라도 된다는 이점이 있지만, 관리할 테이블이 많으며 데이터 무결성 제약 조건을 각 테이블에 대해 관리해야 하므로 관리가 복잡해집니다. 관리 문제점 때문에 분할된 뷰는 종종 데이터를 보관하거나 로드해야 했을 때만 개별 테이블에 사용되었습니다. 데이터가 읽기 전용 테이블로 이동되거나 읽기 전용 테이블에서 데이터가 삭제되면 작업은 매우 힘들어집니다. 시간이 길어지고, 로그 공간이 필요하며, 종종 차단이 생깁니다.

또한, 이전 버전의 분할 전략을 사용하는 경우 개발자가 개별 테이블 및 인덱스를 만든 다음 뷰를 통해 여기에 UNION을 지정해야 했으므로 최적화 프로그램은 각 분할에 대해 계획의 유효성을 검사하고 확인해야 했습니다(인덱스가 바뀌었을 수 있기 때문임). 그러므로 SQL Server 2000의 쿼리 최적화 시간은 처리된 분할 수가 증가함에 따라 길어지는 경우가 많았습니다.

SQL Server 2005에서 각 분할에는 동일한 인덱스가 정의되어 있습니다. 예를 들어, 당월의 OLTP(Online Transaction Processing) 데이터를 매달 말에 분석 테이블로 옮겨야 하는 시나리오를 가정해 봅시다. 읽기 전용 쿼리에 사용되는 분석 테이블은 클러스터된 인덱스 하나와 클러스터되지 않은 인덱스 두 개가 있는 단일 테이블입니다. 이미 인덱싱된 활성 단일 테이블로 1GB의 데이터를 대량 로드하면 테이블 및/또는 인덱스가 조각으로 분리되거나 잠겨 현재 사용자와의 차단이 발생합니다. 또한 각 행이 들어올 때 테이블 및 인덱스를 유지 관리해야 하므로 로딩 프로세스에 시간이 매우 많이 걸립니다. 대량 로드의 속도를 높이는 방법에는 여러 가지가 있지만 이러한 방법을 사용하면 다른 모든 사용자에 직접적으로 영향을 주게 되며 속도는 높아지는 대신 동시성이 떨어집니다.

이 데이터가 새로 만든(빈) 인덱싱되지 않은 [heap] 테이블에 격리된 경우 로드를 먼저 수행한 다음 데이터의 로드가 끝난 후에 인덱스를 만들 수 있습니다. 이 스키마를 사용하면 성능이 10배 이상 향상되는 경우가 많습니다. 사실 인덱싱되지 않은 테이블로 로드하면 여러 데이터 파일을 병렬로 로드하거나 동일한 파일(시작 및 끝 행 위치에 의해 정의됨)에서 여러 청크를 로드함으로써 복수 CPU의 이점을 활용할 수 있습니다. 두 작업 모두 병렬 처리의 이점을 활용하므로 성능을 더욱 향상시킬 수 있습니다.

SQL Server의 모든 릴리스에서 분할을 통해 이 작업을 보다 자세히 제어할 수 있으며, 모든 데이터를 한 위치에 배치할 필요가 없습니다. 그러나 이 경우 많은 개체를 만들고 관리해야 합니다. 기능적인 분할 전략은 동적으로 테이블을 만들거나 삭제하고 UNION이 지정된 뷰를 수정함으로써 이전 릴리스에서도 수행할 수 있었습니다. 그러나 SQL Server 2005에서 이 솔루션은 보다 향상되었습니다. 새로 채운 분할을 기존 분할 스키마의 추가 분할로 바꿔 넣고 이전 분할은 빼면 됩니다. 전체 과정에서 이 프로세스에 걸리는 시간은 아주 짧으며, 병렬 대량 로드 및 병렬 인덱스 만들기를 통해 작업을 보다 효과적으로 수행할 수 있습니다. 더욱 중요한 것은, 분할이 테이블 범위 외부에서 조정되므로 분할을 추가할 때까지 쿼리한 테이블에는 아무런 영향이 없다는 것입니다. 그 결과 일반적으로 분할을 추가하는 데 몇 초밖에 걸리지 않습니다.

성능은 데이터를 제거해야 할 때도 크게 향상됩니다. 한 데이터베이스에서 새 데이터(당월)가 마이그레이션되는 슬라이딩 윈도우(Sliding-Window) 데이터 집합을 필요로 하고 가장 오래된 데이터(전년도의 같은 달)는 제거하는 경우, 이 데이터 마이그레이션의 성능은 분할 사용을 통해 몇 배 향상시킬 수 있습니다. 분할을 사용하지 않을 때와 비교해 보면 그 차이는 더욱 큽니다. 모든 데이터가 하나의 테이블에 있는 경우 1GB의 이전 데이터를 삭제하려면 행 단위로 테이블을 조정해야 할 뿐 아니라 관련 인덱스도 조정해야 합니다. 데이터 삭제 프로세스에서는 매우 많은 양의 로그 작업이 발생하며, 삭제 작업 동안에는 로그를 자를 수 없으므로 훨씬 큰 로그가 필요할 수도 있습니다. 단, 삭제 작업은 단일 자동 커밋 트랜잭션이지만 가능한 경우에는 여러 번 삭제를 수행하여 트랜잭션 크기를 제어할 수는 있습니다. 그러나 분할을 사용하면 그와 같은 데이터를 제거할 때는 분할된 테이블에서 특정 분할을 제거(메타데이터 작업임)한 다음 독립 실행형 테이블을 삭제하거나 자르면 됩니다.

또한, 분할을 가장 효율적으로 디자인하는 방법을 모르면 분할과 함께 파일 그룹을 사용하는 것이 분할 구현에 이상적이라는 사실을 모를 수도 있습니다. 파일 그룹을 통해 서로 다른 실제 디스크에 개별 테이블을 배치할 수 있습니다. 단일 테이블이 파일 그룹을 사용해 여러 파일로 범위가 확장되는 경우에는 실제 데이터 위치를 예측할 수 없습니다. 병렬 처리를 사용할 수 없는 시스템에 대해 SQL Server는 파일 그룹을 통해 모든 디스크를 균등하게 사용하여 특정 데이터 배치의 중요도를 낮춤으로써 성능을 향상시킵니다.

참고 그림 2에는 단일 파일 그룹에 3개의 파일이 있습니다. Orders 및 OrderDetails의 두 테이블이 이 파일 그룹에 배치되었습니다. 테이블이 파일 그룹에 배치되면 파일 그룹 내의 개체에 대해 공간이 필요하므로, SQL Server는 각 파일로부터 범위 할당(8개의 8KB 페이지와 같은 64KB 청크)을 가져와 파일 그룹 내에 파일을 균등하게 채웁니다. Orders 및 OrderDetails 테이블이 만들어지면 파일 그룹은 비게 됩니다. 주문이 들어오면 Orders 테이블에 주문당 한 행씩 데이터가 입력되고 OrderDetails 테이블에는 라인 항목당 한 행씩 데이터가 입력됩니다. SQL Server는 File1에서 Orders 테이블에 범위를 할당한 다음 File2에서 OrderDetails 테이블에 다른 범위를 할당합니다. OrderDetails 테이블은 Orders 테이블보다 크기가 더 빨리 커지며, 다음 번의 할당은 공간이 필요한 다음 테이블에 대해 수행됩니다. OrderDetails의 크기가 커지면 File3에서 다음 범위를 할당받으며, SQL Server는 파일 그룹 내의 파일을 통해 이 작업을 "계속(round robin)"합니다. 그림 2에서 각 테이블의 해당 범위와 각 범위에서 해당 파일 그룹까지의 흐름을 확인할 수 있습니다. 범위는 공간이 필요할 때 할당되며 각각 흐름을 기반으로 번호가 지정됩니다.

그림 2. 파일 그룹을 통한 비례 채우기

SQL Server는 해당 파일 그룹 내의 모든 개체에 대해 할당의 균형을 계속 조정합니다. 지정된 작업에 대해 더 많은 디스크를 사용하면 SQL Server는 보다 효과적으로 실행되는 반면, 관리 또는 유지 면에서는 더 많은 디스크를 사용하는 것이 좋지 않습니다. 특히 사용 패턴을 예측하기가 쉬우며 해당 패턴이 격리되어 있는 경우에는 더욱 그러합니다. 데이터는 디스크에서 특정 위치를 차지하지 않으므로 백업 작업 등의 유지 관리를 위해 해당 데이터를 격리할 수는 없습니다.

SQL Server 2005의 분할된 테이블을 사용하면 분할 키가 같은 모든 행을 특정 위치에 직접 배치하고 항상 그 위치로 이동하도록 함수 및 스키마를 통해 테이블을 디자인할 수 있습니다. 함수는 분할 경계를 정의할 뿐 아니라 첫 번째 값이 배치되어야 하는 분할도 정의합니다. LEFT 분할 함수의 경우 첫 번째 값은 첫 번째 분할에서 상한 역할을 합니다. 그리고 RIGHT 분할 함수의 경우 첫 번째 값은 두 번째 분할의 하한 역할을 합니다. 분할 함수는 이 백서의 뒷부분에서 좀 더 자세하게 다룹니다. 함수를 정의하고 나면 분할 스키마를 만들어 분할 함수를 기반으로 데이터베이스 내에서 해당 위치에 분할의 실제 매핑을 정의할 수 있습니다. 여러 테이블이 같은 함수를 사용할 때(같은 스키마를 사용하는 것은 아님) 분할 키가 같은 행은 비슷하게 그룹화됩니다. 이 개념을 맞춤이라고 합니다. 동일하거나 서로 다른 실제 디스크에 있는 여러 테이블로부터 같은 분할 키를 가지고 있는 행을 맞춤으로써 SQL Server는 최적화 프로그램이 선택하는 경우 각 테이블에서 필요한 데이터 그룹만을 사용할 수 있습니다. 맞춤을 수행하기 위해 두 개의 분할된 테이블이나 인덱스에는 각각의 분할 간에 일부 상응하는 부분이 있어야 합니다. 즉, 분할 열에 대해 동일한 분할 함수를 사용해야 합니다. 두 분할 함수는 다음과 같은 경우에 데이터를 맞추는 데 사용할 수 있습니다.

두 분할 함수가 모두 같은 수의 인수와 분할을 사용하는 경우
각 함수에서 사용되는 분할 키가 같은 유형인 경우(길이, 해당되는 경우 정밀도 및 배율, 데이터 정렬 포함)
경계 값이 동등한 경우(LEFT/RIGHT 경계 조건 포함)
참고 데이터를 맞추기 위해 두 분할 함수를 디자인한 경우에도 인덱스가 분할된 테이블로 동일한 열에서 분할되지 않는 경우에는 인덱스가 맞춰지지 않을 수도 있습니다.
병치(collocation)는 맞춤보다 강력한 형식이며, 맞춰진 두 개체가 분할 열에 있는 동등 조인(equi-join) 조건부로 조인됩니다. 이는 동등 조인(equi-join) 조건부가 있을 수 있는 쿼리, 하위 쿼리 또는 기타 유사한 구문에서 중요합니다. 분할 열에서 테이블을 조인하는 쿼리가 일반적으로 훨씬 더 빠르므로 병치(collocation)는 중요한 기능입니다. 그림 2에서 설명한 Orders 및 OrderDetails 테이블을 예로 들어 봅시다. 파일을 비례적으로 채우는 대신 3개의 파일 그룹에 매핑되는 분할 스키마를 만들 수 있습니다. Orders 및 OrderDetails 테이블을 정의할 때 같은 스키마를 사용하도록 정의합니다. 분할 키에 대해 같은 값을 가진 관련 데이터는 같은 파일 내에 배치되어 조인을 위해 필요한 데이터를 격리합니다. 여러 테이블의 관련 행이 동일한 방식으로 분할되면 SQL Server는 전체 테이블 또는 여러 분할(테이블이 서로 다른 분할 함수를 사용하는 경우)에서 일치하는 행을 검색할 필요 없이 분할을 조인할 수 있습니다. 이 경우 개체는 동일한 키를 사용하기 때문에 맞춰질 뿐만 아니라, 동일한 데이터가 같은 파일 내에 상주하므로 저장소 맞춤이 적용됩니다.

그림 3은 두 개의 개체가 동일한 분할 스키마를 사용할 수 있으며 분할 키가 같은 모든 데이터 행이 동일한 파일 그룹에 배치됨을 보여 줍니다. 관련 데이터가 맞춰지면 SQL Server 2005는 대형 집합에 대해 효율적으로 병렬 방식으로 작업할 수 있습니다. 예를 들어 Orders 및 OrderDetails 테이블의 모든 1월 판매 데이터는 첫 번째 파일 그룹에 있고, 2월 데이터는 두 번째 파일 그룹에 있는 식입니다.

그림 3. 저장소 맞춤 테이블

SQL Server에서는 범위를 기반으로 분할을 수행할 수 있으며 테이블 및 인덱스가 같은 스키마를 사용하도록 디자인하여 보다 효과적으로 맞춤 작업을 할 수 있습니다. 뛰어난 디자인으로 인해 전체적인 성능은 크게 향상됩니다. 그러나 데이터 사용이 시간에 따라 변경되는 경우는 어떨까요? 그리고, 추가 분할이 필요한 경우는 어떨까요? 분할 추가, 제거 및 분할된 테이블 외부에서의 분할 관리에 있어서 관리 작업을 단순화하는 것이 SQL Server 2005의 주요 디자인 목표였습니다.

SQL Server 2005에서는 관리, 개발 및 사용을 염두에 두고 분할을 단순화했습니다. 다음은 일부 성능 및 관리 이점입니다.

성능 및 관리 목적을 위해 분할해야 하는 대형 테이블의 디자인 및 구현 단순화
나머지 분할의 데이터에 액세스할 때 방해를 최소화하며 기존에 분할된 테이블의 새 분할로 데이터 로드
비어 있는 새 테이블에 동일한 데이터를 로드할 때와 동일한 성능으로 기존에 분할된 테이블의 새 분할로 데이터 로드
테이블의 나머지 부분에 대한 액세스에 주는 영향을 최소화하며 분할된 테이블 일부를 보관 및/또는 제거
분할된 테이블에서 분할을 포함/제외하여 분할을 유지 관리할 수 있도록 함
여러 개의 관련 테이블에 대한 대규모 작업을 더욱 뛰어나게 확장 및 병렬 처리할 수 있도록 함
모든 분할에 대해 성능 향상
각 분할을 별도로 최적화할 필요가 없으므로 쿼리 최적화 시간 단축

정의 및 용어

SQL Server 2005에서 분할을 구현하려면 몇 가지 새로운 개념, 용어 및 구문에 익숙해야 합니다. 이러한 새 개념을 이해하기 위해 우선 만들고 배치하는 방법을 중심으로 테이블 구조를 살펴보겠습니다. 이전 릴리스에서 테이블은 항상 실제적이자 논리적인 개념이었지만, SQL Server 2005의 분할된 테이블 및 인덱스를 사용하면 다양한 테이블 저장 위치 및 방법 중에서 선택할 수 있습니다. SQL Server 2005에서는 테이블 및 인덱스를 이전 릴리스와 동일한 구문을 사용하여 만들 수 있습니다. 즉, DEFAULT 파일 그룹 또는 사용자 정의 파일 그룹에 배치되는 단일 테이블 형식 구조로 만들 수 있습니다. 또한 SQL Server 2005에서는 테이블 및 인덱스를 분할 스키마에 만들 수 있습니다. 분할 스키마는 개체를 하나 이상의 파일 그룹에 매핑합니다. 적절한 실제 위치로 이동되는 데이터를 결정하기 위해 분할 스키마는 분할 함수를 사용합니다. 분할 함수는 행 위치를 지정하는 데 사용될 알고리즘을 정의하며 스키마는 분할을 해당 실제 위치(파일 그룹)와 연결합니다. 다시 말해 테이블은 여전히 논리적 개념이지만 디스크에서 테이블의 실제 배치는 이전 릴리스와 크게 다릅니다. 이제는 테이블에 스키마가 포함될 수 있는 것입니다.

범위 분할

범위 분할은 사용자 지정 가능한 특정 데이터 범위에 의해 정의되는 테이블 분할입니다. 범위 분할 경계는 개발자가 선택하며 데이터 사용 패턴이 바뀌면 변경될 수 있습니다. 일반적으로 이러한 범위는 데이터를 기준으로 하거나 순서가 지정된 데이터 그룹화를 기준으로 합니다.

범위 분할은 주로 데이터 보관, 의사 결정 지원(지정된 달이나 분기와 같이 종종 특정 데이터 범위만이 필요한 경우) 및 데이터 사용이 행 수명에 따라 달라지는 결합된 OLTP 및 DSS(의사 결정 지원 시스템)에 사용됩니다. SQL Server 2005 분할된 테이블 및 인덱스의 가장 큰 이점은 특히 보관 및 유지 관리와 관련하여 매우 구체적인 데이터 범위를 조정할 수 있는 기능입니다. 범위 분할을 사용하면 이전 데이터를 매우 빠르게 보관 및 대체할 수 있습니다. 범위 분할은 보통 광범위한 데이터에 대한 의사 결정 지원을 위해 데이터에 액세스하는 경우에 가장 적합합니다. 이 경우 필요할 때 적절한 분할에만 액세스할 수 있어야 하므로 데이터의 위치가 구체적으로 지정되어 있어야 합니다. 또한 트랜잭션 데이터를 사용할 수 있으므로 데이터를 쉽고 빠르게 추가할 수 있습니다. 각 분할에 대해 경계 조건을 정의해야 하므로 범위 분할은 처음에는 정의하기가 조금 더 복잡합니다. 또한 스키마를 만들어 하나 이상의 파일 그룹에 각 분할을 매핑해야 합니다. 그러나 범위 분할은 보통 일관성 있는 패턴을 따르므로 일단 정의하고 나면 프로그래밍 방식으로 쉽게 유지 관리할 수 있습니다(그림 4 참조).

그림 4. 12개의 분할이 있는 범위 분할된 테이블

분할 키 정의

테이블 및 인덱스 분할 작업의 첫 단계는 분할에 키가 지정되는 데이터를 정의하는 것입니다. 분할 키는 테이블에 단일 열로 존재해야 하며 특정 조건을 만족해야 합니다. 분할 함수는 데이터의 논리적 구분이라고도 하는 키의 기준이 되는 데이터 형식을 정의합니다. 함수는 이 키는 정의하지만 디스크에서 데이터의 실제 배치는 정의하지 않습니다. 데이터 배치는 분할 스키마가 결정합니다. 즉, 스키마는 데이터를 특정 파일과 디스크에 매핑하는 하나 이상의 파일 그룹에 데이터를 매핑합니다. 스키마는 이 작업을 수행하기 위해 항상 함수를 사용합니다. 함수가 5개의 분할을 정의하면 스키마는 5개의 파일 그룹을 사용해야 합니다. 파일 그룹은 다르지 않아도 되지만 CPU와 디스크가 여러 개이면 성능이 보다 향상됩니다. 스키마를 테이블과 함께 사용할 때는 분할 함수의 인수로 사용되는 열을 정의해야 합니다.

범위 분할에서 데이터 집합은 논리적인 데이터 중심 경계로 구분됩니다. 사실 데이터 분할의 균형을 완전히 조정하지는 못할 수도 있습니다. 테이블이 범위라고도 하는 특정 분석 경계를 정의하는 패턴으로 사용될 때는 데이터 사용에 따라 범위 분할이 결정됩니다. 범위 함수의 분할 키는 열 하나만으로 구성될 수 있으며, 분할 함수는 데이터 무결성/제약 조건으로 인해 테이블 내에 해당 데이터가 존재하지 않는 경우에도 전체 도메인을 포함합니다. 다시 말해서, 경계는 각 분할에 대해 정의되지만 첫 번째 분할과 두 번째 분할에는 가장 왼쪽(가장 낮은 경계 조건보다 작은 값)의 행 및 가장 오른쪽(가장 큰 경계 조건보다 큰 값)의 행이 포함될 수 있습니다. 그러므로 값 도메인을 특정 데이터 집합으로 제한하려면 분할을 CHECK 제약 조건으로 결합해야 합니다. CHECK 제약 조건을 사용하여 비즈니스 규칙 및 데이터 무결성 제약 조건을 적용하면 데이터 집합을 무제한의 범위가 아닌 유한한 범위로 제한할 수 있습니다. 범위 분할은 유지 및 관리 작업에서 정기적으로 광범위한 데이터를 보관해야 하며 쿼리가 범위의 하위 집합 내에 있는 많은 양의 데이터에 액세스하는 경우에 적합합니다.

인덱스 분할

테이블의 데이터 집합을 분할할 수 있을 뿐 아니라 인덱스도 분할할 수 있습니다. 같은 함수를 사용하여 테이블 및 해당 인덱스를 분할하면 성능이 최적화되는 경우가 많습니다. 인덱스와 테이블이 같은 분할 함수와 열을 같은 순서로 사용하는 경우 테이블과 인덱스는 맞춰진 것입니다. 이미 분할되어 있는 테이블에서 인덱스가 만들어지면 SQL Server는 인덱스가 명시적으로 다르게 분할되어 있지 않는 한 자동으로 새 인덱스를 테이블의 분할 스키마에 맞춥니다. 테이블 및 해당 인덱스가 맞춰지면 모든 관련 데이터 및 인덱스는 같은 알고리즘으로 나누어지므로 SQL Server는 분할을 분할된 테이블 내외부로 보다 효과적으로 이동할 수 있습니다.

같은 분할 함수뿐 아니라 같은 분할 스키마를 사용하여 정의되는 테이블 및 인덱스는 저장소 맞춤이 적용된 것으로 간주됩니다. 저장소 맞춤의 한 가지 이점은 같은 경계 내의 모든 데이터가 같은 실제 디스크에 배치된다는 것입니다. 이 경우 백업은 특정 시간으로 격리할 수 있으며 전략은 빈도 및 백업 유형 면에서 데이터의 변동에 따라 달라질 수 있습니다. 같은 파일이나 파일 그룹의 테이블 및 인덱스가 조인 또는 집계되는 경우에는 성능이 더욱 향상됩니다. SQL Server는 분할 전체에 걸친 작업 병렬화의 이점을 활용합니다. 저장소 맞춤 및 복수 CPU의 경우 모든 필수 데이터가 같은 디스크에 있으므로 각 프로세서는 데이터 액세스의 충돌 없이 특정 파일이나 파일 그룹에서 직접 작업할 수 있습니다. 이로 인해 보다 많은 프로세스가 중단 없이 병렬로 실행될 수 있습니다.

자세한 내용은 SQL Server 온라인 설명서의 "분할된 인덱스에 대한 특별 지침"을 참조하십시오.

분할의 특수 조건: 분할(split), 병합 및 전환

분할된 테이블의 사용을 돕기 위해 분할 관리와 관련된 몇 가지 새 기능 및 개념이 추가되었습니다. 분할은 확장 가능한 대형 테이블에 사용되므로 분할 함수를 만들 때 선택한 분할 수는 시간에 따라 변경됩니다. ALTER TABLE 문과 새로운 분할(split) 옵션을 사용하여 다른 분할을 테이블에 추가할 수 있습니다. 분할이 분할(split)되면 데이터를 새 분할로 이동할 수 있지만, 성능을 유지하려면 행은 이동하지 않아야 합니다. 이 시나리오는 이 백서 뒷부분의 사례 연구에서 설명합니다.

반대로 분할을 제거하려면 해당 데이터를 제외한 다음 경계 지점을 병합합니다. 범위 분할의 경우 제거해야 하는 경계 지점을 제시함으로써 병합 요청을 하게 됩니다. 특정 기간의 데이터만이 필요하며 데이터가 정기적(예: 월 단위)으로 보관되는 경우에는 당월의 데이터를 사용할 수 있게 되면 하나의 데이터 분할(맨 첫달)을 보관하도록 할 수 있습니다. 예를 들어 1년 동안의 데이터를 사용할 수 있도록 하면 매달 말일에 당월의 데이터가 새로 들어가고 맨 첫달의 데이터는 빠지는 식으로 당월의 읽기/쓰기 OLTP와 전월의 읽기 전용 데이터를 구분할 수 있습니다. 다음 시나리오에 나타나 있듯이 프로세스를 가장 효율적으로 수행할 수 있도록 하는 특정 작업 흐름이 있습니다.

1년분의 읽기 전용 데이터를 사용 가능한 상태로 유지합니다. 테이블에는 현재 2003년 9월부터 2004년 8월까지의 데이터가 보관되어 있습니다. 당월인 2004년 9월의 데이터는 다른 데이터베이스에 있으며, OLTP 성능을 위해 최적화되어 있습니다. 테이블의 읽기 전용 버전에는 13개의 분할이 있습니다. 그 중 12개의 분할에는 2003년 9월부터 2004년 8월까지의 데이터가 포함되어 있으며, 마지막 분할 하나는 비어 있습니다. 범위 분할은 맨 왼쪽 및 맨 오른쪽을 비롯하여 전체 도메인을 항상 포함하므로 이 마지막 분할은 비어 있습니다. 그리고 슬라이딩 윈도우(Sliding-Window) 시나리오에서 데이터를 관리하려는 경우에는 새 데이터가 배치되는 빈 분할이 항상 분할(split)되도록 할 수 있습니다. LEFT 경계 지점으로 정의된 분할 함수에서 빈 분할은 논리적으로 가장 먼 RIGHT에 있습니다. 마지막에 분할을 비워 두면 들어오는 새 데이터를 위해 빈 분할을 분할(split)할 수 있으며, 다른 데이터 청크를 포함하기 위해 분할이 분할(split)될 때 마지막 분할의 행을 추가되는 새 파일 그룹으로 이동하지 않아도 됩니다. 마지막 분할에는 행이 없기 때문입니다. 이는 이 백서 뒷부분의 사례 연구에서 자세하게 설명할 다소 복잡한 개념입니다. 그러나 핵심적인 내용은 모든 데이터 추가 및 삭제 작업은 메타데이터 전용 작업이어야 한다는 것입니다. 메타데이터 전용 작업이 수행되도록 하기 위해 테이블에서 변경되는 부분을 전략적으로 관리할 수 있습니다. 이 분할이 비어 있도록 하려면 CHECK 제약 조건을 사용하여 기본 테이블에서 이 데이터를 제한합니다. 이 경우 OrderDate는 2003년 9월 1일 당일 또는 이후이거나 2004년 9월 1일 이전이어야 합니다. 마지막으로 정의한 경계 지점이 8월 31일 11:59:59.997인 경우(997이 붙는 이유는 이후 설명함) 분할 함수와 이 제약 조건이 조합되어 마지막 분할을 비어 있도록 유지합니다. 이러한 내용은 단지 개념일 뿐이지만, 분할(split) 및 병합은 ALTER PARTITION FUNCTION을 통해 처리되고 전환은 ALTER TABLE을 통해 처리된다는 것이 중요합니다.

그림 5. 데이터 로드/보관 전의 범위 분할 경계

OLTP 데이터베이스에서 10월이 시작되면 9월의 데이터는 분할된 테이블로 옮겨져 분석에 사용됩니다. 테이블을 추가하고 제외하는 프로세스는 고속으로 이루어지며 준비 작업은 분할된 테이블 외부에서 수행할 수 있습니다. 이 시나리오는 이후 나올 사례 연구에서 자세하게 설명되지만 핵심적인 내용은 최종적으로는 분할된 테이블 내의 분할이 되는 "Staging 테이블"을 사용한다는 것입니다. 이 시나리오에 대한 자세한 정보는 이 백서 뒷부분의 사례 연구에서 설명합니다. 이 프로세스에서는 테이블의 분할을 같은 파일 그룹 내에 있는 분할되지 않은 테이블로 빼냅니다(그림 6). 분할되지 않은 테이블이 같은 파일 그룹 내에 이미 있으므로(있어야 작업이 성공적으로 수행됨) SQL Server는 이를 메타데이터 변경으로 수행되도록 할 수 있습니다. 이는 메타데이터 전용 변경이며, 삭제 작업을 실행하면 몇 시간이 걸리고 대형 테이블에서 차단이 생길 수 있는 반면 변경 작업은 단 몇 초 만에 수행할 수 있습니다. 이 분할을 제외한 후에도 분할은 여전히 13개입니다. 가장 오래된 첫 번째 분할은 이제 비어 있는 상태이며, 가장 최신 상태이고 역시 비어 있는 마지막 분할은 분할(split)해야 합니다.

그림 6. 분할 제외

가장 오래된 분할(2003년 9월)을 제거하려면 새로운 병합 옵션을 ALTER TABLE과 함께 사용합니다(그림 7 참고). 경계 지점을 병합하면 경계 지점이 효과적으로 제거되므로 분할도 제거할 수 있습니다. 그러면 데이터가 로드되는 분할 수가 n-1(이 경우 12)로 줄어듭니다. 병합할 경계 지점에 데이터 행이 없어 이동해야 하는 행이 없는 경우에는 분할을 병합하는 작업은 고속으로 진행됩니다. 이 경우 첫 번째 분할이 비어 있으므로 첫 번째 분할에서 두 번째 분할로 옮겨야 하는 행은 없습니다. 첫 번째 분할이 비어 있지 않을 때 경계 지점을 병합하면 행을 첫 번째 분할에서 두 번째 분할로 이동해야 하므로 작업 부담이 매우 커질 수 있습니다. 그러나 대부분의 슬라이딩 윈도우(Sliding-Window) 시나리오에서는 빈 분할을 활성 분할과 병합하여 행을 이동하지 않으므로 이를 피할 수 있습니다.

그림 7. 분할 병합

마지막으로 새 테이블을 분할된 테이블에 추가해 넣어야 합니다. 이를 메타데이터 변경으로 수행하려면 분할된 테이블 경계 외부에서 새 테이블에 대해 인덱스 로드 및 빌드를 수행해야 합니다. 분할을 추가하려면 먼저 가장 최신 상태의 비어 있는 맨 마지막 범위를 두 개의 분할로 분할(split)합니다. 또한 새 범위를 허용하도록 테이블의 제약 조건을 업데이트해야 합니다. 여기서도 분할된 테이블에는 13개의 분할이 있습니다. 슬라이딩 윈도우(Sliding-Window) 시나리오에서 LEFT 분할 함수를 사용한 마지막 분할은 항상 비어 있는 상태로 유지됩니다.

그림 8. 분할 분할(split)하기

이제 새로 로드한 데이터를 12번째 분할(2004년 9월)에 추가해 넣을 수 있습니다.

그림 9. 분할 추가

그러면 테이블은 다음과 같이 됩니다.

그림 10. 데이터 로드/보관 후의 범위 분할 경계

한 번에 하나의 분할만 추가 또는 제거할 수 있으므로 둘 이상의 분할을 추가하거나 제거해야 하는 테이블은 다시 만들어야 합니다. 이와 같은 새 분할 구조로 변경하려면 먼저 새 분할된 테이블을 만든 다음 데이터를 새로 만든 테이블로 로드합니다. 이는 각 분할(split)에 대해 전체 테이블의 균형을 다시 조정하는 것보다 적절한 방법입니다. 이 프로세스는 새 분할 함수 및 새 분할 스키마를 사용하고 데이터를 새로 분할한 테이블로 이동하여 수행할 수 있습니다. 데이터를 이동하려면 먼저 INSERT 새 테이블SELECT 열 목록 FROM 이전 테이블을 사용하여 데이터를 복사한 다음 원래 테이블을 삭제합니다. 이 프로세스를 실행 중일 때 데이터가 손상되지 않도록 사용자가 수정 작업을 하지 못하도록 하십시오.

자세한 내용은 SQL Server 온라인 설명서의 "ALTER PARTITION FUNCTION" 및 "ALTER TABLE"을 참조하십시오.

분할된 테이블을 만드는 단계

이제 분할된 테이블의 중요성에 대해 이해했으므로 다음 섹션에서는 분할된 테이블 구현 프로세스 및 이 프로세스를 도와 주는 기능에 대해 자세히 설명합니다. 논리의 흐름은 다음과 같습니다.

그림 11. 분할된 테이블 또는 인덱스를 만드는 단계

개체 분할 여부 결정

분할은 많은 이점을 제공하기도 하지만 한편으로는 개체를 구현할 때 관리 부담이 커지고 과정이 복잡해지므로 득보다는 실이 많을 수도 있습니다. 특히 작은 테이블이나 현재 성능 및 유지 관리 요구 사항을 충족하는 테이블은 분할하기를 원치 않을 수도 있습니다. 앞서 언급한 판매 시나리오에서는 분할을 사용하여 행과 데이터 이동 작업의 부담을 줄였습니다. 분할 구현 여부를 결정할 때는 현 상황에서 이러한 종류의 작업 부담이 있는지를 고려해야 합니다.

분할 키 및 분할 수 결정

대형 데이터 하위 집합에 대해 성능 및 관리 용이성을 향상시키려는 경우 정의된 액세스 패턴이 있으면 범위 분할을 통해 데이터 경합을 줄일 수 있을 뿐만 아니라 읽기 전용 데이터에서 필요로 하지 않는 경우 유지 관리 작업도 줄일 수 있습니다. 분할 수를 결정하려면 데이터 내에 논리 그룹 및 패턴이 있는지를 평가해야 합니다. 이러한 정의된 하위 집합 중 한 번에 몇 개만을 사용하여 작업하는 경우가 많으면, 쿼리가 격리되어 적절한 데이터(특정 분할)로만 작업하도록 범위를 정의합니다.

자세한 내용은 SQL Server 온라인 설명서의 "분할된 테이블 및 인덱스 디자인"을 참조하십시오.

다중 파일 그룹 사용 여부 결정

성능과 유지 관리를 최적화하려면 파일 그룹을 사용하여 데이터를 구분해야 합니다. 파일 그룹 수는 부분적으로 하드웨어 리소스에 의해 결정됩니다. 일반적으로는 분할과 파일 그룹 수가 같은 것이 좋습니다. 이러한 파일 그룹은 서로 다른 디스크에 상주합니다. 그러나 이는 주로 분석이 전체 데이터 집합에 대해 수행되는 시스템에만 해당됩니다. CPU가 여러 개인 경우 SQL Server는 여러 분할을 병렬로 처리할 수 있으므로 대형의 복잡한 보고 및 분석을 처리하는 전체 시간을 크게 줄일 수 있습니다. 이 경우 병렬 처리가 가능할 뿐 아니라 분할된 테이블에서 분할을 추가 및 제외할 수도 있습니다.

파일 그룹 만들기

보다 나은 I/O 균형 조정을 위해 분할된 테이블을 여러 파일에 배치하려는 경우 적어도 하나의 파일 그룹을 만들어야 합니다. 파일 그룹은 하나 이상의 파일로 구성될 수 있으며 각 분할은 파일 그룹에 매핑되어야 합니다. 단일 파일 그룹이 여러 개의 분할에 사용될 수 있지만, 백업을 더욱 세밀하게 제어하는 등 데이터를 보다 효율적으로 관리하려면 관련이 있거나 논리적으로 그룹화된 데이터만이 동일한 파일 그룹에 상주하도록 분할된 테이블을 디자인해야 합니다. ALTER DATABASE를 사용하면 논리 파일 그룹 이름을 추가한 다음 파일을 추가할 수 있습니다. 2003Q3이라는 파일 그룹을 AdventureWorks 데이터베이스에 대해 만들려면 다음과 같은 방식으로 ALTER DATABASE를 사용합니다.

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

파일 그룹이 만들어지면 ALTER DATABASE를 사용하여 파일 그룹에 파일을 추가합니다.

ALTER DATABASE AdventureWorks
ADD FILE
(NAME = N'2003Q3',
FILENAME = N'C:\AdventureWorks\2003Q3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]

CREATE TABLE의 ON 절에서 파일 그룹을 지정하여 파일에서 테이블을 만들 수 있습니다. 그러나 테이블을 분할해야 다중 파일 그룹에서 테이블을 만들 수 있습니다. 단일 파일 그룹에서 테이블을 만들려면 CREATE TABLE의 ON 절을 사용합니다. 분할된 테이블을 만들려면 먼저 분할에 대해 실질적으로 작동하는 메커니즘이 있어야 합니다. 분할 조건은 분할 함수의 형태로 테이블과는 논리적으로 구분됩니다. 이 분할 함수는 테이블과는 별개의 정의로 존재하며, 여러 개체가 분할 함수를 사용할 수 있으므로 이러한 실제적 구분이 도움이 됩니다. 그러므로 테이블을 분할하는 첫 번째 단계는 분할 함수를 만드는 것입니다.

범위 분할을 위한 분할 함수 만들기

범위 분할은 경계 조건으로 정의해야 합니다. 또한, 테이블이 CHECK 제약 조건을 통해 제한되어 있다 하더라도 범위 양쪽의 어떤 값도 지울 수 없습니다. 데이터를 주기적으로 테이블에 추가해 넣을 수 있도록 하려면 비어 있는 마지막 분할이 필요합니다.

범위 분할에서는 먼저 경계 지점을 정의합니다. 5개 분할에 대해 4개의 경계 지점 값을 정의하고 각 값이 첫 번째(LEFT) 분할보다 높은 경계인지 아니면 두 번째(RIGHT) 분할보다 낮은 경계인지를 지정합니다. LEFT 또는 RIGHT의 지정에 따라 하나의 분할은 항상 비어 있습니다. 분할에 명시적으로 정의된 경계 지점이 없기 때문입니다.

특히 분할 함수의 첫 번째 값 또는 경계 조건이 '20001001'이면 경계에 있는 분할의 값은 다음과 같습니다.

LEFT의 경우
첫 번째 분할의 모든 데이터는 '20001001'보다 작거나 같음
두 번째 분할의 모든 데이터는 '20001001'보다 큼
RIGHT의 경우
첫 번째 분할의 모든 데이터는 '20001001'보다 작음
두 번째 분할의 모든 데이터는 '20001001'보다 크거나 같음
범위 분할은 datetime 데이터에서 정의되므로 함축된 의미를 염두에 두어야 합니다. datetime 사용에는 특별한 의미가 함축되어 있습니다. 즉, 항상 날짜와 시간을 모두 사용한다는 것입니다. 시간에 대해 지정된 값이 없는 날짜는 오전 12시 정각의 "0"인 시간을 의미합니다. 이러한 데이터 형식에 LEFT가 사용되면 날짜가 10월 1일 오전 12시 정각인 데이터는 첫 번째 분할에 배치되고 10월의 나머지 데이터는 두 번째 분할에 배치됩니다. 논리적으로 시작 값에 RIGHT를 사용하고 끝 값에 LEFT를 사용하는 것이 가장 좋습니다. 이 세 절을 통해 논리적으로 동일한 분할 구조를 만듭니다.

RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')

-또는-

RANGE RIGHT FOR VALUES ('20001001 00:00:00.000',
'20010101 00:00:00.000',
'20010401 00:00:00.000',
'20010701 00:00:00.000')

-또는-

RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')

참고 datetime 데이터 형식을 사용하면 코드가 약간 더 복잡해지지만 경계 조건을 정확하게 설정해야 합니다. RIGHT를 사용하는 경우에는 기본 시간이 오전 12:00:00.000이므로 매우 단순합니다. LEFT의 경우에 복잡해지는 이유는 datetime 데이터 형식의 정밀도 때문입니다. 23:59:59.997을 선택해야 하는 이유는 datetime 데이터가 밀리초 단위의 정밀도를 보장하지 않기 때문입니다. 대신 datetime 데이터의 정밀도는 3.33밀리초 이내입니다. 23:59:59.999의 경우 정확하게 일치하는 시각은 사용할 수 없으며, 대신 값이 가장 가까운 시각인 다음날 오전 12:00:00.000으로 반올림됩니다. 이 반올림으로 인해 경계는 올바르게 정의되지 않습니다. datetime 데이터의 경우 특수하게 제공되는 밀리초 값을 사용할 때는 주의해야 합니다.
참고 분할 함수는 분할 함수 정의의 일부로 함수도 허용합니다. DATEADD(ms,-3,'20010101')를 사용함으로써 '20001231 23:59:59.997'을 통한 명시적 시간 정의 작업을 대신할 수 있습니다.
자세한 내용은 SQL Server 온라인 설명서의 Transact-SQL 참조에서 "날짜 및 시간"을 참조하십시오.

Orders 데이터 중 1/4을 각각 하나의 분기를 나타내는 네 개의 활성 분할에 저장하고 나중에 사용하기 위해 다섯 번째 분할(분할된 테이블 내외부로 데이터를 이동하는 자리 표시자)을 만들려면 LEFT 분할 함수와 4개의 경계 조건을 사용합니다.

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')

여기서 4개의 정의된 경계 지점이 5개의 분할을 만듭니다. 다음과 같이 집합을 검토하여 이 분할 함수에 의해 만들어진 데이터 집합을 확인하십시오.

경계 지점 '20000930 23:59:59.997'을 LEFT로 지정하는 경우(패턴 설정):
맨 왼쪽 분할에는 '20000930 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
경계 지점 '20001231 23:59:59.997'의 경우:
두 번째 분할에는 '20000930 23:59:59.997'보다 크고 '20001231 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
경계 지점 '20010331 23:59:59.997'의 경우:
세 번째 분할에는 '20001231 23:59:59.997'보다 크고 '20010331 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
경계 지점 '20010630 23:59:59.997'의 경우:
네 번째 분할에는 '20010331 23:59:59.997'보다 크고 '20010630 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
마지막으로 다섯 번째 분할에는 '20010630 23:59:59.997'보다 큰 모든 값이 포함됩니다.

분할 스키마 만들기

분할 함수를 만든 후에는 해당 함수를 분할 스키마와 연결하여 분할을 특정 파일 그룹에 지정할 수 있습니다. 분할 스키마를 정의할 때는 모든 분할에 대해 파일 그룹 이름을 지정해야 합니다. 동일한 파일 그룹에 여러 개의 분할이 있는 경우에도 마찬가지입니다. 앞서 만든 범위 분할(OrderDateRangePFN)의 경우에는 5개의 분할이 있으며 마지막의 빈 분할은 PRIMARY 파일 그룹에서 만들어집니다. 이 분할에는 데이터가 포함되지 않으므로 이를 위한 특수한 위치는 필요하지 않습니다.

CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])
참고 모든 분할이 같은 파일 그룹에 있으면 다음과 같이 보다 단순한 구문을 사용할 수 있습니다.
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
ALL TO ([PRIMARY])

분할된 테이블 만들기

분할 함수(논리 구조) 및 분할 스키마(실제 구조)를 정의하면 테이블을 만들어 이러한 함수와 스키마를 활용할 수 있습니다. 테이블은 사용할 스키마를 정의하며 스키마는 함수를 정의합니다. 이 세 가지를 모두 결합하려면 분할 함수를 적용할 열을 지정해야 합니다. 범위 분할은 항상 분할 함수 내에서 정의된 경계 조건의 데이터 형식과 일치하는 테이블의 정확히 한 열에 매핑됩니다. 또한 테이블이 음의 무한대에서 양의 무한대가 아니라 구체적으로 데이터 집합을 제한하는 경우 CHECK 제약 조건 또한 추가해야 합니다.

CREATE TABLE [dbo].[OrdersRange]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL ,
[ModifiedDate] [datetime] NULL ,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] <= '20040630 11:59:59.997'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO

인덱스 만들기: 분할 여부 지정

기본적으로 분할된 테이블에서 만들어진 인덱스 역시 동일한 분할 스키마 및 분할 열을 사용합니다. 이 사항이 적용되는 경우 인덱스는 테이블에 맞춰집니다. 이 작업은 필수적인 요구 사항은 아니지만 테이블과 해당 인덱스를 맞추면 관리 작업이 보다 쉬워집니다. 이는 슬라이딩 윈도우(Sliding-Window) 시나리오에서 더욱 그러합니다.

예를 들어 고유한 인덱스를 만들려면 분할 열은 키 열 중 하나여야 합니다. 이를 통해 적절한 분할을 확인할 수 있으므로 인덱스를 고유하게 만들 수 있습니다. 그러므로 한 열에서 테이블을 분할하고 다른 열에서 고유한 인덱스를 만들어야 하는 경우에는 테이블과 인덱스를 맞출 수 없습니다. 이 경우 인덱스는 고유한 열에서 분할할 수 있습니다. 이 열이 다중 열의 고유한 키인 경우에는 키 열 중 어떤 열이라도 가능합니다. 또는 인덱스를 분할하지 않을 수도 있습니다. 분할된 테이블에서 데이터를 추가 및 제외할 때는 이 인덱스를 삭제하고 다시 만들어야 합니다.

참고 테이블에 기존 데이터를 로드하고 즉시 인덱스를 추가하려는 경우, 분할되지 않고 인덱싱되지 않은 테이블로 로드한 후에 인덱스를 만들어 데이터를 분할하면 성능이 향상되는 경우가 많습니다. 분할 스키마에 클러스터된 인덱스를 정의하면 로드 후에 효과적으로 테이블을 분할할 수 있습니다. 이는 또한 기존 테이블을 분할하는 훌륭한 방법이기도 합니다. 분할되지 않은 테이블과 같은 테이블을 만들고 클러스터된 인덱스를 클러스터된 분할 인덱스로 만들려면 CREATE TABLE의 ON 절을 단일 파일 그룹 대상으로 대체합니다. 그런 다음 데이터가 로드된 후에 분할 스키마에 클러스터된 인덱스를 만듭니다.

전체 통합: 사례 연구

분할과 관련된 개념, 이점 및 코드 샘플을 살펴보았다면 해당 프로세스를 잘 이해하셨을 것입니다. 그러나 각 단계에서 사용 가능한 특정 설정 및 옵션이 있으며 일부 경우에는 다양한 조건을 충족해야 합니다. 이 섹션에서는 이러한 모든 것을 통합하는 방법을 알아봅니다.

범위 분할: 판매 데이터

판매 데이터는 용도가 다양합니다. 당월의 데이터는 거래 데이터이며 전월의 데이터는 보통 분석용으로 많이 쓰입니다. 분석은 보통 월별, 분기별 및/또는 연도별 데이터에 대해 이루어집니다. 여러 분석가가 동시에 다양한 대량의 데이터를 보고자 할 수 있으므로, 분석을 통해 이러한 작업을 보다 효율적으로 구분할 수 있습니다. 이 시나리오의 활성 데이터는 283개의 분기 위치로부터 들어오며 두 개의 표준 형식 ACSII 파일로 전달됩니다. 모든 파일은 매달 1일 오전 3시 이전에 중앙 파일 서버에 배치됩니다. 각 파일의 크기는 다양하지만 평균적으로 매달 약 86,000건의 판매(주문) 내역이 들어 있습니다. 각 주문은 평균 2.63 라인 항목이므로 OrderDetails 파일에는 평균 226,180개의 행이 있습니다. 매월 약 2천 5백만 개의 새 Orders 및 6천 4백만 개의 OrderDetails 행이 추가되며 기록 분석 서버는 분석용으로 2년에 해당하는 데이터를 활성 상태로 유지 관리합니다. 2년에 해당하는 데이터는 6억 개가 약간 못 되는 Orders 및 15억 개가 조금 넘는 OrderDetails 행입니다. 분석은 보통 같은 분기 내의 달 또는 전년의 같은 달과 비교함으로써 수행되므로 범위 분할이 사용됩니다. 각 범위의 경계는 월입니다.

그림 11에 설명되어 있는 단계를 사용하면 테이블은 OrderDate를 기준으로 범위 분할을 통해 분할됩니다. 이 새 서버의 요구 사항을 살펴보면 분석가들은 최대 연속 6개월간의 데이터 또는 최대 3개월간의 금년 및 전년(예: 2003년 1-3월 및 2004년 1-3월) 데이터를 집계 및 분석하려는 경향이 있습니다. 디스크 스트립을 최대화하고 대부분의 데이터 그룹화를 격리하기 위해 다중 파일 그룹이 같은 실제 디스크를 사용하지만, 파일 그룹은 디스크 경합을 줄이기 위해 6개월 단위로 오프셋됩니다. 최신 데이터는 2004년 10월분이며 283개 상점은 모두 최신 판매를 로컬에서 관리하고 있습니다. 서버는 2002년 10월부터 2004년 9월 말까지의 데이터를 보관합니다. 새로운 16중 프로세서 컴퓨터 및 SAN(Storage Area Network)을 활용하기 위해 매달의 데이터는 파일 그룹에 고유한 파일을 가지며 스트립된 미러(RAID 1+0) 디스크 집합에 상주합니다. 파일 그룹을 통한 논리 드라이브로의 실제 데이터 배치의 경우, 다음 다이어그램(그림 12)은 데이터가 월을 기준으로 상주하는 위치를 보여 줍니다.

그림 12. 분할된 테이블 Orders

각각의 12개 논리 드라이브는 RAID 1+0으로 구성되어 있으므로 Orders 및 OrderDetails 데이터에 필요한 총 디스크 수는 48개입니다. SAN은 78개의 디스크를 지원하므로 나머지 30개는 트랜잭션 로그, TempDB, 시스템 데이터베이스 및 Customers(9백만 개)와 Products(386,750개 행) 등의 기타 작은 테이블에 사용됩니다. Orders 및 OrderDetails 테이블은 동일한 경계 조건을 사용하고 디스크에서 같은 위치에 배치되며 동일한 분할 스키마를 사용합니다. 그 결과(그림 13에 있는 두 논리 드라이브 [드라이브 E:\ 및 F:\]만 고려함) Orders 및 OrderDetails의 데이터는 같은 월에 대해 같은 디스크에 상주합니다.

그림 13. 디스크 배열의 범위 배치에 대한 범위 분할

이는 복잡해 보이지만 매우 만들기 쉽습니다. 분할된 테이블 디자인에 있어서 가장 힘든 부분은 여러 소스로부터 데이터를 전달받는 것입니다. 즉, 283개의 상점에는 표준 전달 메커니즘이 있어야 합니다. 그러나 중앙 서버에는 정의할 Orders 테이블 및 OrderDetails 테이블이 하나씩밖에 없습니다. 두 테이블을 모두 분할된 테이블로 만들려면 먼저 분할 함수와 분할 스키마를 만들어야 합니다. 분할 스키마는 디스크에 대한 실제 분할 위치를 정의하므로 파일 그룹도 있어야 합니다. 이 테이블에서는 파일 그룹이 필요하므로 다음 단계는 파일 그룹을 만드는 것입니다. 모든 파일 그룹의 구문은 다음과 동일하지만 24개 파일 그룹을 모두 만들어야 합니다. 24개의 파일 그룹을 모두 만들기 위한 전체 스크립트는 RangeCaseStudyFilegroups.sql 스크립트를 참조하십시오.

참고: 적절한 드라이브 문자가 있어야 이 스크립트를 실행할 수 있습니다. 그러나 스크립트에는 단순화된 테스트를 위해 수정할 수 있는 "설정" 테이블이 들어 있습니다. 드라이브 문자/위치를 단일 드라이브로 변경하여 구문을 테스트하고 익힐 수 있습니다. 파일 크기는 GB가 아닌 MB로 지정해야 하며, 사용 가능한 디스크 공간에 따라서는 초기 크기를 보다 작게 지정해야 합니다.

SalesDB 데이터베이스에 대해 24개의 파일 및 파일 그룹이 만들어집니다. 각각은 위치, 파일 이름 및 파일 그룹 이름을 제외하고는 동일한 구문을 사용합니다.

ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBFG1File1',
FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
SIZE = 20GB,
MAXSIZE = 35GB,
FILEGROWTH = 5GB)
TO FILEGROUP [FG1]
GO

24개 파일 및 파일 그룹을 모두 만들었다면 분할 함수 및 분할 스키마를 정의할 수 있습니다. 파일 및 파일 그룹을 확인하려면 각각 sp_helpfile 및 sp_helpfilegroup을 사용하십시오.

분할 함수는 OrderDate 열에서 정의됩니다. 사용되는 데이터 형식은 datetime이며 두 테이블을 모두 이 값으로 분할하려면 두 테이블은 OrderDate를 저장해야 합니다. 두 테이블이 같은 키 값에서 분할되는 경우 사실상 분할 키 값은 중복되는 정보입니다. 그러나 이는 맞춤 기능을 활용하는 데 필요하며 대부분의 경우 이 값은 비교적 좁은 열(datetime 날짜 형식은 8바이트)입니다. 이 백서의 앞부분인 "범위 분할을 위한 분할 함수 만들기" 에서 설명한 것과 같이 함수는 첫 번째 경계 조건이 LEFT(첫 번째) 분할인 범위 분할 함수입니다.

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20021031 23:59:59.997', -- Oct 2002
'20021130 23:59:59.997', -- Nov 2002
'20021231 23:59:59.997', -- Dec 2002
'20030131 23:59:59.997', -- Jan 2003
'20030228 23:59:59.997', -- Feb 2003
'20030331 23:59:59.997', -- Mar 2003
'20030430 23:59:59.997', -- Apr 2003
'20030531 23:59:59.997', -- May 2003
'20030630 23:59:59.997', -- Jun 2003
'20030731 23:59:59.997', -- Jul 2003
'20030831 23:59:59.997', -- Aug 2003
'20030930 23:59:59.997', -- Sep 2003
'20031031 23:59:59.997', -- Oct 2003
'20031130 23:59:59.997', -- Nov 2003
'20031231 23:59:59.997', -- Dec 2003
'20040131 23:59:59.997', -- Jan 2004
'20040229 23:59:59.997', -- Feb 2004
'20040331 23:59:59.997', -- Mar 2004
'20040430 23:59:59.997', -- Apr 2004
'20040531 23:59:59.997', -- May 2004
'20040630 23:59:59.997', -- Jun 2004
'20040731 23:59:59.997', -- Jul 2004
'20040831 23:59:59.997', -- Aug 2004
'20040930 23:59:59.997') -- Sep 2004
GO

맨 왼쪽 및 맨 오른쪽 경계가 모두 포함되어 있으므로 이 분할 함수는 25개의 분할을 만듭니다. 테이블은 비어 있는 상태로 유지되는 25번째 분할을 유지 관리합니다. 제약 조건이 테이블의 데이터를 제한하므로 이 빈 분할에는 아무런 데이터도 상주하지 않기 때문에 특수한 파일 그룹은 필요하지 않습니다. 데이터 위치를 적절한 디스크로 지정하기 위해 분할 스키마를 사용하여 분할을 파일 그룹에 매핑합니다. 분할 스키마는 데이터를 포함할 24개 파일 그룹 각각에 대해 명시적 파일 그룹 이름을 사용하며 25번째의 빈 분할에 대해서는 PRIMARY 파일 그룹을 사용합니다.

CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS
PARTITION TwoYearDateRangePFN TO
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6],
[FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
[FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
[FG19],[FG20],[FG21],[FG22],[FG23],[FG24],
[PRIMARY] )
GO

기본 파일 그룹 또는 사용자 정의 파일 그룹을 분할되지 않은 테이블로 사용하거나 분할된 테이블을 만드는 데 스키마를 사용함으로써 이전 릴리스에서 지원했던 것과 동일한 구문을 사용하여 테이블을 만들 수 있습니다. 어떤 옵션이 더 적합한지는 테이블을 채우는 방법 및 만들 분할 수에 따라 달라집니다. 대개의 경우 힙을 채운 다음 클러스터된 인덱스를 빌드하는 작업을 통해 이미 인덱싱된 테이블로 로드하는 것보다 성능을 향상시킬 수 있습니다. 또한 CPU가 여러 개인 경우에는 병렬 BULK INSERT에서 테이블로 데이터를 로드한 다음 인덱스도 병렬로 빌드할 수 있습니다. Orders 테이블의 경우 일반적인 방법으로 테이블을 만든 다음 데이터를 AdventureWorks 샘플 데이터베이스에서 가져오는 INSERT SELECT 문을 통해 기존 데이터를 로드합니다. Orders 테이블을 분할된 테이블로 만들려면 테이블의 ON 절에서 분할 스키마를 지정합니다. Orders 테이블은 다음 구문을 사용하여 만듭니다.

CREATE TABLE SalesDB.[dbo].[Orders]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[TotalDue] [money] NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO

OrderDetails 테이블도 이 스키마를 사용할 것이며 OrderDate를 포함해야 하므로 OrderDetails 테이블은 다음 구문을 사용하여 만듭니다.

CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrderDetailsRangeYearCK
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[DueDate] [datetime] NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [OrderDetailsModifiedDateDFLT]
DEFAULT (getdate()),
[LineTotal] AS (([UnitPrice]*[OrderQty])),
[StockedQty] AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO

데이터를 로드하기 위한 다음 단계는 두 개의 INSERT 문을 통해 처리됩니다. 이러한 문은 복사한 데이터가 있었던 새 AdventureWorks 데이터베이스를 사용합니다. AdventureWorks 샘플 데이터베이스를 설치하여 이 데이터를 복사합니다.

INSERT dbo.[Orders]
SELECT o.[PurchaseOrderID]
, o.[EmployeeID]
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
WHERE ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001')
GO
INSERT dbo.[OrderDetails]
SELECT od.PurchaseOrderID
, od.LineNumber
, od.ProductID
, od.UnitPrice
, od.OrderQty
, od.ReceivedQty
, od.RejectedQty
, o.OrderDate
, od.DueDate
, od.ModifiedDate
FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
ON o.PurchaseOrderID = od.PurchaseOrderID
WHERE (o.[OrderDate] >= '20021001'
AND o.[OrderDate] < '20041001')
GO

데이터가 분할된 테이블로 로드되었으므로 새 기본 제공 시스템 함수를 사용하여 데이터가 상주할 분할을 결정할 수 있습니다. 다음 쿼리는 데이터가 포함된 각 분할에 대해 각 분할 내에 있는 행 수와 최소 및 최대 OrderDate 정보를 반환하므로 유용합니다. 행이 포함되지 않은 분할은 이 쿼리에 의해 반환되지 않습니다.

SELECT $partition.TwoYearDateRangePFN(o.OrderDate)
AS [Partition Number]
, min(o.OrderDate) AS [Min Order Date]
, max(o.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
SELECT $partition.TwoYearDateRangePFN(od.OrderDate)
AS [Partition Number]
, min(od.OrderDate) AS [Min Order Date]
, max(od.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO

마지막으로, 테이블을 채운 다음 클러스터된 인덱스를 빌드할 수 있습니다. 이 경우 분할 키가 두 테이블을 모두 식별하므로 클러스터된 인덱스는 기본 키에서 정의됩니다. OrderDetails의 경우에는 LineNumber를 인덱스에 추가하여 고유하게 만듭니다. 분할된 테이블에 빌드된 인덱스의 기본 동작은 같은 스키마에서 인덱스를 분할된 테이블과 맞추는 것입니다. 스키마는 지정하지 않아도 됩니다.

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO

분할 스키마를 지정하는 전체 구문은 다음과 같습니다.

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON TwoYearDateRangePScheme(OrderDate)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
ON TwoYearDateRangePScheme(OrderDate)
GO

분할된 테이블 조인

맞춘 테이블을 조인할 때 SQL Server 2005는 테이블을 한 단계 또는 여러 단계로 조인할 수 있는 옵션을 제공합니다. 개별 분할을 먼저 조인한 다음 하위 집합을 함께 추가합니다. 분할을 조인하는 방법에 관계없이 SQL Server는 일정 수준의 분할 제거가 가능한지를 항상 평가합니다.

분할 제거

다음 쿼리에서 데이터는 이전 시나리오에서 만든 Order 및 OrderDetails 테이블에서 쿼리됩니다. 쿼리는 3/4분기에서만 정보를 반환합니다. 일반적으로 3/4분기는 주문 처리가 다소 낮은 달이 포함됩니다. 그러나 2004년에는 이 기간에 주문이 가장 많았습니다. 이 경우 3/4분기에 대해 Products와 관련된 동향(주문량 및 주문 날짜)을 파악해 보고자 합니다. 맞춰진 분할 테이블을 조인할 때 분할 제거 기능을 활용하려면 각 테이블의 분할 범위를 지정해야 합니다. 이 경우 Orders 테이블의 기본 키는 OrderDate 및 OrderID의 복합 키이므로 이 두 테이블을 조인하려면 테이블 간에 OrderDate가 같아야 합니다. SARG(검색 인수)가 두 분할된 테이블에 모두 적용됩니다. 이 데이터를 검색할 쿼리는 다음과 같습니다.

SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty
FROM dbo.Orders AS o
INNER JOIN dbo.OrderDetails AS od
ON o.OrderID = od.OrderID
AND o.OrderDate = od.OrderDate
WHERE o.OrderDate >= '20040701'
AND o.OrderDate <= '20040930 11:59:59.997'
GO

그림 14와 같이 실제 또는 예상 실행 계획 출력을 검토할 때는 확인해야 할 몇 가지 주요 요소가 있습니다. 먼저, SQL Server Management Studio를 사용하는 경우 액세스 중인 테이블 중 하나를 가리키면 "예상 실행 수" 또는 "실행 수"가 표시됩니다. 이 경우 한 분기, 즉 3개월분의 데이터가 표시됩니다. 각 달에는 고유한 분할이 있으며 이 데이터를 찾기 위해 각 테이블당 한 번씩 세 번의 실행이 이루어집니다.

그림 14. 실행 수

그림 15와 같이 SQL Server는 불필요한 분할을 모두 제거하고 올바른 데이터가 들어 있는 분할만을 선택합니다. 평가되는 내용에 대해서는 인수 섹션의 PARTITION ID:([PtnIds1017]) 을 검토해 보십시오. "PtnIds1017" 식에 대해 간단하게 설명하겠습니다. 이 식은 이 쿼리에서 액세스하는 분할의 논리적 표현입니다. 실행 계획 위쪽의 상수 검색을 가리키면 VALUES(((21)), ((22)), ((23)))이라는 인수가 표시됩니다. 이는 분할 번호를 나타냅니다.

그림 15. 분할 제거

각 분할에 있는 데이터만 확인하려면 이전에 사용했던 쿼리를 약간 수정한 버전을 사용하여 분할의 새 기본 제공 시스템 함수에 액세스합니다.

여기서 분할 제거를 그래픽으로 확인할 수 있습니다. 분할된 테이블 및 인덱스에 대해, 특히 테이블 및 인덱스가 조인 중인 테이블에 맞춰져 있는 경우에는 추가적인 최적화 기술을 사용할 수 있습니다. SQL Server는 각 분할을 먼저 조인하여 다중 조인을 수행할 수 있습니다.

맞춤 테이블 미리 조인

동일한 쿼리 내에서 SQL Server는 분할을 제거할 뿐 아니라 나머지 분할 간에 조인을 개별적으로 실행하기도 합니다. 각 테이블 액세스에 대한 실행 수를 검토하고 병합 조인 관련 정보를 확인하십시오. 병합 조인을 가리키면 병합 조인이 세 번 실행되었음이 표시됩니다.

그림 16. 분할된 테이블 조인

그림 16에서는 중첩된 루프 조인이 추가로 수행됩니다. 이는 병합 조인 뒤에 수행되는 것처럼 보이지만 실제로 분할 ID는 이미 각 테이블 찾기 또는 검색으로 전달된 상태입니다. 이 마지막 조인은 데이터의 각 부분 집합이 시작 시 상수 검색 식에서 정의된 분할 ID를 따르도록 데이터의 두 부분 집합을 한 곳에서 모은 것일 뿐입니다.

슬라이딩 윈도우(Sliding-Window) 시나리오

다음 달(이 기사에서는 2004년 10월)의 데이터를 사용할 수 있는 경우에는 기존 파일 그룹을 사용하고 데이터를 추가 및 제외하기 위해 따라야 하는 특정 순서의 단계가 있습니다. 이 판매 시나리오에서 현재 FG1에 있는 데이터는 2002년 10월 데이터입니다. 이제 2004년 10월 데이터를 사용할 수 있으므로 사용 가능한 공간 및 보관 요구 사항에 따라 두 가지 옵션 중에서 선택할 수 있습니다. 테이블에서 분할을 빠르게 추가하거나 제외하려면 이러한 추가/제외 작업에서 메타데이터만을 변경해야 함을 기억하십시오. 특히 새 테이블(원본 또는 대상, 즉 가장된 분할)은 추가/제외 작업이 수행되는 것과 같은 파일 그룹에서 만들어야 합니다. 같은 파일 그룹(여기서는 FG1)을 계속 사용할 계획이라면 공간 및 보관 요구 사항을 처리할 방법을 결정해야 합니다. 테이블에 2년분의 데이터가 모두 포함되지 않는 기간을 최소화하려면 공간이 있는 경우에는 보관할 데이터(2002년 10월)를 제거하지 않고도 최신 데이터(2004년 10월)를 FG1에 로드할 수 있습니다. 그러나 공간이 부족하여 당월 및 보관할 달의 데이터를 모두 보관할 수 없다면 먼저 이전 분할을 빼내서 제거하거나 삭제해야 합니다.

물론 보관은 쉬운 작업이며 이미 수행 중일 수도 있습니다. 효과적으로 보관하려면 분할을 제외하기로 한 직전이 아닌 새 분할을 로드하여 교체해 넣은 후에 즉시 파일 그룹을 백업합니다. 예를 들어 RAID 배열에서 오류가 발생한 경우 데이터를 다시 빌드하거나 다시 로드하지 않고 파일 그룹을 복원할 수 있습니다. 특히 이 경우 데이터베이스를 최근에 분할했으므로 분할 구조가 안정화된 후에 전체 백업을 수행했을 수도 있습니다. 전체 데이터베이스 백업만이 유일한 방법은 아닙니다. SQL Server 2005에는 구현 가능한 다양한 백업 전략이 있으며, 이 중 대부분은 백업 및 복원을 보다 세밀하게 제어할 수 있도록 합니다. 데이터의 많은 부분은 변경되지 않으므로 개별 파일 그룹을 로드한 다음 백업할 수 있습니다. 사실 이 작업은 진행 중인 분할 전략의 일부가 되어야 합니다. 자세한 내용은 SQL Server 온라인 설명서의 "SQL Server 관리"에서 "파일 및 파일 그룹 백업"을 참조하십시오.

전략을 지정한 후에는 정확한 프로세스 및 구문을 이해해야 합니다. 구문 및 단계 수는 복잡해 보일 수 있지만 프로세스는 매달 같습니다. 동적 SQL 실행을 사용하면 다음 단계를 통해 이 프로세스를 쉽게 자동화할 수 있습니다.

추가해 넣을 분할의 Staging 테이블을 관리합니다.
제외할 분할의 두 번째 Staging 테이블을 관리합니다.
분할 테이블에서 오래된 데이터는 빼고 새 데이터를 넣습니다.
Staging 테이블을 삭제합니다.
파일 그룹을 백업합니다.
각 단계의 구문과 최적의 방법을 비롯하여 동적 SQL 실행을 통해 이 프로세스를 자동화할 수 있도록 하는 참고 사항이 다음 섹션에 설명되어 있습니다.

추가해 넣을 분할의 Staging 테이블 관리

나중에 분할이 될 가장 Staging 테이블을 만듭니다. 이 Staging 테이블에는 해당 데이터를 앞으로 만들 분할에 대해 유효한 데이터로만 제한하는 제약 조건이 있어야 합니다. 성능을 향상시키려면 데이터를 제약 조건이 없고 인덱싱되지 않은 힙에 로드한 다음 테이블을 분할된 테이블로 추가해 넣기 전에 WITH CHECK 제약 조건을 추가합니다(3단계 참조).

CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [FG1]
GO

자동화: 이 테이블은 항상 최근 달이므로 쉽게 만들 수 있습니다. 프로세스 실행 시기에 따라 DATENAME(m, getdate()) 같은 기본 제공 함수를 사용하여 달을 쉽게 검색할 수 있습니다. 테이블 구조가 기존 테이블과 일치해야 하므로 매달 가장 크게 변경되는 것은 테이블 이름입니다. 그러나 매달 같은 이름을 사용할 수도 있습니다. 분할에 추가한 뒤에는 테이블이 없어도 되기 때문입니다. 데이터를 분할된 테이블에 추가한 후에도 테이블이 존재하기는 하지만 추가 작업이 완료되고 나면 Staging 테이블을 삭제할 수 있습니다. 또한 날짜 범위도 변경되어야 합니다. datetime 데이터로 작업 중이고 시간 저장 방법에 대한 반올림 문제점이 있기 때문에 적절한 밀리초 값을 프로그래밍 방식으로 결정할 수 있어야 합니다. 월말에 대한 마지막 datetime 값을 찾는 가장 쉬운 방법은 작업 중인 달에 한 달을 더한 다음 거기서 2 또는 3밀리초를 빼는 것입니다. 59.999는 다음 달 첫날인 .000으로 반올림되기 때문에 1밀리초만 빼면 안 됩니다. -2밀리초는 .997로 버림되고 3밀리초는 .997이 되기 때문에 2 또는 3밀리초를 빼는 것입니다. .997은 저장 가능한 유효한 값입니다. 그러면 datetime 범위에 대해 정확한 끝 값을 얻을 수 있습니다.

DECLARE @Month nchar(2),
@Year nchar(4),
@StagingDateRange nchar(10)
SELECT @Month = N'11', @Year = N'2004'
SELECT @StagingDateRange = @Year + @Month + N'01'
SELECT dateadd(ms, -2, @StagingDateRange)

테이블은 데이터가 추가 및 제외되는 파일 그룹에서 상주해야 하므로 매달 새로 만들어집니다. 작업할 적절한 파일 그룹을 결정하려면 다음 시스템 테이블 쿼리를 앞서 제시되었던 $partition 함수와 결합하여 사용하십시오. 현재 제외 중인 범위 내의 날짜를 지정해야 합니다. 이는 모든 작업을 수행할 분할 및 파일 그룹입니다. 사용 중인 특정 테이블, 분할 함수 및 특정 날짜에 적용할 때는 밑줄이 그어진 섹션을 변경해야 합니다.

SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
fg.name AS FileGroupName
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
dds.destination_id = $partition.TwoYearDateRangePFN('20021001')

데이터와 함께 Staging 테이블을 로드합니다. 파일이 일정하면 이 프로세스는 BULK INSERT 문을 통해 처리됩니다.
자동화: 이 프로세스는 자동화가 가장 복잡합니다. 모든 파일이 로드되었는지 확인해야 하며, 파일을 병렬로 로드하는 것을 고려해야 합니다. 로드된 파일 및 파일의 위치를 추적하는 테이블을 통해 이 프로세스를 제어할 수 있습니다. 몇 분에 한 번씩 파일을 확인하고 새 파일을 선택한 다음 다중 대량 삽입 문을 실행하는 SQL Agent 작업을 만들 수 있습니다.

데이터가 로드되고 나면 제약 조건을 추가할 수 있습니다. 데이터를 트러스트된 상태로 만들려면 제약 조건에 WITH CHECK를 추가해야 합니다. WITH CHECK 설정은 기본값이므로 지정할 필요가 없지만 WITH NOCHECK는 사용하지 않아야 합니다.
Staging 테이블을 인덱싱합니다. 이 테이블은 분할이 될 때 속해 있을 테이블과 같은 클러스터된 인덱스를 가져야 합니다.

ALTER TABLE [OrdersOctober2004]
ADD CONSTRAINT OrdersOctober2004PK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO

자동화: 쉬운 단계입니다. 1단계의 달 및 파일 그룹 정보를 사용하여 이 클러스터된 인덱스를 만들 수 있습니다.

ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]
WITH CHECK
ADD CONSTRAINT OrdersRangeYearCK
CHECK ([OrderDate] >= '20041001'
AND [OrderDate] <= '20041031 23:59:59.997')
GO

제외될 분할의 두 번째 Staging 테이블 관리

두 번째 Staging 테이블을 만듭니다. 이는 분할을 제외될 때 해당 데이터를 보관하는 빈 테이블입니다.

CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [FG1]
GO

Staging 테이블을 인덱싱합니다. 이 테이블은 분할이 될 속해 있을 테이블과 같은 클러스터된 인덱스를 가져야 합니다. 분할은 이 테이블이 됩니다.
ALTER TABLE [OrdersOctober2002]
ADD CONSTRAINT OrdersOctober2002PK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO

분할 테이블에서 오래된 데이터 빼기 및 새 데이터 넣기

이전 데이터를 빼내 두 번째 Staging 테이블로 옮깁니다.

ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
GO

분할 함수를 변경하여 2002년 10월의 경계 지점을 제거합니다.

ALTER PARTITION FUNCTION TwoYearDateRangePFN()
MERGE RANGE ('20021031 23:59:59.997')
GO

그러면 파일 그룹과 분할 스키마 간의 연결도 제거됩니다. 특히, FG1은 더 이상 분할 스키마의 일부분이 아닙니다. 기존과 같은 24개 분할을 통해 새 데이터를 넣으므로 FG1을 분할(split)에 사용되는 다음 분할이 되는 "다음으로 사용되는" 분할이 되도록 해야 합니다.
ALTER PARTITION SCHEME TwoYearDateRangePScheme
NEXT USED [FG1]
GO
분할 함수를 변경하여 2004년 10월에 대한 새 경계 지점을 포함합니다.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()

SPLIT RANGE ('20041031 23:59:59.997')
GO

기본 테이블에 제약 조건 정의가 있는 경우 변경하여 새 데이터 범위를 허용합니다. 제약 조건을 추가하면 데이터 확인에 많은 작업이 필요할 수 있으므로 제약 조건을 삭제하고 다시 만드는 대신 날짜를 계속 확장하는 것이 좋습니다. 지금은 제약 조건이 OrdersRangeYearCK 하나만 있습니다만 이후 날짜에 대해서는 두 개의 제약 조건이 사용될 것입니다.

ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMaxOctober2004
CHECK ([OrderDate] < '20041101')
GO
ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMinNovember2002
CHECK ([OrderDate] >= '20021101')
GO
ALTER TABLE Orders
DROP CONSTRAINT OrdersRangeYearCK
GO
첫 번째 Staging 테이블에서 새 데이터를 가져와 추가합니다.
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
GO

Staging 테이블 삭제

모든 데이터는 다음 마지막 단계에서 보관되므로 Staging 데이터는 필요하지 않습니다. 이러한 테이블을 가장 빨리 제거하는 방법은 테이블을 삭제하는 것입니다.

DROP TABLE dbo.OrdersOctober2002
GO
DROP TABLE dbo.OrdersOctober2004
GO

파일 그룹 백업

마지막 단계에서 백업할 내용은 백업 전략에 따라 달라집니다. 파일 또는 파일 그룹 기반 백업 전략을 선택한 경우 파일이나 파일 그룹 백업을 수행해야 합니다. 전체 데이터베이스 기반 백업 전략을 선택한 경우에는 전체 데이터베이스 백업 또는 차등 백업을 수행할 수 있습니다.

BACKUP DATABASE SalesDB
FILEGROUP = 'FG1'
TO DISK = 'C:\SalesDB\SalesDB.bak'
GO

목록 분할: 지역 데이터

테이블에 여러 지역의 데이터가 있고 분석은 보통 한 지역 내에서 이루어지는 경우 또는 각 지역의 데이터를 주기적으로 받는 경우에는 정의된 범위 분할을 목록 형태로 사용할 수 있습니다. 즉, 각 분할을 지역의 값으로 명시적으로 정의하는 함수를 사용하는 것입니다. 예를 들어 스페인, 프랑스, 독일, 이탈리아 및 영국에 고객이 있는 스페인 회사를 가정해 봅시다. 이 회사의 판매 데이터는 항상 국가별로 분석됩니다. 그리고 테이블에는 국가당 하나씩 5개의 분할이 있습니다.

이 목록 분할을 만드는 작업은 범위의 경계에는 실제 분할 키 외의 값은 없다는 것을 제외하고는 날짜에 대한 범위 분할 만들기와 거의 동일합니다. 대신 목록 분할은 범위가 아니라 목록입니다. 목록이라고 해도 경계 조건에는 맨 왼쪽 및 맨 오른쪽이 포함되어야 합니다. 5개의 분할을 만드는 경우 분할 함수에는 4개 분할만 지정합니다. 값의 순서는 SQL Server에서 내부적으로 지정하므로 따로 지정할 필요는 없지만, 정확한 분할 번호를 결정하는 가장 논리적인 방법은 분할 값 순서를 지정한 다음 가장 큰 값을 마지막 분할(LEFT 분할 함수로 정의된 경우)을 위해 남겨 두거나 분할 값 순서를 지정하고 두 번째로 작은 값부터 시작하는 것입니다(RIGHT의 경우).

분할이 5개이므로 5개의 파일 그룹이 있어야 합니다. 이 경우 파일 그룹 이름은 저장되는 데이터 이름에 따라 지정됩니다. 스크립트 파일 RegionalRangeCaseStudyFilegroups.sql은 이 구문의 내용을 모두 보여 주는 스크립트입니다. 각 파일 그룹은 동일한 설정을 사용하여 만들어지지만 데이터 균형이 조정되지 않는 경우에는 반드시 설정이 같지 않아도 됩니다. 스페인에 대한 파일 그룹 및 파일만이 표시되어 있습니다. 추가적인 4개 파일 그룹 및 파일의 매개 변수는 같습니다. 다만 이들은 서로 다른 드라이브에 있으며 특정 국가 분할 이름이 지정됩니다.

ALTER DATABASE SalesDB
ADD FILEGROUP [Spain]
GO
ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBSpain',
FILENAME = N'C:\SalesDB\SalesDBSpain.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [Spain]
GO

다음 단계는 경계 조건으로 LEFT를 사용하여 4개의 분할만을 지정하는 함수를 만드는 것입니다. 이 경우 목록에는 영문으로 알파벳 순서상 마지막인 영국을 제외한 모든 국가가 포함됩니다.

CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
GO

이름을 따온 파일 그룹에 데이터를 넣기 위해 분할 스키마는 알파벳순으로 나열됩니다. 5개 파일 그룹은 모두 분할 스키마 구문 내에서 지정되어야 합니다.

CREATE PARTITION SCHEME [CustomersCountryPScheme]
AS
PARTITION CustomersCountryPFN
TO ([France], [Germany], [Italy], [Spain], [UK])
GO

이제 Customers 테이블을 새 CustomersCountryPScheme에서 만들 수 있습니다.

CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [char](7) NOT NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL
) ON CustomersCountryPScheme (Country)
GO

범위 분할은 범위만을 지원하는 것으로 정의되지만 목록 분할 등 다른 형식의 분할을 수행하는 방법도 제공합니다.

요약

SQL Server 2005는 분할을 통해 대형 테이블 및 인덱스를 쉽고 일관성 있게 관리하는 방법을 제공합니다. 또한 분할을 사용하여 활성 테이블 외부에서 데이터 하위 집합을 관리할 수 있습니다. 분할 스키마는 응용 프로그램으로부터 완전히 독립적이므로 이로 인해 관리가 단순화되고 성능이 향상되며 응용 프로그램 논리가 간단해집니다. 데이터에 논리적 그룹화(범위 또는 목록)가 있고 대형 쿼리가 이러한 미리 정의된 일관성 있는 범위 내에서 해당 데이터를 분석하고 들어오는 데이터 및 나가는 데이터를 관리해야 하는 경우 범위 분할을 선택하는 것이 간편합니다. 사용할 특정 범위가 없는 대량의 데이터에 대해 분석을 수행하거나 모든 쿼리가 전체 또는 대부분의 데이터에 액세스하는 경우에는 특정 배치 기술 없이 다중 파일 그룹을 사용하는 것이 보다 쉽고 성능도 향상됩니다.

이 백서의 스크립트

이 백서의 코드 샘플에서 사용되는 스크립트는 SQLServer2005PartitionedTables.zip 파일에 들어 있습니다. 다음은 압축 파일에 있는 각 파일에 대한 설명입니다.

RangeCaseStudyScript1-Filegroups.sql-범위 분할된 테이블 사례 연구에 필요한 파일 그룹 및 파일을 만드는 구문이 들어 있습니다. 이 스크립트는 수정이 가능하므로 크기가 작은 파일(GB가 아닌 MB 단위)이 있는 작은 디스크 집합에서 이 샘플을 만들 수 있습니다. 또한 INSERT...SELECT를 통해 데이터를 가져오는 코드가 있으므로 적절한 분할 함수를 통해 데이터가 배치되는 위치를 평가할 수 있습니다.

RangeCaseStudyScript2-PartitionedTable.sql-범위 분할 테이블 사례 연구와 연관된 분할 함수, 분할 스키마 및 범위 분할 테이블을 만드는 구문이 들어 있습니다. 또한 이 스크립트에는 적절한 제약 조건 및 인덱스도 포함됩니다.

RangeCaseStudyScript3-JoiningAlignedTables.sql-SQL Server가 분할 테이블에 대해 제공하는 다양한 조인 전략을 보여 주는 쿼리가 들어 있습니다.

RangeCaseStudyScript4-SlidingWindow.sql-범위 분할 테이블 사례 연구의 월별 관리와 연결된 구문 및 프로세스가 들어 있습니다. 이 스크립트에서는 데이터를 Orders 테이블 내외부로 "슬라이드"합니다. 또한 선택적으로 OrderDetails 테이블 내외부로 데이터를 이동하는 동일한 프로세스를 직접 만들 수도 있습니다. 힌트: OrderDetails에 삽입할 테이블 및 정확한 데이터 열은 RangeCaseStudyScript2에서 사용된 Insert를 참조하십시오.

RegionalRangeCaseStudyFilegroups.sql-지역별로 분할된 테이블 사례 연구에 필요한 파일 그룹 및 파일을 만드는 구문이 들어 있습니다. 사실 이것은 목록 분할 스키마를 시뮬레이션하기 위한 범위 분할입니다.

RegionalRangeCaseStudyPartitionedTable.sql-범위 분할 테이블 사례 연구와 연관된 분할 함수, 분할 스키마 및 지역별로 분할된 테이블을 만드는 구문이 들어 있습니다.

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

[출처] SQL Server 2005의 분할된 테이블 및 인덱스 |작성자 윈마스터

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

Table 변수 vs 임시 테이블 (Ver. 2000) (0)	2008.04.29
SQL Server 2005의 XQuery 소개 (0)	2008.04.29
검색결과 xml 생성하기 (0)	2008.04.29
SQL 2005에서 행번호 매기기 (0)	2008.04.29
특정 행 구간 선택 하여 쿼리 (0)	2008.04.29

PREV 1 ···5 6 7 8 9 10 11 ···16 NEXT

컴퓨토피아

2005

세로 컬럼들을 가로 컬럼으로 모으기.... pivot 쿼리

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

SQL Server 2005의 XQuery 소개

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

SQL Server 2005의 분할된 테이블 및 인덱스

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

+ Recent posts

티스토리툴바