'데이터베이스' 카테고리의 글 목록 (67 Page)

데이터베이스

MsSql 날짜 함수 2008.04.28
SQL Server 2005에서 XML 데이터 형식을 위한 성능 최적화 2008.04.28
Microsoft SQL Server 2005의 원시 XML 웹 서비스 개요 2008.04.28

MsSql 날짜 함수

2008. 4. 28. 08:27

728x90

--**********************************************************************
-- 함수(날짜열)
--**********************************************************************

함수(날짜열)
DATE구분	구분약자	DATE구분	구분약자
year	yy	week	wk
quarter	qq	hour	hh
month	mm	minute	mi
day of year	dy	second	ss
day	dd	millisecond	ms
항목	내용
GETDATE()	시스템에서 제공하는 날짜와 시간 값
산술연산	-- date + NUMBER -- date - NUMBER

--날짜는 계산할 수 있다.
--getdate()
select getdate()'현재날짜 & 시간'
--getdate() -1, +1
select getdate() - 1'어제 이 시간',
getdate() + 1'내일 이 시간'
--오늘부터 100일 전의 날짜를 검색!
select getdate() - 100
--회원테이블의 생일과 1200일째 되는 날을 검색! (Alias는 회원명, 생일, 12000일째)
select mem_name "회원명", mem_bir "생일",
getdate() + 1200 "1200일째"
from member

--**********************************************************************
-- DATEADD 함수(날짜열)
--**********************************************************************
--DATEADD (Part,n,date)/Part부분에 n만큼 더한 date
-- (DATE구분별로 모두 적용)

select getdate() '현재날짜 & 시간',
dateadd(year, 1, getdate()) '1년뒤'
--
select getdate() '현재날짜 & 시간',
dateadd(mm,-1, getdate()) '한달전'
--
select getdate()'현재날짜 & 시간',
dateadd(dd, -50, getdate()) '50일전'
--
select getdate()'현재날짜 & 시간',
dateadd(hour, 7, getdate()) '7시간 뒤'
--
select dateadd(yy, 1, '19990228') "1년후",
dateadd(yy, 2, '19990228') "2년후",
dateadd(yy, 3, '19990228') "3년후"
--
select dateadd(mm, 12, '19000228') "1년후",
dateadd(mm, 24, '19000228') "2년후",
dateadd(mm, 36, '19000228') "3년후"
--오늘부터 6개월 전의 날짜를 검색!
select dateadd(mm, -6, getdate())"6개월전의 날짜"
--회원테이블에서 구길동회원의 생일과 12000일째 되는 날을 검색!
--(DATEADD사용) (Alias는 회원명, 생일, 12000일째)
--1.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
where mem_name like '구길동%'
--2.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
where mem_name like '구길동'
--3.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
where mem_name='구길동'

--********************************************************************
-- YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
month(getdate())'month',
day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
-- (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
datediff(mm,'1999/02/15','2000/02/15')'month',
datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
datediff(mm,'1999/11/06','2000/02/15')'month',
datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
getdate()"오늘",
datediff(dd,mem_bir,getdate())"태어난지?"
from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"

--*******************************************************************
-- Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上同)
select mem_name"회원명", mem_bir"생일",
datename(dw,mem_bir)'기타上同',
datepart(dw,mem_bir)'기타上同'
from member
where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
dateadd(dd,-1,'2000-03-01') "종료일"

--*******************************************************************
-- 함수(Conversion)
--*******************************************************************
--cast, convert : 문자로 치환/number와 date를 문자로 치환.
-- /간단한 형 변환(CAST)
-- /날짜를 문자로 변환 시 여러가지 형식이 가능하므로 주로 사용하는 함수는(CONVERT)이다.
select convert(char, 123456789) result1,
cast(123456789 as char) result2
--
select convert(varchar,456789) result1,
cast(456789 as varchar) result2,
str(456789, 10) result3
/* 숫자를 바꾸는 convert함수를 썼는데 2번째줄의 경우는 '300567'이라는 6섯자를
'char(4)' 4자로 바꾸라는 것: 이것은 말이 안되므로 * 표시가 프린트됨!(주의 사항) */
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--
select convert(char,getdate(),0) result1,
cast(getdate() as char) result2
--
select convert(CHAR, GETDATE(), 109) RESULT
select convert(CHAR, GETDATE(), 111) RESUlT
select convert(CHAR(10), GETDATE(), 121) RESULT
select convert(CHAR, GETDATE(), 114) RESULT

--
select convert(char, getdate(),0)
select convert(char, getdate(),1)
select convert(char, getdate(),2)
select convert(char, getdate(),3)
select convert(char, getdate(),4)
select convert(char, getdate(),5)
select convert(char, getdate(),6)
select convert(char, getdate(),7)
select convert(char, getdate(),8)
select convert(char, getdate(),9)
select convert(char, getdate(),10)
select convert(char, getdate(),11)
select convert(char, getdate(),12)
select convert(char, getdate(),13)
select convert(char, getdate(),14)
--다음 아래는 ERR...
select convert(char, getdate(),15)
select convert(char, getdate(),16)
select convert(char, getdate(),17)
select convert(char, getdate(),18)
select convert(char, getdate(),19)
--여기까지 ERR...
select convert(char, getdate(),20)
select convert(char, getdate(),21)
select convert(char, getdate(),22)
select convert(char, getdate(),23)
select convert(char, getdate(),24)
select convert(char, getdate(),25)
--여기서 또 아래는 ERR...
select convert(char, getdate(),26)
select convert(char, getdate(),27)
select convert(char, getdate(),28)
select convert(char, getdate(),29)
select convert(char, getdate(),30)
select convert(char, getdate(),31)
select convert(char, getdate(),32)
select convert(char, getdate(),33)
select convert(char, getdate(),34)
-- .
-- .
-- .
select convert(char, getdate(),99)
--99까지 ERR
--100단위로 위와 똑같은 숫자안에 포함되어 쓰인다.
--120과 121번의 경우는 우리나라에서 많이 쓴다. 그냥 21번을 쓰기도 한다.
select convert(char, getdate(),120)
select convert(char, getdate(),121)
/* 위에서 일반숫자형을 문자로 변환할 때 변환되어지려는 값과 변환하려는 값의
자릿숫자값이 다르면 에러가 났었지만, 날짜형은 그 자릿수만큼 잘라서 출력한다! */
--다음은 그 예이다.
--1. 날짜형 변환의 예)
select convert(char(10), getdate(),121)
--2. 숫자형 변환의 예)
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--3. 문자형를 자르기 예)
select convert(char(10), 'abcdefghijklmnopqrstuvwxyz')

--122~에서부턴 형식이 맞지 않는다 ERR
select convert(char, getdate(),122)
select convert(char, getdate(),123)
select convert(char, getdate(),124)
select convert(char, getdate(),125)
:
:

--상품테이블에서 상품코드와 상품명을 연결하여 30자리로 치환하여 검색!
--Alias는 상품코드, 상품명, 치환상품명)
select prod_id 상품코드, prod_name 상품명,
convert(char(30), prod_id + prod_name) 치환상품명
from prod

--***********************************************
-- conversion
--***********************************************
--예제1) // datetime, smalldatetime, decimalzero, decimalpoint, numericzero, numericpoint

select convert(char(8), getdate(),112) result
--
select convert(datetime, '2001-01-01') DATETIME
--
select smalldatetime = convert(smalldatetime,'2001-01-01')
--
select decimalzero = convert(decimal(15),12345678912345)
--
select decimalpoint = convert(decimal(17,2),123456789012345.11)
--
select numericzero = convert(numeric,123456789012345)
select numericpoint = convert(numeric(17,2),123456789012345.11)

--예제2) // float, int, smallint, tinyint, money

select convert(float,123456789012345) float
--
select convert(float,10/3.0) float
--
select convert(int,1234567890) int
--
select convert(smallint,12345) smallint
--
select convert(tinyint, 123) tinyint
--
select convert(money,123456789012345) money

--***********************************************************************
--함수(Conversion) : 숫자로 치환
--***********************************************************************
-- 숫자로 치환 // 모양만 수치인 문자를 NUMBER로 치환
-- /모양만 수치라면 연산에는 영향이 없다.
select convert(numeric,'123456.9') result1,
cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
cast('123456.9' as decimal) result2
--*****************************************************
/* 회원테이블에서 이쁜이 회원의 회원ID(b001) 2~4문자열을 숫자형으로 치환한 후 10을 더하여
새로운 회원ID(b011)로 조합(Alias는 회원ID, 조합회원ID) */

회원ID 조합회원ID
------- ------------
b001 b011
(1 row(s) affected)

--1. 방법1
select mem_id "회원ID",
left(mem_id,1) + right(convert(char(4),right(mem_id,3) + 1010),3) "조합회원ID"
from member
where mem_name = '이쁜이'
--2. 방법2
select mem_id "회원ID",
left(mem_id,1) + right(convert(char(4),substring(mem_id,2,4)+1010),3) "조합회원ID"
from member
where mem_name = '이쁜이'
--***********************************************************************
-- 함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
'1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
convert(datetime,'20201025')" result2",
convert(datetime,'2020-10-25 10:15:20.000') " result3",
cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) + 500 치환날짜
from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
from member
where convert(char, mem_bir, 121) like '1975%'

<INPUT style="BORDER-TOP-WIDTH: 1px; PADDING-LEFT: 33px; FONT-WEIGHT: bold; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: white; BACKGROUND: #d4d4d4; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: white; WIDTH: 770px; BORDER-TOP-COLOR: white; PADDING-TOP: 7px; HEIGHT: 30px; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: white" value="MSSQL 함수 (기타함수)">

--******************************************************************
-- 함수(NULL)
--******************************************************************
/* ## 데이터를 처리할 때 NULL값의 사용은 최대한 줄여야 하지만 사용해야 할 경우가 있다.
## 학생에 대한 정보를 입력할 때 '전화번호' 속성은 전화번호가 없는 학생이 있을 수가 있다 .이런 경우에는 NULL값을 사용한다.
## NULL값은 0.1과 같은 특정한 값이 아니고 아무 것도 없는 것을 뜻한다.
## SQL에서 NULL값을 허용하지 않는 속성에 대해 NULL값으로 수정하려 한다면 에러가 발생한다. */

--null값을 찾을 때에는 is null
select buyer_name buyer_charger
from buyer
where buyer_charger is null
--null인 값을 찾을 때 '='은 성립되지 않는다.
select buyer_name buyer_charger
from buyer
where buyer_charger=null
--
select buyer_name buyer_charger
from buyer
where buyer_charger =''
--null값이 아닌 값을 찾으려 할 때 is not null
select buyer_name buyer_charger
from buyer
where buyer_charger is not null
--ISNULL(c,d) / c가 NULL값이면 d값으로 치환
--null값일 때 '이름없다' 로 출력!
select buyer_name, buyer_charger, isnull(buyer_charger, '이름없다')
from buyer
--null에다 100을 더하면 null이다.
select null + 100
--
select isnull(null,0)+100
--
select mem_name, mem_mileage, mem_mileage+100
from member
--
select mem_name, mem_mileage, mem_mileage + 100
from member
where mem_name like '[바-빟]%'
--
update member set mem_mileage = null
where mem_name like '[바-빟]%'
--
select mem_name, mem_mileage, isnull(mem_mileage,0) + 100
from member

--NULLIF(c,d) / c와 d를 비교하여 같으면 NULL을 다르면 c값을 돌려준다.
--반환값 : NULL
select nullif(123,123)
--반환값 : NULL
select nullif(1234,'1234')
--반환값 : 123
select nullif(123,1234)
--반환값 : a
select nullif('a','b')

--**************************************************
--예제)
--**************************************************
--거래처테이블에서 거래처명, 담당자 조회!
select buyer_name 거래처,buyer_charger 담당자
from buyer
--거래처 담당자 성씨가 '김'이면 null로 갱신! 하기전에 먼저 확인!
select buyer_name, buyer_charger
from buyer
where buyer_charger like '김%'
--거래처 담당자 성씨가 '김'이면 null로 갱신!
update buyer set buyer_charger=null
where buyer_charger like '김%'
--거래처 담당자 성씨가 '성'이면 SPACE로 갱신! 하기전에 먼저 확인!
select buyer_name, buyer_charger
from buyer
where buyer_charger like '성%'
--거래처 담당자 성씨가 '성'이면 SPACE로 갱신!
update buyer set buyer_charger=''
where buyer_charger like '성%'
--***********************************************************************
-- 함수 (NULL 관련)
--***********************************************************************
--is null, is not null /null값인지 아닌지 비교
--isnull(c,d)/c가 null값이면 d값으로 치환!
--nullif(c,d)/c와 d를 비교하여 같으면 null을, 다르면 c값을 돌려준다.
--***********************************************************************
--해당 컬럼이 null값 비교 조회
--1. null이 존재하는 상태로 조회
select buyer_name 거래처, buyer_charger 담당자
from buyer
--2. null을 이용 null값 비교
select buyer_name 거래처, buyer_charger 담당자
from buyer
where buyer_charger = null --▷' = null ' 대신 ' is null '을 사용해야 제대로 된 구문 !

--***********************************************************************
-- 함수(GROUP)
--***********************************************************************
--AVG(columm)/조회범위 내에 해당 컬럼들의 평균값
-- /DISTINCT : 중복된 값은 제외
-- /ALL : Default로써 모든 값을 포함(all을 쓰지 않아도 Default값으로 적용)
-- /Column명 : NULL값은 제외
-- /* : NULL값도 포함(COUNT함수만 사용)
-- isnull을 사용하여 NUll값은 '0'등으로 나오게 한다.
--
select avg(distinct prod_cost),avg(all prod_cost),
avg(prod_cost) 매입가평균
from prod
--상품테이블의 상품분류별 매입가격 평균 값
/* (집게함수 앞에 있는 것은 group by에 포함되어있어야만 한다.
그러나, group by에 있는 것이 집게함수나 그 앞에 포함되지 않아도 무관!) */
select prod_lgu, avg(prod_cost)'상품분류별 매입가격 평균'
from prod
group by prod_lgu
--상품테이블의 총 판매가격 평균값을 구하시요?
--(Alias는 상품총판매가격평균)
select prod_lgu, avg(prod_sale)'상품총판매가격평균'
from prod
group by prod_lgu
--상품테이블의 상품분류별 판매가격 평균값을 구하시요? (Alias는 상품분류, 상품분류별판매가평균)
select prod_name'상품분류', avg(prod_sale)'상품분류별판매가평균'
from prod
group by prod_name

