Tobias Ternstrom: 안녕하세요. 저는 Tobias Ternstrom 입니다. 저는 SQL Server 2012 의향상된프로그래밍능력에대해설명하고자합니다. 먼저새로운기능을 살펴보고, 그런다음새로추가한스칼라함수이야기를조금해본후, 마치려고합니다. 저희가처음한일은, 많은사람들이필요로했던여러개의테이블이나, 여러서버에서 식별가능한유일한 (unique) 값을생성하기위한언어기능입니다. SQL Server 7 에부터 GUID 가지원되었고, identity 속성도지원됩니다. GUID 의문제는, 모든환경을 만족시킬수없다는점인데, 특히여러분이계속증가하는값을원하고, 적은공간을 사용하는데이터형식을이용하고싶어한다면문제가될수있습니다. Identity 속성은단일테이블에서만지원됩니다. 그래서우리가한일은 ANSI 표준 시퀀스생성기에대한지원을추가하는것이었습니다. 시퀀스 (sequence) 개체를생성하고, next vaule for 함수로단일값일련번호를생성할수있습니다. sp_sequence_get_range 저장프로시저를이용해일정범위의일련번호를생성할수있습니다. 새로추가된시퀀스개체의강력한기능을활용하면, 플랫폼간의마이그레이션에도도움을얻을수있습니다 그럼 S1 시퀀스를생성하고슬라이드에보이는것처럼 next value FOR 함수를이용, 시퀀스를호출합니다. 보시다시피, 해당형식의가장낮은최소값을반환하며, 기본값은해당데이터형의최소값입니다. 이는도메인의전체범위또는선택된형식의전체도메인을사용하기위해서필요한것입니다. 시퀀스를삭제하고다시만드는대신시퀀스를변경할수도있습니다. 시퀀스개체의데이터형식만변경 (alter) 할수없습니다. 계속해서, 정수를선택하고, 1 로시작합니다. 이제호출하면, 1,2,3 등으로시작하는것을알수있습니다. 이제, 더가서좀더복잡한쿼리를실행해보겠습니다. 고객에게판매한결과가반환되며, 지속적으로고유한값이만들어지고있음을확인할수있습니다.
그런데, 이러한쿼리를동시에여러개실행할수도있습니다. 트랜잭션동안에는 next value FOR 는차단되지않을것입니다. 고성능환경작업을수행하고있다면, next value for 함수의결과값이징검다리형식으로반환됩니다. 먼저실행한쿼리가 1,2,3 을반환받은다음,, 두번째쿼리는 4,5,6 을받고, 그다음첫번째쿼리는등등. 시퀀스를테이블에서제약조건으로사용할수있습니다. 매출.T1 테이블을만들고, ID 칼럼은기본값은 next value for S1 으로입력됩니다. T2 에서도사용할수있습니다. 두테이블에걸쳐 ID 가생성되었는데실제로유일한값으로생성되고서로차단이발생할일은없을것입니다. sp_sequence_get_range 저장프로시저를사용해일련번호범위를생성할수있습니다. 예를들어, 천개정도의값을범위로할당받은다음, 오프라인이나원격환경에서사용할수있도록별도의파일로준비하여데이터적재계획을수립할수도있고. 또는백만개값을사전에할당하여다른서버에할당할수도있습니다. 이제이서버는로컬서버와충돌할수있는값을생성하지는않습니다. 시퀀스개체에대해살펴보았으니, 다음은쿼리페이징기능에대해살펴보겠습니다. 특히이기능은지금까지고민해왔던문제를해결하는매우단순한솔루션을제공해줄것입니다. 어떤환경에서든첫번째페이지와두번째및그이후페이지를요구하는것은흔한일입니다. X 행의 offset 을주고, 그다음 X 행값을가져옵니다. 그럼데모를해보겠습니다. 이러한페이징쿼리를지원하기위해, SQL Server 2012 에서는 OFFSET 과 FETCT NEXT 키워드를제공합니다. 이는 ANSI 표준으로정의된기능입니다. 다시고객테이블에간단한쿼리를실행합니다. 고객테이블첫번째행을가져오고, 그다음두번째행, 등등. 물론, 조인, GROUP BY 등다양한구문을사용할수있습니다. 그냥단순한쿼리로하겠습니다. 첫번째페이지행만을가져오도록합니다. 다음은첫번째페이지를위한행은제외하고, 그다음페이지를위한 10 개의행을 반환하도록합니다. ORDER BY 절을제대로지정해야합니다. 원하는결과를명확하게 반환하도록하기위해서는 ORDER BY 절에유일한 (unique) 값을가지는칼럼이
포함되도록설정해야합니다. 그래야명확하게원하는결과에해당하는두번째페이지 행을반환할수있습니다. 앞에서이야기한것과같이 ORDER BY 절에유일한식별자인고객 ID 를추가합니다. 이제첫번째페이지를위한 10 개의행을제외하고, 그다음페이지를위한 10 개의행을반환할수있습니다. 또한이부분에서변수와식 (expression) 을사용할수있습니다. 만약, OFFSET 절에 10 행씩 10 번을지정하면, 100 으로계산하여쿼리를수행할수있습니다. 해당위치에서브쿼리 (sun-query) 도쓸수있으며, 매우단순한방법으로페이징쿼리를수행할수있습니다. 좀더단순한오류처리 (error handling) 를위해 Throw 명령을지원합니다. Throw 를사용해서오류를발생시킬수있으며, 어떤범위에속해있던, Throw 명령을통해바로해당위치에서중단 (abort) 할수있습니다. 만약 TRY 블록에서오류가발생하면, 보통의다른언어와같이 CATCH 블럭 block 으로옮겨서, 어떤인수없이바로 THROW 명령을호출할수있습니다. 많은고객이필요하다고요청한현재오류를그대로다시발생시키는기능이었습니다. OVER 절에대한지원을확장하고, WINDOW 함수에서이를참조할수있도록하였으며, 좀더분석함수를지원하기위해많은투자를수행하였습니다. 이는일반적인 SQL 로는 작성하기어려운유형의쿼리를매우단순하게해결할수있는기능을제공합니다. over clause 에대한지원연장을확인하는와중에, 윈도우함수 (window function) 와해석함수 (analytical function) 를추가하는일이었습니다. 이것은단순유스케이스 (use case) 를풀어내는데있어대단한중요한기능으로, 통상의 SQL 에서는쓰기힘듭니다. 또한, 매우수준높은쿼리를처리할때도움이됩니다. 예를들어마지막 5 분동안의주식가격의평균또는단계적평균 (rolling average) 을구하는쿼리. 예를들어, 최근 5 분동안주식가격의평균등매우고난이도쿼리를좀더쉽게처리할수있습니다.
실제어떻게구현할지예를들어보겠습니다. 고전적인예제인데, 시간에따른계좌의총누적잔액을알아내는문제입니다. 이미언급했듯이, 이전버전의 SQL Server 로도구현은가능하였으나, 성능까지고려한다면그리쉽지않은문제였습니다. SQL Server 2012 에서는 ANSI 표준을지원하기위해노력을강구해왔으며, 이기능을통해좀더편하게업무를수행할수있게되었습니다. 새로운기능을배우기위해어느정도시간을소비해야한다면, 이번에는좀더추가로시간을더써도될만큼득도의경지를제공해줄기능이며, 여러분이매우좋아할만한기능이될것입니다. 여기주문관련테이블을간단한쿼리를만들어보겠습니다. 주문과주문상세테이블을주문 ID 를기준으로조인합니다. 그다음, 주문을정렬하기위해, 고유한값을가지는주문 ID 와주문상세 ID 순으로정렬합니다. 주문상세테이블을은행업무시스템의거래테이블을나타내는것으로가정하겠습니다. 이쿼리를실행하면전체값을조회할수있습니다. 이제부분합칼럼이나누적합계를계산해보도록하겠습니다. 주문 ID 와주문에대한기본적인정보, 그리고, 주문에대한거래내역을조회하고자합니다. 지금보는행은부분합칼럼이아니라단가칼럼입니다. 여기에누적합계를추가하려고합니다. 이를위해단가를모두더하면된다고생각할수도있겠습니다. 이쿼리를실행하면, GROUP BY 절에그룹조건이누락되었다는오류메시지가나타납니다. SQL Server 2005 이후버전에서는이를처리하기위해 OVER 절을사용할수있었습니다. 이와같이각주문 ID 별로누적합계를계산할수있습니다. 이제최종결과에필요한모든칼럼을계산하였습니다. 마지막으로, 맨마지막부분에총계정잔고또는총잔고와모든거래의총합계가계산되어있습니다. 이제다음할일은결과를다음과같은칼럼을기준으로하여정렬하는것입니다. 기본적으로표준에따라암시적으로첫번째행부터현재행까지일련의순서로나타나게됩니다. 이제, 잔고를확인할수있습니다. 즉, 5 부터 10 까지더해서 45 등과같이계산되며, 이미앞에서언급한바와같이비교적단순하게쿼리를작성할수있고,
Window 함수의 ORDER BY 절의기본범위값으로 UNBOUNDED PRECEDING AND CURRENT ROW 가사용된다. 이러한기본값은실제값으로번역되어져사용된다. 하지만, 이예제에서는실제범위로되어도차이점이발생하지는않습니다. 다만, 이번버전에서도이와유사한쿼리를작성할수는있었지만, 이제는좀더쉽고편하게쿼리를작성할수있게되었습니다. 앞에서언급한바와같이, LAG(), LEAD() 등의분석함수에도 OVER() 절을지정하여사용할수있습니다. 이러한새로운기능을활용하기위해조금만자기계발을위해투자하게되면, SQL 의강력한힘을체험할수있을것입니다. 이제마지막으로 SQL 언어추가된에단순하고, 재미있는함수를소개해드리겠습니다. 예를들면, Try_Convert() 와 Try_Cast() 인데, 기본적으로어떤다른형식으로변환을시도하고, 실패하면 null 을반환합니다. 숫자값을날짜로형변환하기위한사용자정의함수가있다면, 이두함수로유효성검사를할수있습니다. 스트링을로드한후, 특정형식으로변환하고싶은통합시나리오에도좋습니다. 서식 (format) 함수도추가되었는데,.Net 기능을이용해서날짜나숫자같은형식을특정 서식으로변환할수있습니다. 일반적으로.NET 에서는해당값을문자열로표현하거나, 특정한형식의특정한언어설정을가지도록변환할수있습니다. Parse 와 TRY_Parse 는이들의비슷한버젼입니다. Parse 는언어설정이지정된특정문자열을받아서, 이를해당하는데이터형식으로변환하는기능을하고, TRY_PARSE() 는 Try_Convert() 와 Try_Cast() 와유사해서, 구문분석이실패하면 null 을반환합니다. 많은사람들이기다리던 IIF 함수 () 도추가했습니다. Choose() Concat(), 기본적으로여러분이엑셀이나액세스같은다른마이크로소프트상품을사용하고있다면이미공통적으로사용하는기능과동일한기능을제공합니다. 마지막으로저희날짜데이터형식에대응하는몇가지컨스트럭티브 (constructive) 함수를추가했습니다. 이러한함수는정적인날짜데이터를생성하는데매우유용하게
사용될수있습니다. 날짜관련데이터형을구성하는구체적인단위를정수값으로입력받아, 날짜데이터형으로구성합니다. 년, 월, 일, 시, 분, 초, 오프셋, 사용하고자하는함수등을지정하여날짜데이터를구성할수있습니다. 또한특정월의마지막날짜, 마지막일자를계산하기위한 EOMONTH() 함수등도지원됩니다. 그러면, 몇가지예를보고, 끝내기로하겠습니다. 왜이두개의함수가유익한지설명드리겠습니다. 앞에서언급했듯이, 주어진 데이터가어떤데이터형식으로변환이가능한지확인하고자할때, Try_Convert() 와 Try_Cast() 는매우유용하게사용할수있습니다.Net 환경에서제공하는함수를사용하는것같이작동합니다. 예제를위해 sales 스키마의주문상세테이블과 production 스키마의제품테이블을사용합니다. 이제데이터형변환을시도하려한다고합시다. 생산품번호가실제로정수로변화가능하니, 일단모든값을가져옵니다. 정수형으로형변환할수없는이름도존재하기때문에 Try_Cast() 함수의결과가 null 값이반환된다는것을확인할수있습니다. 왜냐하면분명히정수값으로변환이불가능하기때문이죠. 이러한경우, CASE 구문을사용하여 TRY_Cast() 함수의결과가 not null 인건만을선택할수도있습니다. 이경우, CAST 작업이성공한경우, IS_NUMERIC() 이나 IS_DATE() 함수를사용하여다시한번검증할수있습니다. 이제모든행이정수형으로변환가능하다는것을보장할수있습니다. 다음은 FORMAT() 함수에대해살펴보겠습니다. 오랫동안요청되어왔던함수중에하나입니다. 단순하지만, 삶을편리하게해주는함수죠. 주문테이블에서주문날짜열을조회합니다. 이를다양한형식의서식으로다음과같이표현할수있습니다. 그래서, 원래의설정에따라, 스웨덴언어설정을선택하는데, ISO 표준을따라서인지, 인간이읽을수있는글자들입니다. 년, 월, 일이라고해석할수도있지만, 특정국가에서는년월일이아닌월일년으로해석할수도있습니다.
어쨌든, 날짜데이터를근거로하여무엇인가주문번호를생성하기위해 FORMAT() 함수를사용해보도록하겠습니다. 날짜형칼럼을다음과같은포맷문자열에따라특정 형식으로표현할수있습니다. 보시다시피, 년, 월, 일데이터를시간정보없이그리고년, 월, 일사이에아무것도없도록설정된형식으로표현할수도있습니다. 또한두개의언더스코어 (_) 가표현되도록할수있으며, 영어표현으로월을 July 요일을 Friday 로표시할수있도록설정할수도있습니다. 이는매우단순하지만, 매우훌륭하게동작하는기능으로제품에내장되어구현되어 있습니다.