--**************************************************************************
-- 함수(GROUP)
--**************************************************************************
--COUNT(col) / 조회 범위내 해당컬럼들의 자료수
--COUNT(*) / 선택된 자료의 수
-- NULL값까지 포함해서 갯수를 헤아린다.
--
select count(distinct prod_cost), count(all prod_cost),
count(prod_cost),count(*)
from prod
--상품테이블의 자료수
select count(*) result1, count(prod_lgu) result2
from prod
--상품테이블의 상품분류별 자료수
select prod_lgu, count(*)'상품분류별 자료의 수'
from prod
group by prod_lgu
--1.거래처테이블의 담당자를 컬럼으로 하여 count집게
-- (Alias는 "자료수(Distinct)", 자료수, 자료수(*))
--방식1
select count(distinct buyer_charger) "자료수(Distinct)",
count(buyer_charger) "자료수",
count(*) "자료수(*)"
from buyer
--방식2
select count(distinct buyer_charger) "자료수(Distinct)",
count(all buyer_charger) "자료수",
count(*) "자료수(*)"
from buyer
--2. 회원테이블의 취미종류를 count집계
-- (Alias는 취미종류)
select distinct mem_like "취미종류" from member
--3. 회원테이블의 취미별 COUNT집계 (Alias는 취미,자료수,자료수(*))
select mem_like 취미, count(mem_like) "자료수", count(*) "자료수(*)"
from member group by mem_like
--4. 회원테이블의 직업종류수를 COUNT집계(Alias는 직업종류수)
select count(distinct mem_job) "직업종류수"
from member
--5. 회원테이블의 직업종류를 집계
select distinct mem_job "직업종류" from member
--6. 회원테이블의 직업별 카운트 집계
select mem_job "직업", count(mem_job) "자료수", count(*) "자료수(*)"
from member group by mem_job
--7. 장바구니 테이블의 회원별 카운트 집계
select cart_member, count(*), count(cart_member), count(distinct cart_member)
from cart
group by cart_member

--**************************************************************************
-- 함수(GROUP)
--**************************************************************************
--MAX(col) / 조회범위 내 해당컬럼들 중 최대값
--MIN(col) /조회범위 내 해당컬럼들 중 최소값
-- 어차피 중복을 배제하나 않하나 최대값과 최소값은 같으므로 distinct를 쓰나 마나이다!
select max(distinct prod_cost), max(prod_cost),
min(distinct prod_cost), min(prod_cost)
from prod
--상품중 최고판매가겨과 최저판매가격
select max(prod_sale) 최고판매가,
min(prod_sale) 최저판매가
from prod
--상품중 거래처별 최고매입가격과 최저매입가격
select prod_buyer 거래처,
max(prod_cost) 최고매입가,
min(prod_cost) 최저매입가
from prod
group by prod_buyer
--문제)
--1. 장바구니 테이블의 회원별 최대구매수량을 검색
-- (Alias는 회워ID, 최대수량, 최소수량)
select cart_member 회원ID,
max(cart_qty) 최대수량,
min(cart_qty) 최소수량
from cart
group by cart_member
--2. 오늘이 2002년도 5월 15일이라 가정하고 장바구니 테이블에 발생될 추가주문번호를 검색?
-- (Alais는 최고치주문번호, 추가주문번호)
-- 우선, cart의 내용을 확인한다.
select * from cart
-- 그다음은, 2002년도 5월 15일을 출력하기 위해...
select * from cart where cart_no like '20020515%'
--2002년도 5월 15일의 최고치주문번호를 검색
select max(cart_no) from cart where cart_no like '20020515%'
--*************************** 중 요 *********************************
--2002년도 5월 15일의 최고치주문번호와 추가주문번호를 모두 검색!
--정수형의 범위가 정해져 있기 때문에 convert함수를 썼다.
select max(cart_no) 최고치주문번호, convert(decimal(13),max(cart_no)) + 1 추가주문번호
from cart where cart_no like '20020515%'

--**************************************************************************
-- 함수(GROUP)
--**************************************************************************
--SUM(column) / 조회범위 내 해당컬럼들의 합계
--상품테이블의 매입가의 총합계 값
select sum(distinct prod_cost), sum(prod_cost)
from prod
--상품테이블의 판매가의 총합계 값
select sum(prod_sale)'상품 판매가 총합계'
from prod
--상품테이블의 상품분류별 판매가 합계값
select prod_lgu, sum(prod_sale)'상품 분류별 판매가 합계'
from prod
group by prod_lgu
--상품입고테이블의 상품별 입고수량의 합계값
select buy_prod 상품, sum(buy_qty)'입고수량합계'
from buyprod
group by buy_prod
--문제)
--1. 장바구니테이블의 상품분류별 판매수량의 합계값
-- (Alias는 상품, 판매수량합계)
-- 장바구니테이블 모두 출력
select * from cart
--판매수량합계
select left(cart_prod,4) 상품, sum(cart_qty) 판매수량합계
from cart
group by left(cart_prod,4)
--2. 회원테이블의 회원전체의 마일리지 평균, 마일리지 합계, 최고마일리지, 최소마일리지, 인원수를 검색
-- (Alias는 마일리지평균, 마일리지합계, 최고마일리지, 최소마일리지, 인원수)
select avg(mem_mileage) 마일리지평균, sum(mem_mileage) 마일리지합계,
max(mem_mileage) 최고마일리지, min(mem_mileage) 최소마일리지,
count(*) 인원수 from member

--**************************************************************************
-- 함수(소 GROUP 분리)
--**************************************************************************
--소 GROUP / 집계함수를 제외한 select절에 기술된 column명들은 모두 group by절에 기술!
-- / group by절에 기술된 column명들은 select절에 기술되지 않아도 무방
-- / 하지만 결과를 파악하기 위해서는 select절에 기술해주는 것이 타당
-- / group by절을 기술하면 group by 절에 기술된 column값으로 1개의 table이 소group으로 나눠진다.
결과는 column값으로 sort되어서 출력된다.
--상품테이블에서 거래처, 상품분류별로 최고판매가, 최소판매가, 자료수를 검색
select * from prod
--
select prod_buyer 거래처, prod_lgu 상품분류,
max(prod_sale) 최고판매가,
min(prod_sale) 최소판매가,
count(prod_sale) 자료수
from prod
group by prod_buyer, prod_lgu
--
--1. 장바구니테이블에서 회원, 상품분류별로 구매수량평균, 구매수량합계, 자료수를 검색?
-- (Alias는 회원ID,상품분류,구매수량평균,구매수량합계,자료수)
-- (회원ID, 상품분류 순으로 sort하시요)
select * from cart
--
select cart_member 회원ID, left(cart_prod,4) 상품분류,
avg(cart_qty) 구매수량평균,
sum(cart_qty) 구매수량합계, count(cart_qty) 자료수
from cart
group by cart_member, left(cart_prod,4)
order by cart_member, left(cart_prod,4)
--
--2. 회원테이블에서 지역(주소1의 2자리),생일년도별로 마일리지평균,마일리지합계, 최고마일리지,최소마일리지,자료수를 검색?
-- (Alias는 지역,생일연도,마일리지평균,마일리지합계,최고마일리지,최소마일리지,자료수)
select * from member
--
select left(mem_add1,2) 지역, year(mem_bir) 생일연도,
avg(mem_mileage) 평균, sum(mem_mileage) 합계,
max(mem_mileage) 최대, min(mem_mileage) 최소,
count(*) 자료수
from member
group by left(mem_add1,2), year(mem_bir)

--*************************************************************************
-- 함수(system)
--*************************************************************************
--ISDATE(c) / 타당한 날짜 포맷인지 확인 : 날짜면 1, 아니면 0
--ISNUMERIC(n) / 타당한 숫자포맷인지 확인 : 숫자면 1, 아니면 0
--CASE WHEN / 연속적인 조건문(자주활용되는 함수)
-- CASE WHEN ~ THEN ~ ELSE ~ END
--
select isdate('20000101') result1,
isdate('12345678') result2,
isdate('abc') result3
--
select isnumeric(1234.5678) result1,
isnumeric('1234.5678') result2,
isnumeric('ABCDEFG') result3
--
select case when'나'='나' then'맞다'
else'아니다' end result
--
select case'나'when'철호'then'아니다'
when'너' then'아니다'
when'나' then'맞다'
else'모르겠다' end result
--
select prod_name 상품, prod_lgu 분류,
상품분류=
case when prod_lgu = 'p101' then '컴퓨터제품'
when prod_lgu = 'p102' then '전자제품'
when prod_lgu = 'p201' then '여성케주얼'
when prod_lgu = 'p202' then '남성케주얼'
when prod_lgu = 'p301' then '피혁잡화'
when prod_lgu = 'p302' then '화장품'
when prod_lgu = 'p401' then '음반/CD'
when prod_lgu = 'p402' then '도서'
when prod_lgu = 'p403' then '문구류'
else '미등록분류'
end
from prod

--10만원 초과 상품판매가 가격대를 검색
select * from prod
--
select prod_name 상품, prod_price 판매가,
case
when(100000-prod_price)>0 then'10만원미만'
when(200000-prod_price)>0 then'10만원대'
when(300000-prod_price)>0 then'20만원대'
when(400000-prod_price)>0 then'30만원대'
when(500000-prod_price)>0 then'40만원대'
when(600000-prod_price)>0 then'50만원대'
when(700000-prod_price)>0 then'60만원대'
when(800000-prod_price)>0 then'70만원대'
when(900000-prod_price)>0 then'80만원대'
when(1000000-prod_price)>0 then'90만원대'
else'100만원이상'
end'가격대'
from prod
where prod_price>100000
--문제)
--회원정보테이블의 주민등록 뒷자리(7자리 중 첫째자리)에서 성별 구분을 검색
-- (Alias는 회원명, 주민등록번호(주민1-주민),성별)
select * from member
--
select (mem_regno2)
from member
--첫번 째 방법
select mem_name 회원명, mem_regno1+'-'+mem_regno2 주민등록번호,
case when left(mem_regno2,1)=1 then'남자'
when left(mem_regno2,1)=2 then'여자'
else'알수없는성별'
end'성별'
from member
--또 다른 방법
select mem_name 회원명, mem_regno1+'-'+mem_regno2 주민등록번호,
case left(mem_regno2,1) when '1' then '남자'
when '2' then '여자'
else'알수없는성별'
end'성별'
from member

출처 : Tong - ahnz님의 데이터베이스통

[출처] MsSql 날짜 함수|작성자 필립박

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

Microsoft SQL Server 2005를 위한 최상의 XML 실행 방법 (0)	2008.04.28
IDENTITY (0)	2008.04.28
SQL Server 2005에서 XML 데이터 형식을 위한 성능 최적화 (0)	2008.04.28
Microsoft SQL Server 2005의 원시 XML 웹 서비스 개요 (0)	2008.04.28
Microsoft SQL Server 2005의 XML 옵션 (0)	2008.04.28

SQL Server 2005에서 XML 데이터 형식을 위한 성능 최적화

2008. 4. 28. 08:26

728x90

SQL Server 2005에서 XML 데이터 형식을 위한 성능 최적화

Shankar Pal, Babu Krishnaswamy, Vasili Zolotov, Leo Giakoumakis _ Microsoft Corporation

적용 대상:
SQL Server 2005

요약: 이 문서에서는 Microsoft SQL Server 2005에서 XML 데이터 형식의 쿼리 및 데이터 수정 성능을 향상시키기 위한 몇 가지 방법을 설명합니다. 이 문서를 최대한 활용하기 위해서는 SQL Server 2005의 XML 기능에 익숙해야 합니다. 배경 자료로 MSDN 기사 Microsoft SQL Server 2005에서 XML 지원 및 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법을 참조하십시오.

소개

엔터프라이즈 응용 프로그램은 반구조적 데이터 및 구조화되지 않은 데이터 모델링에 XML을 점점 더 많이 사용하고 있습니다. Microsoft SQL Server 2005는 이러한 응용 프로그램 개발을 돕기 위해 XML 데이터 처리를 위한 광범위한 지원을 제공합니다. XML 데이터는 XML 데이터 형식 열에 원시적으로 저장되어, XML 스키마의 모음에 따라 형식화되거나 형식화되지 않은 상태로 남아 있을 수 있습니다. 그리고 Last Call 및 XML 데이터 조작 언어에서 현재 부상하고 있는 W3C 권장 사항인 XQuery를 통해 세분화된 데이터 조작이 지원됩니다. 쿼리 성능을 향상시키기 위해 XML 열을 인덱스할 수 있습니다. 엔터프라이즈 응용 프로그램은 반구조적 데이터 및 구조화되지 않은 데이터를 모델링하기 위해 점점 더 많이 XML을 사용하고 있으므로 SQL Server 2005의 XML 지원을 통해 이점을 얻게 될 것입니다.

이 문서에서는 XML 데이터 형식을 사용하는 응용 프로그램의 저장소, 쿼리 및 데이터 수정을 최적화하기 위한 제안을 제공합니다. 이러한 제안은 코드 샘플을 통해 설명됩니다. XML 데이터 모델링 및 사용을 위한 최상의 실행 방법에 관한 설명은 관련 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법을 참조하십시오. 또한 매핑을 사용하는 XML 뷰 기술의 최적화에 관한 내용은 MSDN Library에서 SQLXML 성능 최적화 (영문)를 참조하십시오.

이 문서에서는 우선, 데이터베이스 설계 원리를 포함하여 XML을 사용한 데이터 모델링 지침을 살펴본 다음 응용 프로그램의 성능 최적화를 위한 쿼리 및 데이터 수정 지침을 제공합니다.

XML 데이터 형식을 이용한 데이터 모델링

XML 데이터 형식은 기업 내의 반구조적 데이터 및 구조화되지 않은 데이터에 데이터 모델링 기능을 제공합니다. XML 저장소 및 쿼리 처리의 성능은 데이터베이스 스키마 설계에 좌우되며 XML 열에서 XML 데이터 및 속성 승격의 구조 및 단위와 같은 요소를 포함합니다.

맨 처음 내려야 할 결정은 응용 프로그램에 XML 데이터 모델의 기능이 필요한지 여부입니다. 구조화된 데이터는 관계형으로 가장 잘 모델링되고 관계형 열을 가진 테이블에 저장됩니다. 문서 순서 및 포함 계층을 유지해야 하고 재귀 구조를 가진 반구조적 데이터 또는 태그 데이터를 사용하는 경우 XML 데이터 모델이 가장 적합합니다.

XML 데이터 형식 열에 구조화된 데이터를 저장하면 유용한 경우가 종종 있는데, 예를 들면 데이터가 유연한 구조를 갖거나 구조가 사전에 알려져 있지 않은 경우입니다.

이런 시나리오는 개체에 대한 메타 데이터 정보가 XML로 모델링되고 XML 데이터 형식 열에 저장되는 경우 속성 관리에서 발생합니다. 다른 구조 및 콘텐츠 모델을 사용하는 서로 다른 형식의 개체 속성이 같은 XML 열에 저장되고 서로 쿼리될 수 있습니다. 가장 자주 쿼리되는 속성은 동일한 테이블 또는 다른 테이블의 열로 승격됩니다. 승격된 속성을 인덱싱하고 쿼리할 수 있으며 쿼리 계획은 XML 열을 쿼리하는 것보다 더 단순합니다.

또는 들어오는 XML 데이터는 테이블로 분해되어 SQL 언어를 사용하여 쿼리될 수 있습니다. XML 생성이 쿼리 작업 부하의 중요한 부분인 경우 XML 데이터 형식 열에 XML 데이터의 중복된 복사본을 저장하는 것이 유용할 수 있습니다. 중복된 복사본은 XML 생성의 런타임 비용을 방지합니다.

XML 데이터 형식을 이용한 데이터 모델링에는 절대적인 규칙이 없습니다. 각 모델링 상황에서 장점과 단점을 신중하게 심사숙고해야 합니다. 그리고 형식화된 XML 열과 형식화되지 않은 XML 열 중에서의 선택 및 XML 태그가 데이터에 도입되는 방법도 마찬가지로 중요합니다. 이 섹션의 나머지 부분에 이에 대한 고려 사항과 기타 고려 사항이 논의되어 있습니다.

XML 데이터의 구조

동일한 데이터를 서로 다른 방식(예: 요소 중심, 특성 중심, 요소와 속성의 결합)으로 표시할 수 있습니다. 이 선택은 콘텐츠를 구성하는 것(요소 값) 및 메타 정보를 구성하는 것(특성 값)의 인식, 그리고 태그의 카디널리티(예: 요소의 다중 발생)에 의해 결정됩니다. 한 가지 방식으로 반구조적 데이터 및 구조화되지 않은 데이터에 XML 태그를 도입하는 것은 다른 저장 및 쿼리 처리 방법에 비해 더 효과적일 수 있습니다.

구체적인 태그 사용

일반적인 요소 이름을 사용하고 추가 특성을 이용하여 서로 다른 요소 형식들을 구별하는 것이 편리한 경우가 종종 있습니다. 하지만 이 방법을 이용하면 XML 인덱스 조회를 효율적으로 수행할 수 없기 때문에 이 방법은 쿼리에서 잘 작동하지 않습니다. (XML 인덱싱에 관한 자세한 설명은 XML 데이터 인덱싱을 참조하십시오.)

한편 의미적으로 풍부한 구체적인 요소 이름은 더 읽기 쉽고 더 효과적인 쿼리 계획을 생성하는 데 도움이 되는 태그를 제공합니다. 반면 매우 장황한 태그는 저장소 비용도 증가시킵니다. 다음 예제는 이 두 가지 측면을 보여줍니다.

예제: 일반적 태그와 구체적 태그의 비교

도서 및 DVD 정보에 XML 태그를 사용하기를 원한다고 가정해 봅시다. 한 가지 선택은, 두 가지 항목 형식을 구별하기 위해 두 개의 값 "book"과 "DVD" 중 하나를 갖는 @type 특성을 가진 <item>이라는 일반 요소를 사용하는 것입니다. 도서 및 DVD는 다음과 같이 표시될 수 있습니다.

<item type="book"><title>Writing Secure Code</title></item>
<item type="DVD"><title>The Godfather</title></item>

도서 및 DVD에 대한 경로 식은 각각 /item[@type = "book"] 및 /item[@type = "DVD"]로 작성될 수 있습니다. 한편 다음과 같이 <book> 및 <DVD> 는 더 직접적인 XML 태그입니다.

<book><title>Writing Secure Code</title></book>
<DVD><title>The Godfather</title></DVD>

이 표시는 더 간단한 경로 식 /book 및 /DVD를 제공합니다. 또한 @type 특성에서 술어가 제거되기 때문에 쿼리 계획도 더 간단하고 효과적입니다. 게다가 이 표시를 이용하면 형식화되지 않은 XML의 경우에, 주 XML 인덱스(XML 데이터 인덱싱에 관한 자세한 내용은 XML 데이터 인덱싱 참조)에 있는 행의 수가 4개( <item>, 에 한 행, @type 및 그 값에 한 행, <title> 에 한 행, 제목 값에 한 행)에서 3개( <book> 또는 <DVD>, 에 한 행, <title> 에 한 행, 제목 값에 한 행)로 줄어듭니다. 이로 인해 3개에서 2개 행으로 저장소 오버헤드가 감소되며 이는 상당한 절감입니다.

특성 중심 태그

특성의 값은 형식화된 XML 및 형식화되지 않은 XML 둘 다에 대한 주 XML 인덱스의 단일 행에 특성 태그와 함께 저장됩니다. 비교해 보면, 형식화되지 않은 XML의 단순한 값 요소의 값은 요소 태그와 별개의 행에 저장됩니다. 따라서 형식화되지 않은 XML 내의 특성 값을 사용함으로써 저장소가 더 적게 필요합니다.

더구나 특성의 값이 주 XML 인덱스에 있는 태그로서 동일한 행에서 구해지기 때문에 술어의 평가가 더 효과적이므로, 값의 또 다른 행에 액세스할 필요가 없습니다. 이 내용은 다음 예제에 예시되어 있습니다.

예제: 특성 중심 태그

위 예제에서는 다음과 같이 제목이 요소 대신 특성으로 모델링될 수 있습니다

형식화되지 않은 XML의 경우, 이 방식을 이용하면 주 XML 인덱스에 있는 행의 수를 3개(<book>, <title>에 각각 한 행씩, 제목 값에 한 행)에서 2개(<book>에 한 행, @title 특성에 한 행)로 줄일 수 있습니다. DVD의 경우에도 유사합니다.

경로 식 /DVD[title = "The Godfather"]는 제목이 제목에 대한 요소 중심 태그가 있는 "The Godfather"인 DVD를 찾습니다. 특성 중심 태그의 경우에는 동일한 쿼리가 /DVD[@title = "The Godfather"]로 작성되고 쿼리에 필요한 JOIN 수가 하나 줄어듭니다.

형식화되거나 형식화되지 않은 XML

형식화되지 않은XML(즉, XML 데이터가 XML 스키마에 의해 설명되지 않음)의 요소 및 특성 값은 내부적으로 유니코드 문자열로 저장됩니다. 형식화되지 않은XML에서 작업하는 경우에는 데이터를 적합한 형식으로 변환해야 합니다. 예를 들어, 경로 식 (/book/price)[1] > 19.99 가 평가될 때 <price> 의 문자열 값이 숫자 비교를 위해 십진수로 변환됩니다. 이와 같은 비교가 많으면 비용이 많이 들게 됩니다. XML 스키마가 제공하는 형식 정보는 여러 방식으로 데이터베이스 엔진에 의해 사용됩니다. 삽입 및 업데이트된 XML 데이터는 XML 스키마를 준수하는지 검증된 다음 이진 표시("XML blob")로 저장됩니다. 요소 및 특성 값은 XML 인스턴스 내에 형식화된 값으로 저장됩니다. 이렇게 하면 XML blob이 상응하는 텍스트 형식보다 더 효율적으로 구문 분석될 수 있습니다. 형식화된 값은 XML 인덱스에 저장되며 데이터 변환이 제거될 때마다 인덱스 사용을 허용합니다. 쿼리 컴파일은 형식 정보를 사용하여 XQuery 식 및 데이터 수정 문의 정적 형식 정확성을 검사합니다. 형식 불일치 오류는 컴파일 시에 탐지되며 명시적 형식 캐스트를 사용함으로써 피할 수 있습니다.

또한 형식 추론에 기반한 쿼리 최적화가 수행됩니다(예: <book> 의 <price> 가 xs:decimal 형식인 경우, (/book/price)[1] 의 xs:decimal로의 변환이 제거됩니다). 이는 XML 인덱스 조회에 긍정적인 효과를 줄 수 있습니다. (/book/price)[1] < 19.99와 같은 범위 술어는 VALUE 형식의 보조 XML 인덱스에 범위 스캔을 수행합니다. (XML 데이터 인덱싱에 관한 자세한 내용은 이 문서의 XML 데이터 인덱싱 XML 데이터 인덱싱을 참조하십시오.) 형식화되지 않은 XML에 필요한 데이터 변환은 이러한 범위 스캔을 방지합니다. 또한 XML 스키마가 단일 <price> 요소를 지정하고 각 XML 인스턴스에서 단일 <book> 요소만을 허용하는 경우에는(/book/price)[1]의 서수 [1]이 필요하지 않습니다. 형식화된 XML에는 XML 데이터 삽입 및 수정 동안 유효성 검사가 필요합니다. 유효성 검사의 비용은 결코 사소하지 않으며 스키마 정의의 복잡성 및 XML 데이터에서 발생하는 태그의 수와 같은 요소에 따라 다릅니다.

속성 승격

쿼리 처리 동안 문서 순서 및 포함 계층과 같은 구조 정보가 XML 인스턴스에 보존됩니다. 따라서 쿼리 계획이 복잡해지는 경향이 있습니다. 스칼라 값을 XML 열에서 같은 테이블이나 다른 테이블의 관계형 열로 승격하고 이 열에 대해 직접적으로 쿼리를 작성함으로써 일부 쿼리에 대한 계획이 단순화될 수 있습니다. 승격된 속성은 인덱스할 수 있습니다. 속성 값을 구체화하고 인덱싱하면, 미리 계산된 값이 쿼리 성능을 높이는 방식과 같은 방식으로 XML 열에서 XQuery를 사용할 때보다 성능이 더 좋아집니다.

속성 값을 검색하거나 속성 값을 필터로 사용하여 해당 XML blob을 검색하는 경우 속성 승격은 성능을 향상시킵니다. 후자의 경우 속성 값의 선택성이 중요한 요소입니다.

단일값 속성은 계산 열과 동일한 테이블의 열로 승격할 수 있습니다. 단일값 속성 및 다중 값 속성은 둘 다 다른 테이블의 열로 승격할 수 있고 트리거를 사용하여 유지 관리할 수 있습니다. 아래에 이 두 가지 속성 승격 방법이 설명되어 있습니다.

계산 열 사용

XML 데이터 형식 메서드를 사용하여 스칼라 값을 추출하기 위해 먼저 Transact-SQL 사용자 정의 함수가 생성됩니다. 그런 다음 사용자 정의 함수에서 정의한 계산 열이 테이블에 추가됩니다. 각각의 승격된 속성에 대해 이 두 단계가 반복되고 필요에 따라 관계형 인덱스가 이들 열에 생성됩니다.

XML 열에 대한 XQuery 식을 계산 열을 사용하는 SQL 문으로 다시 작성해야 하며, XML 인스턴스가 일치하는 행에서 검색됩니다. 쿼리 비용을 기준으로 쿼리 최적화 프로그램이 계산 열의 인덱스를 선택합니다. 계산 열은 미리 계산되므로, 승격된 속성은 XML 열을 직접 쿼리하는 것보다 더 빠른 성능을 낳습니다.

열이 SELECT 목록에서만 사용되고 술어 평가에 사용되지 않는 경우 계산 열의 인덱싱을 피할 수 있습니다. 이 경우 성능상의 이점을 위해 계산 열의 지속성만으로도 충분합니다. 계산 열이 인덱스될 때, 계산 열 식이 정확하지 않거나 명확하지 않은 경우 계산 열이 계속 유지되어야 합니다.

아래 예제는 속성 승격을 위한 계산 열의 사용을 보여줍니다.

예제: 속성 승격을 위한 계산 열 사용

ISBN을 계산 열로 승격하는 것이 시간과 노력을 들일 만한 일이 되도록, 지정된 ISBN에 따라 책을 조회하는 일반적인 작업 부하를 가정해 봅시다. 다음과 같이 ISBN을 검색하는 사용자 정의 함수를 정의합니다.

CREATE FUNCTION udf_get_book_ISBN (@xData xml) RETURNS varchar(20)
WITH SCHEMABINDING
BEGIN
RETURN @xData.value('(/book/@ISBN)[1]', 'varchar(20)')
END

ISBN을 위해 계산 열을 docs 테이블에 추가합니다.

CREATE TABLE docs (id int PRIMARY KEY, xCol XML)
ALTER TABLE docs ADD ISBN AS dbo.udf_get_book_ISBN(xCol)

ISBN 열에 클러스터되지 않은 인덱스를 만듭니다.

CREATE INDEX COMPUTED_IDX ON docs (ISBN)

아래 쿼리를

SELECT xCol
FROM docs
WHERE xCol.exist ('/book/@ISBN[. = "0-2016-3361-2"]') = 1

계산 열을 사용하기 위해 다음과 같이 다시 작성합니다.

SELECT xCol
FROM docs
WHERE ISBN = '0-2016-3361-2'

ISBN 값의 추출이 미리 처리되어 있기 때문에 다시 작성한 쿼리는 더 간단한 쿼리 계획을 생성합니다.

속성 테이블 사용

별도의 속성 테이블은 유지 관리를 위해 삽입, 삭제 및 업데이트 트리거 설정이 필요합니다. 이 테이블은 속성 테이블의 각 행이 속성 값(피벗되지 않은 표시)을 포함하고 있는 경우의 다중값 속성에 적합합니다. 속성 테이블의 생성 및 유지 관리를 보여주는 예제를 관련 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에서 찾을 수 있습니다.

형제 항목의 관련 순서가 응용 프로그램에 중요한 경우 속성 테이블에 시퀀스 번호 열이 요구됩니다. 하지만 이로 인해, XML 하위 트리 삽입 및 제거의 경우 속성 테이블 유지 관리가 복잡해집니다.

편의상 단일값 속성 열을 테이블에 추가할 수 있습니다. 이렇게 하면 열에서 중복이 발생하지만 두 속성이 모두 필요한 경우 JOIN이 제거됩니다.

승격된 속성의 최대 카디널리티 N이 작고 사전에 알려진 경우, 별도의 속성 테이블 대신 N개의 계산 열을 생성하고 쿼리 프로세서가 이러한 열을 유지 관리하게 하는 것이 편리할 것입니다.

XML 데이터 대량 로드

SQL Server의 대량 로드 기능을 사용하여 XML 데이터를 XML 데이터 형식 열에 대량 로드할 수 있습니다. 여기에는 BCP IN, BULK INSERT 및 OPENROWSET 메서드가 포함됩니다.

BCP 입력은 가능한 경우 XML 데이터의 중간 복사본을 회피하도록 최적화되었습니다. 따라서 XML 열에 행 또는 열 제약 조건이 없는 경우 BCP가 세 개의 대체 방법들 중에서 최상의 성능을 갖습니다.

OpenRowset 사용

OPENROWSET는 파일에서 XML 데이터를 XML 열, 변수 및 매개 변수로 로드하는 편리한 방법입니다. 변수 또는 매개 변수에서 XML 데이터를 여러 번 쿼리하면 파일에서 같은 횟수만큼 데이터를 가져올 수 있습니다. 아래 예제에서와 같이 XML 데이터를 XML 변수로 한 번 읽어 들여 이를 여러 번 쿼리하는 것이 더 낫습니다.

예제: OPENROWSET의 출력 쿼리

다음 쿼리에서 XML 데이터는 파일에서 테이블 식 XmlFile의 [Contents] 열로 읽혀 들어갑니다. nodes() 메서드는 XML 인스턴스에서 <author> 요소를 찾습니다. 각 value() 메서드는 파일에서 XML 데이터를 매번 로드하는, <author> 요소에 관련된 경로 식을 평가합니다.

WITH XmlFile ([Contents]) AS (
SELECT CONVERT (XML, [BulkColumn])
FROM OPENROWSET (BULK N'C:\Filedata.xml', SINGLE_BLOB) AS [XmlData]
)
SELECT nref.value('first-name[1]', 'nvarchar(32)') FirstName,
nref.value('last-name[1]', 'nvarchar(32)') LastName
FROM [XmlFile] CROSS APPLY [Contents].nodes('//author') AS p(nref)

다시 작성된 아래 쿼리에 나타난 대로 성능 향상을 위해 파일 데이터를 한 번만 로드하면 됩니다. 즉, 파일 내용이 XML 변수 @xmlData로 한 번만 읽혀 들어 가고 SELECT 문에서 다시 사용됩니다.

DECLARE @xmlData XML;
SELECT @xmlData = CONVERT (XML, [BulkColumn])
FROM OPENROWSET (BULK N'C:\Filedata.xml', SINGLE_BLOB) AS [XmlData];

SELECT nref.value('first-name[1]', 'nvarchar(32)') FirstName,
nref.value('last-name[1]', 'nvarchar(32)') LastName
FROM @xmlData.nodes ('//author') AS p(nref)

XML 데이터 인덱싱

XML 인덱스

XML 열로의 세분화된 쿼리의 경우 XML 열에 주 XML 인덱스를 만드는 것이 좋습니다. 주 XML 인덱스는 형식화된 XML 열 및 형식화되지 않은 XML 열 모두에서 생성될 수 있고 전체 XML 열 내의 모든 경로 및 값을 인덱스합니다. 주 XML 인덱스는 XML 열에 XML 인스턴스의 분할된(shredded) 표시를 기준으로 B+트리를 만듭니다. XML 열에 XML blob뿐만 아니라 이 B+트리가 만들어지고 그 크기는 XML 열의 XML blob을 모두 합친 크기보다 더 큽니다. B+트리는 XML 데이터 형식 메서드를 이용하여 XML 데이터를 쿼리하는 데 사용됩니다. XML blob은 전체 XML blobSELECT * FROM docs와 같은 기본 테이블에서 검색되는 경우를 최적화하는 데 사용됩니다. 이렇게 하면 더 작은 크기와 직렬화 비용 덕분에 주 XML 인덱스에서 XML 콘텐츠를 직렬화할 때보다 더 빨라집니다.

보조 XML 인덱스는 쿼리 최적화 프로그램이 더 나은 계획을 제안하도록 추가 옵션을 제공합니다. 응용 프로그램은 PATH, PROPERTY, VALUE 형식의 보조 XML 인덱스를 사용함으로써 성능이 보다 더 향상될 수 있습니다.

PATH 인덱스는 XML 데이터 형식에서 /book[@ISBN = "0-2016-3361-2"]와 같은 경로 식이 발생하는 경우에 항상 유용합니다. 긴 경로 식일수록 그 이점은 더 큽니다. PATH 인덱스는 전반적으로 상당한 속도 증가를 제공합니다.

PROPERTY 인덱스는 XML 인스턴스의 여러 속성이 SELECT 문 내에서 검색되는 경우에 유용합니다. 각 XML 인스턴스의 속성을 함께 클러스터링하면 성능이 보다 더 향상될 수 있습니다.

VALUE 인덱스는 하위 항목 축(예: //-operator) 및 와일드카드(예: /book[@* = "novel"])를 포함하는 경로 식에 유용합니다. 하나 이상의 보조 XML 인덱스가 도움이 되는지 여부를 결정하기 위해서는 쿼리 작업 부하의 분석이 필요합니다. 또한 XML 데이터 인덱스의 전체적인 혜택을 측정하는 데 인덱스 유지 관리 비용을 고려해야 합니다.

대부분의 응용 프로그램은 예상되는 쿼리 작업 부하를 알고 있으며 쿼리에서 발생하는 경로만을 인덱싱하여 이점을 얻을 수 있습니다. 이 경로는 이 문서의 후반부 "속성 승격"에 설명된 대로 속성으로 승격될 수 있습니다.

부분적 XML 업데이트

XML 데이터 형식의 인플레이스 업데이트는 세분화된 데이터 수정 동안 상당한 성능 향상을 제공합니다. 새 상태(데이터 수정 후) 및 이전 상태(데이터 수정 전) 간의 차이점이 처리되고 XML 열 저장소를 비롯하여 주 XML 인덱스에 적용됩니다. 또한 주 XML 인덱스의 변경 사항이 보조 XML 인덱스에도 전파됩니다. 이렇게 하면 저장소에서 업데이트되는 데이터의 양이 더 적고 그에 따라 트랜잭션 로그가 절감되므로 성능 혜택을 얻을 수 있습니다. 대부분의 경우 이러한 절감은 새 상태 및 이전 상태를 비교하는 비용을 상쇄합니다.

이 경우에 가장 적합한 시나리오는 XML DML에서 명령문의 "값 대체"를 이용한 특성 또는 요소 값의 수정입니다. 이렇게 하려면 XML 열에 있는 각각의 주 XML 인덱스 및 보조 XML 인덱스에서 행을 하나씩 업데이트해야 합니다. 또한 이 업데이트는 업데이트된 특성 또는 요소를 포함하는 XML blob의 디스크에 있는 페이지에 로컬입니다. 물론 이전 값을 큰 값으로 대체하면 새 디스크 페이지가 기록됩니다. 다음은 업데이트가 매우 효율적인 경우의 예제입니다.

예제: 특성 값 업데이트

이 예제에 나타난 대로 <book> 의 <price> 수정은 XML 인스턴스 및 XML 인덱스의 인플레이스 업데이트를 수행합니다.

UPDATE docs
SET xCol.modify ('replace value of (/book/price/text())[1] with 29.99')

특성, 요소 또는 하위 트리 삽입의 경우, 새로 삽입된 노드 및 이 노드를 따르는 형제 항목이 해당 하위 트리와 함께 업데이트되거나 삽입됩니다. XML blob에서도 이와 비슷한 변경이 발생합니다. 노드 삭제의 경우에도 이와 유사하며 삭제 지점 위의 형제 항목이 업데이트됩니다.

최악의 시나리오는 노드를 XML 데이터 형식 인스턴스의 가장 왼쪽 조각으로서 삽입하거나 루트 요소의 가장 왼쪽 자식(child)을 삽입하는 동안 발생합니다. 이렇게 하면 전체 XML 인스턴스가 업데이트됩니다. 이 상황은 노드를 XML 인스턴스의 가장 오른쪽 조각 또는 루트 요소의 가장 오른쪽 자식(child)으로 삽입함으로써 피할 수 있습니다.

루트 요소의 가장 왼쪽 조각 또는 가장 왼쪽 자식(child)의 삭제에도 이와 비슷한 비용이 듭니다. 요소가 자주 삽입되고 삭제되는 경우에는 요소를 가장 오른쪽 조각 또는 루트 요소의 가장 오른쪽 자식(child)으로 삽입하는 편이 더 낫습니다. 다음 예제는 비용이 많이 드는 경우를 보여줍니다.

예제: 비용이 많이 드는 업데이트

<publisher> 요소가 <book> 요소의 가장 왼쪽 자식(child)으로 삽입되어 <book> 의 모든 하위 요소가 업데이트됩니다.

UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher>
before (/book/title)[1]')

<publisher> 를 <book> 의 가장 오른쪽 자식(child)으로 삽입하면 더 효율적입니다.

UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher> into (/book)[1]')

XML 스키마 제약 조건은 삽입 지점을 결정할 수 있고 새 노드를 가장 오른쪽의 가능한 위치에 삽입하면 최상의 성능을 얻을 수 있습니다.

형식의 결합으로 인한 최적화 저해

암시적 캐스트를 요구하는 union 형식의 값은, 보조 XML 인덱스가 경로를 일치시키는 데 사용될 수 있는 경우에도 값에 대한 보조 XML 인덱스의 조회를 막습니다. 이와 같이 union 형식의 값은 범위 스캔이 VALUE 보조 XML 인덱스에서 발생하지 못하게 합니다. 자세한 내용은 이 문서의 "범위 제약 조건"을 참조하십시오. 같은 추론이 <xs:anyAttribute>에도 적용됩니다.

모델 그룹(<xs:choice> 및 <xs:all> ), 대체 그룹 및 와일드카드 섹션(xs:any)은 컨텐츠 모델로서 형식의 결합을 갖습니다. 정확한 형식이 쿼리 컴파일 및 최적화 동안 알려지지 않는 경우 런타임 형식 캐스트가 그 값에 대해 작업하기 위해 필요할 수 있습니다. 이렇게 하면 쿼리 속도가 느려집니다. 따라서 성능상의 이유로 가능한 경우 이러한 XML 스키마 구조 및 데이터 형식을 피해야 합니다.

요소의 단일 발생을 나타내는 스키마 구조를 사용하면 쿼리 최적화에 도움이 됩니다. 이러한 이유로 <xs:choice> 구조가 선택적 요소가 있는 <sequence> 에 비해 선호됩니다.

XML 인덱스 선택 비활성화

XML 인덱스가 제약 조건을 평가하기 전 또는 후에 수정되도록 쿼리 최적화 프로그램이 보장하지 않기 때문에 XML 인덱스 선택은 check 제약 조건에서 비활성화됩니다. 이 문서의 성능 지침을 따라 제약 조건이 XML blob 상에서 효율적으로 평가될 수 있도록 하기 위해 충분한 주의를 기울여야 합니다. 뿐만 아니라 XML 인덱스 선택은 CHECK OPTION이 있는 뷰에서 비활성화됩니다.

XML 열의 전체 텍스트 인덱스

XML 열의 XML 인덱스와는 독립적으로 이 열에 전체 텍스트 인덱스를 만들 수 있습니다. 전체 텍스트 인덱스는 요소 콘텐츠를 인덱싱하고 XML 태그 및 특성 값을 무시하며 태그를 토큰 경계로 사용합니다.

XQuery 함수 fn:contains()가 리터럴, 부분 문자열 일치의 의미론을 갖는 반면 CONTAINS()를 사용하는 전체 텍스트 검색은 형태소 분석을 이용한 토큰 일치를 사용합니다. 따라서 이들의 의미론은 상이합니다. 차이점을 예로 들어 보자면, 단어 "data"를 검색하면 XQuery에서는 단어 "database"와 일치하지만 전체 텍스트 의미론에서는 일치하지 반면, 단어 "drove"를 검색하면 전체 텍스트 의미론에서는 단어 "driving"과 일치하지만 XQuery에서는 일치하지 않습니다. 게다가 전체 텍스트 검색은 특성 값 검색에 사용할 수 없고, 한편 XQuery 식은 혼합 콘텐츠를 검색하기 위해 집계 함수 fn:string()을 사용해야 합니다.

전체 텍스트 인덱스가 XML 열에 존재하는 경우 다음 작업을 수행하는 것이 좋습니다.

전체 텍스트 검색을 사용하여 관심 있는 XML 값을 필터링합니다.

선택한 XML 인스턴스를 XML 데이터 형식 메서드를 사용하여 쿼리합니다. 이 단계 동안 XML 열의 XML 인덱스가 사용됩니다. 이렇기 때문에 전체 텍스트 인덱스 및 XML 인덱스를 모두 사용하게 됩니다. 전체 텍스트 검색에서 검색 단어 또는 구의 높은 선택성은 XQuery 검색을 위한 추가 처리 범위를 기본 테이블에 있는 비교적 적은 행의 수로 좁혀줍니다. 이로 인해 쿼리의 속도가 상당히 빨라질 수 있습니다. 이 접근 방법은 검색 구가 키워드 경계와 일치하는 어간으로 이루어진 경우에 사용할 수 있습니다.

예제: XQuery 일치와 전체 텍스트 결합

다음 쿼리는 키워드 'data'에 대한 전체 텍스트 검색을 수행하고 단어 "data"가 <book>의 <title>요소의 컨텍스트에서 나타나는지 확인합니다. 이 쿼리는 전체 텍스트 contains() 메서드를 사용하여 검색 단어를 포함하고 있는 XML 인스턴스를 찾습니다. 즉, XML 데이터 형식 메서드 exist()는 XML 인스턴스가 올바른 컨텍스트에서 부분 문자열을 포함하는지 확인합니다.

select *
from docs
where contains(xCol, 'data')
AND xCol.exist('/book/title/text()[contains(.,"data")]') = 1

예제: 전체 텍스트에서 접두사 검색 사용

전체 텍스트 인덱스에서 접두사 검색을 수행할 수 있습니다. "database"와 같은 "data"로 시작하는 모든 키워드와 일치하는 쿼리에 대해, 위 쿼리는 다음과 같이 다시 작성될 수 있습니다. XQuery 검색은 "database"와도 일치합니다.

select *
from docs
where contains(xCol, '"data*"')
and xCol.exist('/book/title/text()[contains(.,"data")]') = 1

전체 텍스트 contains() 메서드에서 큰따옴표의 사용에 주의하십시오.

스냅샷 격리 및 XML 인덱스

XML 데이터 수정은 이전 XML 인스턴스를 새 XML 인스턴스로 업데이트합니다. 이러한 변경은 주 XML 인덱스 및 보조 XML 인덱스에 전파됩니다. 기본 테이블 및 XML 인덱스에서 수정된 행은 잠겨지고 행 및 페이지 잠금이 쿼리 분석기의 결정에 따라 테이블 잠금으로 에스컬레이션될 수 있습니다. 특히 작업 부하에서 수정이 잦은 경우에는 잠금 에스컬레이션으로 인해 동시성이 나빠집니다.

SQL Server 2005에서 스냅샷 기반 격리는 "스냅샷"이라는 새로운 격리 수준과 새로운 read-committed 격리 수준의 구현을 도입했습니다. 이에 대한 자세한 내용은 SQL Server 온라인 설명서에서 찾아볼 수 있습니다. 이들은 데이터베이스를 스냅샷 격리에 사용할 수 있는 경우 판독기와 작성기 사이의 잠금 경합을 제거하는 내부 버전 관리 메커니즘에 기반합니다. 잠금 경합이 감소되면 처리량이 더 높아질 수 있습니다.

스냅샷 기반 격리 하에서 읽기 작업은 동시 업데이트에서 차단되지 않고서 버전 관리되는 데이터에 액세스할 수 있습니다. 이렇게 차단이 감소되면 동시 작업 부하에서 트랜잭션 처리량이 잠재적으로 향상됩니다.

스냅샷 격리를 사용하면 업데이트 시 XML 열 값과 해당 주/보조 XML 인덱스 행이 버전 관리됩니다. 이렇게 하면, XML이 아닌 열의 수정으로 인해 포함하는 행이 버전 관리되는 경우 XML 열의 불필요한 버전 관리를 피할 수 있습니다. 이 최적화는 XML 처리에 있어 스냅샷 격리를 매우 유용하게 만듭니다.

쿼리 및 데이터 수정

인덱스된 XML에 대한 여러 value() 메서드 실행 병합

인덱스된 경우에 더 빠른 실행을 위해, SELECT 목록의 동일한 형식화된 XML 열에서 여러 value() 메서드의 실행을 결합할 수 있습니다. 실행을 결합할지 여부는 쿼리 분석기가 쿼리 비용을 기반으로 결정합니다. 이로 인해 속도가 현저히 향상됩니다. 아래에 예제가 나와 있습니다.

예제: 여러 value() 메서드 실행 결합

XML 스키마 모음 bookCollection에 있는 XML 스키마 이름 공간 "http://www.microsoft.com/book"에 의해 요소의 콘텐츠 모델이 정의된다고 가정합시다. 또한 이 예제에서 XML 열 xDoc이 docs 테이블에 추가되고 bookCollection을 사용하여 형식화된다고 가정합시다. XML 스키마 정의는 아래와 같습니다.

CREATE XML SCHEMA COLLECTION bookCollection AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.microsoft.com/book"
targetNamespace="http://www.microsoft.com/book">
<xsd:element name="book" type="bookType" />
<xsd:complexType name="bookType">
<xsd:sequence>
<xsd:element name="title" type="xsd:string" />
<xsd:element name="author" type="authorName"
maxOccurs="unbounded"/>
<xsd:element name="price" type="xsd:decimal" />
</xsd:sequence>
<xsd:attribute name="subject" type="xsd:string" />
<xsd:attribute name="releasedate" type="xsd:integer" />
<xsd:attribute name="ISBN" type="xsd:string" />
</xsd:complexType>
<xsd:complexType name="authorName">
<xsd:sequence>
<xsd:element name="first-name" type="xsd:string" />
<xsd:element name="last-name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>'
GO

ALTER TABLE docs ADD COLUMN xDoc XML (DOCUMENT bookCollection)
GO

아래 쿼리에서는, value() 메서드가 동일한 XML 열에서 호출되고 <title> 및 <price> 요소의 단일 카디널리티가 XML 스키마에서 정적으로 추론되기 때문에 value() 메서드의 실행을 결합합니다.

WITH XMLNAMESPACES ('http://www.microsoft.com/book' AS "bk")
SELECT xCol.value ('/bk:book/bk:title', 'nvarchar(128)') Title,
xCol. value('/bk:book/bk:price', 'decimal(5,2)') Price
FROM docs

최적화가 발생하려면 다음 조건이 유지되어야 합니다.

열을 형식화하는 XML 스키마 모음에서 노드의 단일 카디널리티를 추론할 수 있도록 XML 열을 형식화해야 합니다. 해당되는 경우 열 옵션 DOCUMENT을 사용하여 XML 열을 선언해야 합니다. 그렇지 않으면 nodes() 메서드를 사용하여 단일 노드 참조를 생성해야 합니다. 또한 nodes() 메서드가 단일 요소를 보장하는 데 사용되고 value() 메서드가 그러한 요소에서 특성 값을 추출하는 경우 형식화되지 않은 XML에 대해서도 최적화가 작동합니다.

최적화가 발생하려면 전체 경로를 지정해야 합니다. 와일드카드(*), 하위 항목 축(// operator), 상위 항목 축(..), XPath 함수 및 노드 테스트(예: node())를 포함하는 경로는 이러한 최적화의 발생을 막습니다.

value() 메서드의 경로 식은 술어 또는 서수를 포함하지 않을 수 있습니다.

nodes() 메서드에서 얻은 컨텍스트 항목과 관련된 경로 식에 대해 최적화가 발생하는데, 이러한 경우 value() 메서드의 첫 번째 인수는 위 제약과 더불어 전체 상대 경로여야 합니다.

value() 메서드 호출이 병합되려면 SELECT 목록에서 연속적으로 나타나야 합니다. 불연속적인 value() 메서드의 실행은 결합될 수 없습니다.

또한 xCol.value(…) = xCol.value(…)와 같은 술어에 대해 T-SQL WHERE 절의 value() 메서드에서 최적화가 발생할 수 있습니다. xCol.value(…) = constant의 술어에서는 최적화가 발생하지 않습니다.

존재 확인을 위해 exist() 메서드 사용

보다 나은 성능을 위해 가능한 경우 XML 데이터 형식의 value() 메서드 대신 exist() 메서드를 사용하십시오. exist() 메서드는 SQL WHERE 절에서 사용하는 경우 가장 유용하며 value() 메서드보다 XML 인덱스를 더 효과적으로 활용합니다. XQuery 식에서 sql:variable() 및 sql:column()을 사용하는 경우에도 마찬가지입니다.

예를 들어, exist() 메서드를 사용하여 "Writing Secure Code"라는 제목의 책을 검색하는 다음 쿼리를 살펴봅시다.

SELECT *
FROM docs
WHERE xCol.exist('(/book/title/text())[.="Writing Secure Code"]') = 1

PATH 또는 VALUE 보조 XML 인덱스가 이들 인덱스에서의 값 조회(이 예제에서 "Writing Secure Code")를 포함하여 경로 식((/book/title/text())[.="Writing Secure Code"])을 평가하여 반환할 XML 인스턴스를 산출하기 위해 사용됩니다. 경로 및 검색 값의 선택성이 높은 경우 결과 실행이 열의 모든 XML blob에 대해 경로 식을 평가하는 것보다 훨씬 더 빠를 수 있습니다. 검색 값은 sql:variable() 또는 sql:column()을 사용하여 제공할 수 있습니다. 자세한 내용은 이 문서의 "XQuery 및 XML DML 식 매개 변수화"를 참조하십시오.

아래와 같이 value() 메서드를 사용하여 작성한 쿼리는

SELECT *
FROM docs
WHERE xCol.value('(/book/title)[1]', 'varchar(50)') = 'Writing Secure Code'

먼저 모든 책 제목을 평가한 다음 필터 "Writing Secure Code"를 적용합니다. 이렇게 하면 XML 인덱스 조회에서 필터 값 "Writing Secure Code"가 사용되지 않기 때문에 쿼리 실행의 효율성이 떨어집니다. SQL 변수 또는 또 다른 value() 메서드를 사용하여 지정한 필터 값도 유사한 동작(behavior)을 나타냅니다.

nodes()-value() 결합 사용
nodes() 메서드는 value() 메서드 내에서 사용할 수 있는 내부 노드 참조의 행 집합을 생성하여 이들 노드에서 스칼라 값을 추출합니다. 이들 메서드를 함께 사용하여 XML 데이터를 관계형 형식으로 표시할 수 있습니다.

아래 예제에서 보는 대로, value() 메서드에서 컨텍스트 노드의 특성을 선택하는 데 사용되는 서수 술어가 제거될 수 있도록 nodes() 메서드의 출력에서 각 행은 단일 참조를 표시합니다. 또한 nodes() 메서드가 정확히 하나의 참조를 제공할 경우 nodes() 메서드를 완전히 제거하면 쿼리가 더 빠르게 수행됩니다. 이러한 최적화는 XML 변수 및 매개 변수에 가장 유용합니다.

예제: nodes() 메서드로 서수 술어 제거

이 쿼리는 docs테이블의 xCol 열에 있는 각 책의 인스턴스에서 ISBN 특성을 추출합니다. nodes() 메서드는 각 개별 <book> 요소(컨텍스트 노드)에 대한 별도의 참조를 내보내고 컨텍스트 노드에는 최대 하나의 @ISBN 특성이 있을 수 있습니다.

SELECT ref.value('@ISBN', 'nvarchar(32)')
FROM docs CROSS APPLY xCol.nodes('/book') AS node(ref)

각 XML 인스턴스에서 요소가 하나만 발생할 경우 다음의 다시 작성된 쿼리가 더 빠릅니다.

SELECT xCol.value('(/book/@ISBN)[1]', 'nvarchar(32)')
FROM docs

XML blob을 위한 최적화

XML 변수 및 매개 변수의 더 나은 확장성을 위한 여러 개의 tempDB 파일

XML 변수 및 매개 변수는 그 값이 작은 경우에는 주 메모리를 저장소로 사용합니다. 단, 큰 값은 tempdb 저장소에서 백업합니다. 다중 사용자 시나리오에서 많은 수의 대형 XML blob이 발생할 경우 tempdb 경합이 충분한 확장성을 위해 병목 상태를 일으킬 수 있습니다. 여러 개의 tempdb 파일을 만들면 저장소 경합이 감소되고 확장성이 훨씬 더 좋아집니다. 다음 예제는 여러 개의 tempdb 파일을 만들 수 있는 방법을 보여줍니다.

예제: 여러 개의 tempdb 파일 만들기

이 예제에서는 각각 처음 크기가 8MB인 두 개의 tempdb용 추가 데이터 파일과 각각 처음 크기가 1MB인 두 개의 로그 파일을 만듭니다.

USE TEMPDB
GO

ALTER DATABASE tempdb ADD FILE
(NAME = 'Tempdb_Data1',
FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),
(NAME = 'Tempdb_Data2',
FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB)
GO

ALTER DATABASE tempdb ADD log FILE
(NAME = 'Tempdb_Log1',
FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),
(NAME = 'Tempdb_Log2',
FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)
GO

이 파일은 ALTER DATABASE tempdb REMOVE FILE 명령을 사용하여 제거될 수 있습니다. 자세한 내용은 SQL Server 2005용 SQL Server 온라인 설명서를 참조하십시오.

XML 데이터 형식으로의 추가 캐스트 제거

XML 형식의 입력 인수를 가진 inlinable 함수에서 호출자는 XML 데이터 형식으로 암시적으로 변환되는 텍스트 또는 이진 값을 제공할 수 있습니다. 호출 수신자의 본문에서 XML 인수의 각 사용은 입력 값을 XML 데이터 형식으로 캐스트합니다. 이 비용은 인수를 XML 데이터 형식 변수(XML 데이터 형식으로 한 번의 인수 값 변환 유발)로 복사함으로써 피할 수 있고 XML 변수는 함수 또는 저장 프로시저의 본문에서 여러 번 사용할 수 있습니다. 다음 예제는 이러한 특징을 예시합니다.

예제: 변환 제거

책의 제목 및 ISBN을 반환하는 아래 GetTitleAndIsbnOfBook() 함수를 살펴봅시다.

CREATE FUNCTION GetTitleAndIsbnOfBook (@book XML)
RETURNS TABLE AS
RETURN
SELECT @book.value ('(/book/@ISBN)[1]', 'nvarchar(32)') ISBN,
@book.value ('(/book/title)[1]', 'nvarchar(128)') title

함수가 문자열 값으로 호출되면 각 value() 메서드 호출에 대해 XML 데이터 형식으로의 변환이 발생합니다. 함수는 XML 데이터 형식으로의 인수 변환이 한 번만 발생하도록 다음과 같이 다시 작성될 수 있습니다. 하지만 다중 명령문 테이블 반환 함수에 필요한 테이블 변수 @retTab으로 인해 추가 비용이 발생하는데, 이 비용은 XML 데이터의 크기가 큰 경우 XML 변수에 대한 충분한 횟수의 액세스로 상쇄될 수 있습니다.

CREATE FUNCTION GetTitleAndIsbnOfBookOpt (@book varbinary(max))
RETURNS @retTab TABLE (ISBN nvarchar(32), title nvarchar(128)) AS
BEGIN
DECLARE @xbook XML
SET @xbook = @book
INSERT INTO @retTab
SELECT @xbook.value ('(/book/@ISBN)[1]', 'nvarchar(32)'),
@xbook.value ('(/book/title)[1]', 'nvarchar(128)')
RETURN
END

단일 요소 지정

단일 카디널리티 평가는 쿼리 및 데이터 수정 문에서 서수를 지정할 필요성을 제거합니다. 이로 인해 쿼리 계획이 간소화되고 효율적인 JOIN 작업이 생성됩니다. 일반적으로 단일 카디널리티 평가에는 중첩 루프 조인에서 내부 및 외부 루프에 대한 적절한 선택이 수반됩니다.

형식화된 XML에서 기본적으로 요소는 minOccurs 및 maxOccurs의 값을 사용하여 재정의되지 않는다면 XML 스키마 구조의 단일 카디널리티를 갖습니다. 또한 형식화된 XML 열, 변수 및 매개 변수의 DOCUMENT 제약 조건은 XML 데이터 형식 인스턴스에서 정확히 하나의 최상위 요소를 보장합니다.

형식화되지 않은 데이터의 경우 또는 스키마에 여러 형제 요소가 허용되는 경우, 아래 예제에 나타난 대로 경로 식을 만족시키는 노드를 정확하게 하나만 선택하는 서수 값을 사용하여 노드의 단일 카디널리티가 경로 식에 표시될 수 있습니다. 서수 [1]은 Transact -SQL TOP 1 오름차순을 사용하여 평가되는 반면 서수 last()는 TOP 1 내림차순으로 평가됩니다. 또한 nodes() 메서드는 결과 XML 인스턴스 각각에 대해 단일 컨텍스트 항목을 설정합니다.

단일 노드의 선택이 생략된 경우 쿼리 최적화 프로그램이 지나치게 높을 수 있는 기본 카디널리티 평가를 사용합니다. 예를 들면, 이는 술어를 처리해야 하는 경우 중첩 루프 조인에서 내부 및 외부 루프에 대해 차선책을 선택하게끔 유발할 수 있습니다. 그 효과는 XML 인덱스가 존재하지 않고 보다 효율적인 카디널리티 평가를 위해 사용할 수 있는 통계 정보가 없는 XML blob의 경우에 더욱 크게 나타납니다.

예제: 형식화되지 않은 XML의 단일 카디널리티 지정

xCol 열의 각 XML 인스턴스가 <title> 하위 요소를 하나만 갖는 최상위 요소를 하나만 포함한다고 가정합시다. 다음 쿼리를 살펴봅시다.

SELECT xCol.query ('/book/title')
FROM docs

쿼리 최적화 프로그램은 <title> 요소에 기본 카디널리티 평가를 사용합니다. 각 <book>은 <title>이 단일 요소가 되도록 하나의 제목을 갖지만 최적화 프로그램의 평가는 그에 비해 훨씬 더 높습니다. 다시 공식화한 다음 쿼리는

SELECT xCol.query ('(/book/title)[1]')
FROM docs

올바른 카디널리티를 최적화 프로그램에 전달합니다. 비슷한 모양의 경로 식 (/a/b)[1]과 /a/b [1] 간의 의미론적 차이점이 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에 설명되어 있습니다.

XML 데이터 형식 메서드의 다중 실행 제거

다음 쿼리는

SELECT case isnumeric (xCol.value ('(/book/price)[1]', 'nvarchar(32)'))
when 1 then xCol.value ('(/book/price)[1]', 'decimal(5,2)')
else 0
end
FROM docs

책의 <price>를 계산한 다음 가격이 숫자 형식인 경우 가격을 십진수(5, 2)로 변환합니다. 이 논리는 응용 프로그램에서 숫자가 아닌 가격 값이 발생할 수 있는 경우에 유용합니다.

SELECT case isnumeric(Price)
when 1 then CAST (Price AS decimal(5,2))
else 0
end
FROM (SELECT xCol.value ('(/book/price)[1]', 'nvarchar(32)') Price
FROM docs) T

동일한 최적화가 NULLIF()와 같은 다른 곳에서도 사용될 수 있습니다.

SELECT NULLIF (Title, '')
FROM (SELECT xCol.value ('(/book/title)[1]', 'nvarchar(64)') Title
FROM docs) T

NULLIF()에서 value() 메서드를 사용하면 이 메서드가 비어 있지 않은 문자열을 반환할 경우 value() 메서드가 두 번 처리됩니다.

Data(),text() 및 string() 접근자

XQuery는 노드에서 형식화된 원자 값을 추출하기 위한 fn:data() 함수, 텍스트 노드를 반환하기 위한 노드 테스트 text() 및 노드의 문자열 값을 반환하기 위한 fn:string() 함수를 제공합니다. 이들의 사용은 혼동될 수 있습니다. SQL Server 2005에서 이를 올바로 사용하기 위한 지침이 XML 인스턴스 <age>12</age>를 사용하여 아래에 예시되어 있습니다.

형식화되지 않은 XML: 경로 식 /age/text()는 값이 “12”인 <age> 아래에 텍스트 노드를 반환합니다. fn:data(/age) 함수는 fn:string(/age)처럼 문자열 값 “12”를 반환합니다.

형식화된 XML: /age/text() 식은 SQL Server 2005에서 모든 단순한 형식화된 <age> 요소에 대한 정적 오류를 반환합니다. <age>가 단순한 정수 콘텐츠를 갖는 경우에는 fn:data(/age)가 정수 12를 반환하는 반면, fn:string(/age[1])은 문자열 “12”를 산출합니다. 이들 함수는 서로 다른 성능 특성을 갖습니다. Fn:string()은 컨텍스트 노드 아래의 모든 텍스트 노드를 재귀적으로 집계합니다. 컨텍스트 노드가 단일값인 경우 이 방식은 과도하며, 이 경우에는 fn:data() 및 text()로도 충분할 뿐만 아니라 더 효과적입니다.

형식화되지 않은 XML의 경우 노드의 값이 필요할 때, text()를 사용하여 텍스트 노드를 반환하는 것이 fn:data()를 사용하여 반환하는 것보다 더 빠릅니다. 경로 식 /book/text()는 <book> 요소의 텍스트 노드 자식(child)을 반환합니다. query() 메서드 내에서 이러한 텍스트 노드는 직렬화되고 텍스트 노드 값의 연결로 나타납니다. 반면, fn:data()는 <book> 요소의 하위 트리에 있는 모든 값을 집계합니다. 이 집계는 간단한 콘텐츠를 가진 요소의 경우일지라도 fn:data()의 계산이 text()의 계산보다 비용이 더 많이 소요되게 만듭니다.

형식화되지 않은 XML에서 텍스트 집계

XQuery 의미론에 따라, 형식화되지 않은 XML에서 다음과 같은 두 쿼리는 술어를 평가하기 위해 <title> 요소 아래의 모든 텍스트 노드를 집계해야 합니다. 이 때문에 검색 문자열에 대한 XML 인덱스 조회가 억제됩니다.

SELECT xCol.value ('(/book/title[.="Writing Secure Code"])[1]',
'nvarchar(64)')
FROM docs

또는

SELECT xCol.value ('(/book/title
[fn:string()="Writing Secure Code"])[1]'), 'nvarchar(64)')
FROM docs

<title> 요소에 텍스트 노드가 하나만 있는 경우 더 효율적인 쿼리 작성 방법은 아래와 같이 텍스트 노드의 술어를 평가하는 방법입니다.

SELECT xCol.value ('(/book/title/text())[1]
[. = "Writing Secure Code"]', 'nvarchar(64)')
FROM docs

이 경우에는 "Writing Secure Code" 값에 대한 XML 인덱스 조회가 발생합니다.

XQuery 및 XML DML 식 매개 변수화

XQuery 및 XML DML 식은 자동으로 매개 변수화되지 않습니다. 따라서 두 XQuery 식이 매개 변수의 값만 다른 경우, 동적 SQL 문을 사용하는 대신 sql:column() 또는 sql:variable()을 사용하여 XQuery 또는 XML DML 식에 매개 변수 값을 제공하는 것이 더 좋습니다. 이들 함수를 사용하면 쿼리가 자동으로 매개 변수화됩니다.

아래 예제는 저장 프로시저 실행을 보여줍니다. 이 기법은 쿼리, 함수/메서드 호출 또는 데이터 수정 문의 매개 변수화에 적용할 수 있습니다.

예를 들어, 아래 저장 프로시저는 입력 인수보다 낮은 가격의 책을 찾습니다.

CREATE PROC sp_myProc
@Price decimal
AS
SELECT *
FROM docs
WHERE 1 = xCol.exist('(/book/price)[. < sql:variable("@Price")]')

ADO.NET 및 OLEDB에서 @Price의 입력 값을 매개 변수에 바인딩합니다. 이렇게 하면 매개 변수가 서로 다른 값에 바인딩될 때 쿼리를 다시 컴파일할 필요가 없습니다. sql:column()을 사용하면 이와 비슷한 이점을 얻을 수 있습니다.

다음 Visual Basic.NET 코드는 저장 프로시저 호출에서 매개 변수 바인딩을 보여줍니다.

'myConn is the connection string
SqlCommand cmd = New SqlCommand("sp_myProc", myConn)
cmd.CommandType = CommandType.StoredProcedure

'Parameter binding
Dim myParm As SqlParameter = cmd.Parameters.Add("@Price", _
SqlDbType.Decimal)
myParm.Direction = ParameterDirection.Input
myParm.value = 2

'Invoke the stored procedure
SqlDataReader myReader = cmd.ExecuteReader()

'Invoke the stored procedure a second time
myParm.value = 49.99
SqlDataReader myReader = cmd.ExecuteReader()

자세한 내용은 Microsoft Visual Studio.NET 설명서 (영문)를 참조하십시오.

예제: 데이터 수정에서 sql:variable() 사용

ISBN이 "0-2016-3361-2"인 <book>의 <price>가 10% 할인되었다고 가정합시다. 할인과 ISBN 둘 다 XML 데이터 수정 문에 매개 변수로 전달될 수 있고 이 문은 다른 책 또는 다른 할인에 대해 똑같이 유지됩니다.

DECLARE @discountFactor decimal, @sqlisbn nvarchar(32)
SET @discountFactor = 0.9
SET @sqlisbn = N'0-7356-1588-2'

UPDATE docs
SET xCol.modify('replace value of (/book/price/text())[1] with
sql:variable("@discountFactor")*(/book/price/text())[1]')
WHERE xCol.exist('/book[@ISBN = sql:variable("@sqlisbn")]') = 1

예제 : 요소 구성에서 sql:variable() 사용

아래에 나와 있는 modify() 메서드는 구성된 요소 내에서 값을 제공하기 위한 sql:variable()의 사용을 보여줍니다.

DECLARE @name nvarchar(64)
SET @name = 'Microsoft Press'
UPDATE docs
SET xCol.modify ('
insert < publisher Name = "{sql:variable("@name")}"></publisher>
into (/book/title)[1]')

술어 및 서수의 최적화

노드 테스트 또는 분기가 없는(즉, 경로의 중간 노드에 술어 또는 서수가 없는) 전체 경로(즉, 루트 노드로부터 자식(child) 및 자신의 축만을 포함하는 선택한 노드에 이르는 절대 위치 경로)는 분기가 있는 경로 식보다 효율적으로 평가할 수 있습니다. 인덱스된 경우에 전체 경로는 인덱스 탐색에 사용될 수 있습니다. XML blob의 경우, 이러한 경로의 구문 분석은 분기 또는 와일드카드(*)가 있는 경로의 구문 분석보다 더 빠릅니다.

경로 끝부분의 노드 테스트 및 술어는 선택한 노드에서 필터로 사용됩니다. 인덱스가 사용됩니다. XML blob의 경우 구문 분석이 효율적입니다. 아래에 예제가 나와 있습니다.

예제: 전체 경로 평가

이름이 Davis인 저자가 쓴 책을 선택하는 경로 식을 생각해 봅시다.

SELECT xCol.query ('/book[author/first-name = "Davis"]')
FROM docs

술어가 <book> 요소에 직접 존재하지 않더라도 축소 경로 /book/author/first-name을 사용하여 위치한 <first-name> 노드는 값 "Davis"에 의해 필터링됩니다. 반환된 <book> 요소는 주어진 술어를 충족하는 것들입니다.

경로 기반 조회는 /book//first-name과 같이 술어 또는 서수가 없는 부분적으로 지정된 경로에도 효율적입니다. 쿼리 컴파일러는 XML 인덱스에서 이러한 경로를 일치시키기 위해 LIKE 연산자를 사용합니다. 그러므로 가능한 한 경로의 많은 부분을 지정하는 것이 보다 효율적인 처리에 도움이 됩니다.

/book[@ISBN = "1-8610-0157-6"]/author/first-name에서와 같이 분기(즉, 경로 식의 중간에 있는 노드 테스트 및 서술어)는 경로 식 /book[@ISBN = "1-8610-0157-6"] 및 /book/author/first-name을 평가하고 <book> 요소의 두 집합 사이의 교집합을 취합니다. 따라서 분기 없는 경로 식보다 실행이 느려집니다. 경로 식의 중간에 노드 테스트 및 술어를 사용하는 것을 가능한 한 피하는 것이 효과적입니다. 이는 “일반적 태그와 구체적 태그의 비교” 예제에 설명된 대로, 데이터 모델링 시 신중한 주의를 기울임으로써 가능한 경우가 종종 있습니다.

경로의 끝으로 서수 이동

정적 형식의 정확성을 위해 경로 식에 사용되는 서수는 경로 식의 끝부분에 넣을 수 있는 좋은 후보입니다. 경로 식 book[1]/title[1]은 (/book/title)[1]과 동일합니다. 후자는 문서 순서에서 <book> 요소 아래의 첫 번째 <title> 요소를 확인함으로써 XML 인덱스 및 XML blob 두 경우 모두에 대해 모두 더 빠르게 평가될 수 있습니다. 이와 유사하게 경로 식 (/book/@ISBN)[1]은 /book[1]/@ISBN보다 더 빠른 실행을 가져옵니다.

컨텍스트 노드를 사용한 술어 평가

술어, 서수 및 노드 테스트를 경로 식의 끝으로 이동하는 것 외에도 컨텍스트 노드를 사용하여 이러한 조건을 평가하면 한층 더 나은 성능을 얻을 수 있습니다. 아래에 이에 대한 쿼리 재작성 예제가 나와 있습니다.

예제: 컨텍스트 노드를 사용한 술어 평가

아래 쿼리는 “security”라는 제목의 책을 검색합니다. 이 쿼리에는 두 경로 식, /book 및 /book/@subject의 평가와 후자 경로에 대한 “security” 값 확인이 필요합니다.

SELECT *
FROM docs
WHERE xCol.exist ('/book[@subject = "security"]') = 1

다시 작성된 아래 쿼리는 단일 경로 /book/@subject를 평가하고 이 경로에 "security" 값이 있는지 확인합니다. 이렇게 하면 위 쿼리보다 쿼리 계획이 더 단순해지고 실행이 훨씬 더 빨라집니다.

SELECT *
FROM docs
WHERE xCol.exist ('/book/@subject[. = "security"]') = 1

범위 조건

범위 조건은 형식화된 XML의 사용을 통해 이점을 얻습니다. XML 열 및 XML 인덱스에 저장된 데이터는 XML 스키마에 지정된 형식 정의에 따라 형식화됩니다. 값 비교는 데이터의 런타임 변환을 피하고 VALUE 보조 XML 인덱스에 대한 범위 스캔을 허용합니다. 또한 이를 위해서는 다음 예제에 나타난 대로, 효율적인 액세스를 위해 범위 조건에 컨텍스트 노드(.)를 지정해야 합니다.

예제: 범위 조건의 컨텍스트 노드

$9.99와 $49.99 범위에 있는 가격대의 책을 찾는 쿼리를 고려해 봅시다.

SELECT xCol
FROM docs
WHERE xCol.exist ('/book[price > 9.99 and price < 49.99]') = 1

경로 식 /book/price > 9.99 및 /book/price < 49.99가 각각 따로 평가됩니다. <book> 요소 아래에 여러 <price> 요소가 존재할 수 있기 때문에 쿼리 최적화 프로그램은 <price> 요소가 동일하다는 사실을 알지 못합니다. 이 때문에 VALUE 보조 XML 인덱스에 대한 범위 스캔이 억제됩니다. 아래에 다시 작성된 쿼리는 <price>에 동일한 컨텍스트 노드를 사용하고 9.99와 49.99 사이의 값에 대해 VALUE 보조 XML 인덱스의 범위 스캔이 발생하도록 보장합니다. 이로 인해 성능이 더 좋아집니다.

SELECT xCol
FROM docs
WHERE xCol.exist ('/book/price[. > 9.99 and . < 49.99]') = 1

동적 쿼리

XQuery 식은 XML 데이터 형식 메서드 내에서 리터럴로 지정됩니다. 이들의 평가는 사용 가능한 경우 쿼리 최적화 프로그램에서 선택한 XML 인덱스를 사용합니다.

XQuery 식을 리터럴 대신 동적으로 지정할 수 있으면 응용 프로그램 개발이 편리합니다. 이는 다음 방식으로 가능합니다.

쿼리 구성
쿼리를 문자열로 작성하고 sp_executesql을 사용하여 실행합니다. EXEC와 달리, 이렇게 하면 컴파일된 쿼리 계획이 캐시되어 최적화 프로그램이 컴파일된 계획을 다시 사용할 수 있습니다. 쿼리는 문자열로 구성되므로 매개 변수화될 수 있고 포함된 매개 변수를 포함할 수 있습니다. SQL 주입 공격을 피하려면 충분한 주의를 기울여야 합니다.

XPath 함수 사용
XPath 식의 각 위치 단계를 name() 함수 또는 local-name() 및 namespace-URI() 함수로 대체합니다. 이렇게 하면 노드 이름 및 검색 값으로 전달할 수 있는 쿼리가 만들어집니다. "XQuery 또는 XML DML 식 매개 변수화" 예제에 설명된 대로 추가적으로 매개 변수화할 수 있습니다. 이와 같은 매개 변수화된 쿼리는 응용 프로그램 개발에 편리합니다. 하지만 컴파일 시에 구체적인 경로가 알려지지 않으므로, 이에 대해 생성된 쿼리 계획은 XML 인덱스를 무시합니다.

쿼리 구성 접근 방법은 경로 식 매개 변수화보다 더 능률적으로 작동하지만 런타임 쿼리 컴파일 비용이 들기 때문에, 전체 쿼리를 리터럴로 지정하는 것보다 더 느려집니다. 사용자가 전달한 실제 쿼리는 SQL 주입 공격을 피하기 위해 유효성이 검사되어야 합니다. 그렇지 않으면, 쿼리의 매개 변수화(이 문서의 “XQuery 및 XML DML 식 매개 변수화")를 위해 이 접근 방법을 사용하지 말아야 합니다. 다음 예제는 이 접근 방법을 예시합니다.

노드 이름을 사용하여 노드 테스트를 지정하는 두 번째 접근 방법은 SQL 주입 문제를 방지합니다. 하지만 쿼리 계획이 복잡해지고 원래 쿼리보다 훨씬 더 비능률적으로 수행됩니다. 이 접근 방법은 두 번째 예제에 나와 있습니다.

예제: sp_executesql를 사용한 쿼리

다음 쿼리를 동적으로 작성하여 매개 변수를 사용하여 @subject의 검색 값을 전달하기를 원한다고 가정합시다.

SELECT *
FROM docs
WHERE xCol.exist('/book[@subject = "security"]') = 1

동적 쿼리를 아래에 나타난 대로 작성하고 실행할 수 있습니다. 쿼리 문자열은 @SQLString 변수에 만들어지고 exist() 메서드에서 사용된 포함된 변수 @bksubj를 포함합니다. @subj 변수는 매개 변수의 런타임 값을 제공합니다. @SQLString을 사용하여 전달된 동적 쿼리는 SQL 주입 공격을 피하기 위해 유효성이 검사되어야 합니다(아래에는 표시되어 있지 않습니다).

DECLARE @SQLString NVARCHAR(500)
DECLARE @subj NVARCHAR(64)
DECLARE @ParmDefinition NVARCHAR(500)
--- Build the SQL string once
SET @SQLString =
N'SELECT *
FROM docs
WHERE xCol.exist(''/book[@subject=sql:variable("@bksubj")]'')=1'
SET @ParmDefinition = N'@bksubj NVARCHAR(64)'
--- Execute the string with the first parameter value
SET @subj = 'security'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@bksubj = @subj

예제: local-name()을 사용한 쿼리

위 쿼리를 다음과 같이 태그 이름을 리터럴로 사용하도록 다시 작성할 수 있습니다.

DECLARE @elemName nvarchar(4000), @attrName nvarchar(4000)
DECLARE @subjValue nvarchar(4000)
SET @elemName = N'book'
SET @attrName = N'subject'
SET @subjValue = N'security'
SELECT *
FROM docs
WHERE xCol.exist('/*[local-name() = sql:variable("@elemName") and
@*[local-name() = sql:variable("@attrName") and
. = sql:variable("@subjValue")]]') = 1

다시 작성된 쿼리는 와일드카드(*)와 노드 이름을 사용하는 노드 테스트를 포함하므로 효과적으로 최적화하기가 어렵습니다. 결과적으로 원래 쿼리 및 쿼리 구성 접근 방법에 비해 훨씬 더 비능률적으로 수행됩니다.

XML 데이터에서 행 집합 생성

일부 응용 프로그램은 하나 이상의 속성을 행 집합의 열로 승격함으로써 XML 데이터에서 행 집합을 생성해야 합니다. 예를 들어, 응용 프로그램은 책의 저자를 쿼리하고 성 및 이름에 대해 두 개의 열을 포함하는 테이블로 결과를 표시할 수 있습니다. 이러한 행 집합 생성은 서로 다른 성능 특성을 지닌 서버 또는 클라이언트에서 모두 수행될 수 있습니다.

서버에서 다음 메커니즘 중 하나를 사용하십시오.

XML 데이터 형식의 nodes() 및 value() 메서드의 결합
OpenXML
CLR(공용 언어 런타임)에서 테이블 반환 함수 스트리밍

또한 XML 결과가, 클라이언트 쪽 프로그래밍(예: DataSet)을 사용하여 데이터를 행 집합으로 변환하는 클라이언트에게 반환됩니다. 클라이언트 쪽 행 집합 생성은 서버의 부하를 덜어주므로 서버에서 클라이언트로 전송된 거의 전체 데이터가 행 집합으로 매핑되는 경우에 유용합니다. 그렇지 않은 경우에는 데이터 제공 비용이 클라이언트 쪽 처리 이점보다 더 클 수 있습니다.

서버 쪽 행 집합 생성은 서버에서 들어오는 XML 데이터로부터의 행 집합 생성에 유용합니다. 이 방식은 서버에 저장된 XML 데이터의 일부분만이 행 집합의 열로 승격되는 경우에 일반적으로 더 바람직합니다. 서버 쪽 접근 방법들의 상대적 장점 및 단점에 관한 자세한 설명은 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에서 찾을 수 있습니다.

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

[출처] SQL Server 2005에서 XML 데이터 형식을 위한 성능 최적화|작성자 필립박

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

IDENTITY (0)	2008.04.28
MsSql 날짜 함수 (0)	2008.04.28
Microsoft SQL Server 2005의 원시 XML 웹 서비스 개요 (0)	2008.04.28
Microsoft SQL Server 2005의 XML 옵션 (0)	2008.04.28
저장 프로시저의 테스트 자동화 (0)	2008.04.28

Microsoft SQL Server 2005의 원시 XML 웹 서비스 개요

2008. 4. 28. 08:25

728x90

Microsoft SQL Server 2005의 원시 XML 웹 서비스 개요

Brad Sarsfield, Srik Raghavan _ Microsoft Corporation

적용 대상:
Microsoft SQL Server 2005 (이전에는 "Yukon"으로 알려짐)
Transact-SQL(T-SQL) 언어

요약: SQL Server 2005(이전에는 "Yukon"으로 알려짐) 내의 SOAP/HTTP를 사용하여 XML 웹 서비스를 설정하고 사용하는 방법의 개요를 설명합니다. 여기에는 실례가 되는 예제가 포함되어 있습니다. 이 문서를 최대한 활용하려면 HTTP, SOAP 및 WSDL을 포함한 웹 서비스 기술에 대한 기본적인 이해가 필요합니다.

소개

Microsoft SQL Server 2005는 HTTP를 통해 SOAP를 사용하는 데이터베이스 엔진에 액세스하기 위한 표준 메커니즘을 제공합니다. 이 메커니즘을 이용하면 SOAP/HTTP 요청을 SQL Server에 전송하여 다음을 실행할 수 있습니다.

매개 변수가 있거나 없는 Transact-SQL 일괄 명령문

저장 프로시저, 확장 저장 프로시저, 스칼라 반환 사용자 정의 함수

SQL Server 20005 이전에는 SQL Server 연결에 사용할 수 있는 메커니즘은 테이블 형식 데이터 스트림(TDS)이라는 사용자 지정 바이너리 프로토콜을 통하는 방법뿐이었습니다. Microsoft는 SOAP/HTTP 액세스를 이용하여, SQL Server에 연결하기 위한 대안으로 사용할 수 있는 문서화된 개방형 프로토콜을 제공했습니다. SOAP/HTTP 액세스를 제공하면, SQL Server에 연결을 시도하는 클라이언트 장치에 Microsoft Data Access Components(MDAC) 스택을 더 이상 설치할 필요가 없기 때문에, 별도의 공간을 필요로 하지 않는 “제로 풋프린트” 클라이언트를 포함하여 보다 광범위한 클라이언트가 SQL Server에 액세스할 수 있습니다. 이로 인해 다양한 플랫폼에서 .NET, SOAP Toolkit, Perl 등과의 상호 운용성이 용이해집니다. SOAP/HTTP 액세스 메커니즘은 XML 및 HTTP와 같이 잘 알려진 기술을 기반으로 하므로 이 메커니즘은 유형이 다른 환경에서 SQL Server에 대한 액세스 및 상호 운용성을 본질적으로 촉진합니다. XML을 구문 분석하고 HTTP 요청을 제출할 수 있는 모든 장치가 이제 SQL Server에 액세스할 수 있습니다.

많은 기업들이 UNIX 및 Linux 플랫폼에서 실행되는 응용 프로그램에 SQL Server에 대한 연결이 필요할 수 있는 유형이 다른 환경을 가지고 있습니다. 지금까지는 이러한 사용자들이 사용할 수 있는 솔루션은 JDBC 또는 ODBC 드라이버 중 하나를 사용하는 것이었습니다. SOAP/HTTP 액세스는 이제 비용이 낮은 또 하나의 대안을 제공합니다. SOAP/HTTP 액세스는 DBA가 UNIX에서 실행되고 SQL Server 리소스를 관리하는 Perl로 작성된 스크립트를 가지고 있는 경우의 시나리오에 아주 유용합니다. 또한 Microsoft Visual Studio .NET 또는 Jbuilder와 같은 기본 제공 SOAP/HTTP 지원이 포함된 스마트 통합 개발 환경(IDE)을 사용하여 SQL Server에 연결하는 클라이언트 응용 프로그램 개발에도 유용합니다. 이러한 IDE는 SQL Server와의 통신을 추상화하고 클라이언트 응용 프로그램이 사용할 수 있는 개체를 제공하는 프록시 코드를 생성합니다. 또한 SOAP/HTTP를 사용하면 언제 어디서나 SQL Server에 대한 액세스가 가능하므로, 모바일 또는 산발적으로 연결된 장치를 위한 응용 프로그램의 개발이 쉬워집니다. 일단 연결이 되고 서버가 요청 처리를 시작하면 sqlclient, ODBC, OLEDB와 같은 TDS 기반 클라이언트가 사용하는 기존 메커니즘을 사용하여 연결이 모니터링될 수 있습니다.

요구 사항

SQL Server 2005의 기본 웹 서비스는 운영 체제로서 Microsoft Windows Server 2003이 필요한데, 그 이유는 웹 서비스가 이 버전이 제공하는 커널 모드 http 드라이버 http.sys에 의존하기 때문입니다. SQL Server는 커널 모드 http.sys 드라이버를 활용하므로 SQL Server로부터 웹 서비스를 노출하기 위해 IIS를 반드시 설치해야 할 필요가 없어 관리가 간소화됩니다. 대신 IIS 설치 여부의 결정은 응용 프로그램 요구 사항에 입각해야 합니다. 예를 들어 특정 응용 프로그램은 명시적인 중간 계층이 있으면 유리합니다. 이와 같은 경우에는 IIS가 유용할 수 있습니다.

HTTP 종점

SQL Server를 HTTP SOAP 요청을 기본적으로 수신할 수 있는 웹 서비스로 설정하려면 HTTP 종점을 만들고 종점이 노출하는 메서드를 정의해야 합니다. HTTP 종점을 만들 때에는 들어오는 HTTP 요청을 수신하기 위해 사용하는 고유 URL을 사용하여 만들어야 합니다. 예를 들어, URL "http://servername/sql"을 사용하여 종점을 만드는 경우 http://servername/sql에 전송되는 SOAP 요청은 http.sys에 의해 포착됩니다. 그런 다음 http.sys가 SOAP 요청을 URL과 연결된 종점을 호스팅하는 SQL Server 인스턴스로 라우팅합니다. 거기에서 요청은 SQL Server 내 SOAP 처리 레이어로 전달됩니다.

SQL Server 인스턴스에는 여러 개의 종점이 있을 수 있는데, 이 종점은 각각 임의 개수의 저장 프로시저(Transact-SQL 또는 CLR을 사용하여 구현됨)를 WebMethod로 종점에서 노출할 수 있고 SOAP 원격 프로시저 호출(RPC)을 통해 호출될 수 있습니다. WebMethod는 노출되는 실제 저장 프로시저와는 다른 이름을 사용할 수 있습니다. WebMethod 이름은 작업 이름으로 WSDL에서 사용자에게 보여지는 것입니다.

참고 종점의 WebMethod 절은 SQL Server 2005에만 해당되고 ASMX WebMethod 특성과 무관하다는 사실에 주의해야 합니다.

사용자는 종점에 대해 ad-hoc Transact-SQL 문을 실행할 수 있습니다. 이 작업은 데이터 정의 언어(DDL)에서 선택적 절을 사용하여 종점에서 Batches를 활성화하여 수행됩니다. Batches를 암시적으로 활성화하면 "sqlbatch"라는 WebMethod가 사용자에게 노출됩니다. 이 개념은 다음에 이어지는 섹션에서 더 자세히 설명됩니다.

HTTP 종점 만들기

HTTP 종점은 Transact-SQL DDL을 사용하여 만들어지고 관리됩니다. HTTP 종점을 만드는 작업은 SQL Server 2005에 대한 HTTP/SOAP 액세스 활성화의 첫 번째 단계입니다. 각 종점은 이름 및 결합될 경우 종점의 동작(behavior)을 정의하는 옵션의 모음을 가집니다.

CREATE HTTP ENDPOINT가 사용되는 방법을 예시하기 위해 SQL Server 웹 서비스를 통해 저장 프로시저를 호출하는 Hello World 예제를 살펴보도록 하겠습니다.

먼저, 다음 T-SQL을 사용하여 마스터 데이터베이스에 hello world라는 저장 프로시저를 만듭니다. 이 저장 프로시저는 단순히 입력 매개 변수에 제공된 문자열을 표시합니다.

CREATE PROCEDURE hello_world
(@msg nvarchar(256))
AS BEGIN
select @msg as 'message'
END

다음 단계로, 다음 T-SQL을 사용하여 WebMethod로서 이 저장 프로시저에 액세스할 수 있도록 하는 HTTP 종점을 만듭니다.

CREATE ENDPOINT hello_world_endpoint
STATE = STARTED
AS HTTP (
AUTHENTICATION = ( INTEGRATED ),
PATH = '/sql/demo',
PORTS = ( CLEAR )
)
FOR SOAP (
WEBMETHOD
'http://tempuri.org/'.'hello_world'
(NAME = 'master.dbo.hello_world'),
BATCHES = ENABLED,
WSDL = DEFAULT
)

모든 종점은 메타데이터 뷰 master.sys.http_endpoints에서 마스터에 저장됩니다. SOAP 메서드를 정의하지 않는 한 종점은 어떤 SOAP 메서드도 가지지 않습니다. 위 예제에서 저장 프로시저 master.dbo.hello_world를 WebMethod 'hello_world'로 노출했습니다. 이와 같이 WebMethod는 임의의 이름을 가질 수 있습니다. 예를 들면, WebMethod가 'http://tempuri.org' 네임스페이스 아래에서 'testproc1'로 호출될 수도 있었습니다. DEFAULT를 WSDL 절의 값으로 지정하면 기본 형식을 사용하는 WSDL를 생성하여 종점이 WSDL 요청에 응답할 수 있습니다. 위 명령문에서 WSDL=NONE을 설정하여 WSDL 생성을 억제할 수 있습니다. 다음에 이어지는 섹션에서 WSDL 생성에 대해 자세히 설명합니다.

인증 및 보안

HTTP 종점은 기본, 다이제스트, 통합(NTLM, Kerberos) 및 SQL Auth라는 표준 인증 메커니즘을 지원합니다. 먼저 HTTP 전송 수준에서 인증합니다. 성공적으로 인증되면, 사용자의 SID를 사용하여 SQL을 인증합니다. 이 과정은 LOGIN_TYPE = MIXED를 지정하여 SQL-AUTH가 종점에서 활성화되는 경우를 제외하고 모든 옵션에 적용됩니다. WsSecurity Username 토큰 헤더를 사용하여SQL Auth 자격 증명이 SOAP 패킷의 일부로 전송됩니다. 또한 관리자는 종점을 기준으로 IP 기반 제한을 설정하여, HTTP 종점으로의 액세스를 특정 IP 또는 IP 범위에 대해서만 허용함으로써 종점에 대한 액세스를 제한할 수 있습니다. 개념적으로 “종점”은 “응용 프로그램”입니다. 단일 응용 프로그램을 구현하는 모든 메서드가 종점에 매핑되므로 응용 프로그램에 대한 액세스를 제어하기 위해 종점에 보안이 적용됩니다. 종점은 설계에 의해 보안됩니다. 종점의 보안을 유지할 수 있도록 도와주는 몇 가지 항목이 아래에 나와 있습니다.

기본적으로 Off로 설정되어 있습니다. 매핑된 기본 종점 또는 웹 메서드가 없으므로 이를 명시적으로 생성 및 지정해야 합니다.

또한 개체에도 보안 검사가 적용되므로, 매핑된 저장 프로시저는 사용자가 종점에 대한 연결 권한 및 저장 프로시저에 대한 실행 권한을 가지고 있는 경우에만 실행 가능합니다.

종점에 연결을 위한 익명 지원을 하지 않습니다. WSDL 요청을 포함한 모든 요청이 인증을 받아야 합니다. 클라이언트가 요청을 제출하기 위해서는 SQL Server 원칙에 비추어 인증을 받아야 합니다.

종점이 만들어지면 sysadmin 역할의 구성원과 종점의 소유자만 종점에 연결할 수 있습니다. 사용자가 종점에 액세스할 수 있도록 연결 권한을 부여해야 합니다. 이를 수행하려면 다음 명령문을 실행합니다.

GRANT CONNECT ON HTTP ENDPOINT::hello_world_endpoint TO [DOMAIN\USER]

Microsoft 이외의 플랫폼에서 클라이언트는 BASIC 또는 SQL Auth 중 하나를 사용하여 SQL Server에 연결할 수 있습니다. 그러나 BASIC 또는 SQL Auth를 사용하려면 채널이 보안되어야 하므로 사용자가 활성화된 SSL이 있는 포트에서만 연결할 수 있습니다.

WSDL

WSDL은 웹 서비스를 설명하는 XML로 작성된 문서이며, 서비스가 노출하는 서비스 및 작업(또는 메서드)의 위치를 지정합니다. WSDL은 클라이언트가 웹 서비스와 상호 작용하기 위해 필요한 정보를 제공합니다. Visual Studio .NET 및 Jbuilder와 같은 도구는 WSDL을 사용하여 클라이언트 응용 프로그램이 웹 서비스와 통신하기 위해 사용할 수 있는 프록시 코드를 생성합니다. 종점에 활성화된 WSDL이 있는 경우에는 해당 종점이 WSDL 요청을 받을 때 WSDL을 만듭니다. 이 문서의 앞부분에서 만든 종점은 인증된 요청이 종점에 전송될 때 WSDL을 만듭니다. WSDL 요청은 폼의 간단한 HTTP Get 요청입니다.

http://servername/sql/demo?wsdl

서버는 종점에 연결된 메타데이터를 쿼리하고 WSDL을 동적으로 생성합니다. 생성된 WSDL은 저장 프로시저 매개 변수의 풍부한 형식 설명을 제공합니다. 서버는 여러 다른 특성의 WSDL을 생성할 수 있습니다(요청/응답 메시지에서 매개 변수를 설명하기 위해 기본적인 xsd 형식을 사용하는지 또는 복잡한 형식을 사용하는지에 따라 간단한 WSDL 및 복잡한 WSDL로 칭합니다). 기본적으로는 복잡한 형식을 사용합니다.

SOAP RPC: 메서드 호출

위에서 만든 종점에서는 이 저장 프로시저 master.dbo.hello_world를 SOAP RPC를 통해 실행할 수 있는 웹 메서드로 노출했습니다. 다음은 HTTP를 사용하는 SOAP를 통해 이 SP를 호출하기 위해 서버에 전송된 soap 메시지의 예제입니다.

<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<hello_world xmlns="http://tempuri.org/">
<msg>Hello World!</msg>
</hello_world>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

결과는 다음을 포함하는 SOAP Envelope입니다.

<SqlRowSet1 xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
<row>
<message>Hello World!</message>
</row>
</SqlRowSet1>

Batches: AdHoc 쿼리

T-SQL 명령을 사용하여 종점에서 BATCHES가 ENABLED로 설정된 경우 "sqlbatch"라고 하는 또 다른 SOAP 메서드가 종점에 암시적으로 노출됩니다. sqlbatch 메서드를 이용하면 SOAP을 통해 T-SQL 문을 실행할 수 있습니다. 이 메서드는 두 가지 매개 변수를 취합니다. 첫 번째 매개 변수는 이름이 ""이고 T-SQL 문의 배치입니다. 두 번째 매개 변수는 이름이 ""이고 선택적이며, T-SQL 문이 임의의 매개 변수를 사용한 경우 매개 변수 정보의 배열을 포함합니다. 다음은 이에 대한 예로서, sqlbatch 메서드를 호출하고 매개 변수화된 쿼리를 실행하는 SOAP 요청의 본문입니다.

<sqlbatch xmlns="http://schemas.microsoft.com/SQLServer/2001/12/SOAP">
<BatchCommands>
SELECT EmployeeID, LoginID, Gender
FROM Employee
WHERE EmployeeID=@x
FOR XML AUTO;
</BatchCommands>
<Parameters>
<SqlParameter name="x" sqlDbType="Int" maxLength="20"
xmlns="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlParameter">
<Value xsi:type="xsd:string">1</Value>
</SqlParameter>
</Parameters>
</sqlbatch>

이 SOAP 요청으로부터의 응답은 다음과 같습니다.

<sqlresultstream:SqlXml xsi:type="sqlsoaptypes:SqlXml">
<SqlXml>
<employees EmployeeID="1" FirstName="Nancy" LastName="Davolio"/>
</SqlXml>
</sqlresultstream:SqlXml>

관리

지금까지 종점을 만들고 종점에 대한 SOAP 요청을 전송하는 작업이 얼마나 간단한지 알아보았습니다. 이제 단 하나의 구성 요소, 즉 SQL Server만 관리하면 되므로 관리가 단순화됩니다. IIS 구성 요소를 관리할 필요가 없습니다. 종점 추상화는 IP 필터링을 활성화할 수 있는 관리자에게 더 많은 유연성을 제공합니다. 또한 종점 추상화 덕분에 http/https 웹 트래픽에 사용되는 포트를 재사용할 수 있기 때문에 또 다른 포트를 열 필요가 없습니다. 그리고 특정 사용자들에게만 CONNECT 권한을 명시적으로 부여함으로써 특정 개인에만 한하는 액세스를 위한 종점을 공급할 수 있습니다.

결론

Microsoft는 기본 SOAP 액세스를 이용하여 SQL Server에 액세스하기 위한 SOAP/HTTP와 같이 잘 알려지고 문서화된 표준에 기반한 프로토콜을 제공했습니다. 이로 인해 보다 광범위한 클라이언트가 SQL Server에 연결할 수 있어 상호 운용성이 촉진되고 액세스 도달이 용이해집니다.

[출처] Microsoft SQL Server 2005의 원시 XML 웹 서비스 개요|작성자 필립박

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

MsSql 날짜 함수 (0)	2008.04.28
SQL Server 2005에서 XML 데이터 형식을 위한 성능 최적화 (0)	2008.04.28
Microsoft SQL Server 2005의 XML 옵션 (0)	2008.04.28
저장 프로시저의 테스트 자동화 (0)	2008.04.28
mssql2000 = > mssql2005으로 복원 (0)	2008.04.28

PREV 1 ···64 65 66 67 68 69 70 ···74 NEXT

컴퓨토피아