한지붕두가족 MS SQL Server 2012 Identity 와 Sequence 엑셈컨설팅본부 /SQL Server 팀이제춘 1992 년 MS 가 Windows NT 에서운용되는첫번째 SQL Server(4.2) 를 2 출시한이후 20 년이흘렀다. 그간꾸준한발전을통해

Size: px

Start display at page:

Download "한지붕두가족 MS SQL Server 2012 Identity 와 Sequence 엑셈컨설팅본부 /SQL Server 팀이제춘 1992 년 MS 가 Windows NT 에서운용되는첫번째 SQL Server(4.2) 를 2 출시한이후 20 년이흘렀다. 그간꾸준한발전을통해"

대연 아
5 years ago
Views:

1 한지붕두가족 MS SQL Server 2012 Identity 와 Sequence 엑셈컨설팅본부 /SQL Server 팀이제춘 1992 년 MS 가 Windows NT 에서운용되는첫번째 SQL Server(4.2 를 2 출시한이후 20 년이흘렀다. 그간꾸준한발전을통해후발주자임에도 Oracle 과함께관계형 DBMS 시장의양대산맥으로자리매김하였다. 그리고지난 2012 년또하나의새로운버전 SQL Server 2012 를출시하였다. 이 SQL Server 2012 에서는고가용성향상을위한 Always on 기능및 BI 의 Power View, Cloud 환경지원등굵직한새기능들이포함되었다. 이러한기능들이외에도프로그래밍기능향상과같은 MS 의세심함을느낄수있는부분들이있는데그중하나가 Sequence 이다. 사실 Sequence 는지금까지 Oracle 에서사용되어왔던자동증가기능이었다. 이전부터 SQL Server 와 Oracle 의다른점으로거론된부분중하나가자동증가컨트롤에대해 SQL Server 는 Identity 를 Oracle 에서는 Sequence 를사용한다는것이었다. 헌데이두가지모두를 SQL Server 2012 부터사용가능하게된것이다. 자동증가기능이란 1, 2, 3, 4 혹은 2, 4, 6, 8 등과같이정해진시작값에서일정한값만큼 자동으로증가하는기능이다. 자동으로값이증가되므로중복되는값이없어테이블을운영하 며 Primary key 를적용할수있는일련번호등으로많이사용된다. SQL Server 2012 라는한지붕에살게된 Identity 와 Sequence 에대해알아보도록하자. 2 Microsoft 는 Sybase 와 1987 년부터협력을맺었고 1989 년 OS/2 기반 SQL Server 1.0 을발표한다 년 3 월 OS/2 용 SQL Server 4.2 를발표했는데이버전에있는데이터베이스엔진의소스코드가 MS 가 Sybase 로부터받은마지막코드이다. 발표직후양사는개발팀을분업하였고 1992 년 10 월 MS 개발팀에서개발한 Windows NT 용 SQL Server 4.2 를출시한다 년 4 월 Microsoft 와 Sybase 는협력관계를마감한다. Kalen Delaney 저, Microsoft Inside SQL Server 2000 발췌 기술백서 White Paper

2 안방마님 Identity Identity 는이전부터 SQL Server 에서사용한자동증가방식이다. 선언하는방법과사용은아 래와같다. CREATE TABLE Identity_Test ( Idt INT IDENTITY(1,1, Val VARCHAR(5 INSERT INTO Identity_Test(Val VALUES ('test' 3 SELECT * FROM Identity_Test Idt Val 1 test 2 test 3 test 테이블을생성해주면서사용하고자하는칼럼의자료형속성뒤에 IDENTITY( 초기값, 증가값 를추가해주기만하면끝이난다. 굉장히심플하다. 예시코드에서 IDENTITY(1,1 이므로초기값 1 부터 1 씩증가된다. 예시에서 INSERT 구문을통해두개의데이터를삽입하였고삽입된모습을보면 IDENTITY 속성이있는 Idt 칼럼이자동으로증가하여삽입되었음을확인할수있다. 주의할점은하나의테이블에는반드시하나의 IDENTITY 속성만을사용할수있다는것이다. 또한기본적으로 INSERT 작업시 IDENTITY 속성이있는칼럼은기재하지말아야한다. 드물게 IDENTITY 속성이적용되어있는칼럼의값을직접입력해야하는경우가있는데 SET IDENTITY_INSERT 테이블명 ON 위명령어를통해 IDENTITY 속성이적용된칼럼에도직접값을입력할수있다. 이때제약이설정되어있지않은경우이미기록된값도다시입력될수있는데이같은상황에서는중복값이없다는부분은거짓말이된다. 통상적으로이렇게사용되지않기때문에하는말이다. IDENTITY_INSERT 속성을 OFF 시키면다시자동증가기능이실행된다. 이때 IDENTITY 값은 Part 3 SQL Server 489

3 마지막입력된값이아닌해당칼럼의 MAX 값으로설정된다. ( 증가값이음수일경우는 MIN 값으로설정 자동증가기능이활성화된상황에서다시입력이될경우 [ 해당칼럼의 MAX 값 + IDENTITY 증가값 ] 이삽입된다. 필요한경우이 IDENTITY 값도변경이가능한데이는 DBCC CHECKIDENT 구문을사용하여확인및변경이가능하다. DBCC CHECKIDENT ( table_name [, { NORESEED { RESEED, new_reseed_value]}}] 다음구문을통해 IDENTITY 값을자동으로칼럼의 MAX 값으로변경시켜주거나현재 IDENTITY 값을확인, 또는직접 IDENTITY 값을지정해줄수있다. 뒤쪽에서언급한 IDENTITY 값을컨트롤하는내용들은사실자주사용되는기능은아니다. 기본 적으로사용되는구문만놓고본다면 IDENTITY 속성은참으로간단하고도편리한자동증가기 능이라볼수있다. 굴러온돌 Sequence SQL Server 2012 에서새롭게추가된 Sequence 는테이블밖에서선언된다. 위에서 Identity 를설명했을때와같은결과가나오도록하나의 Sequence 와함께연동될테이블을만들어보 자. CREATE TABLE Sequence_Test ( Seq INT, Val VARCHAR(5 CREATE SEQUENCE SeqTest START WITH 1 INCREMENT BY 1 ; INSERT INTO Sequence_Test(Seq, Val 기술백서 White Paper

4 3 VALUES (NEXT VALUE FOR SeqTest, 'test' SELECT * FROM Sequence_Test Seq Val 1 test 2 test 3 test 예시에서보다시피 Sequence 는테이블과별도로생성된다. 그러므로하나의테이블에여러개의 Sequence 가들어갈수있고하나의 Sequence 를여러개의테이블에서사용할수도있다. START WITH 문에서초기값을정해주고 INCREMENT BY 문을통해증가값을세트할수있다. INSERT 구문을보면알겠지만 NEXT VALUE FOR 문을호출할때마다 Sequence 가증가된다. 기본적으로 Sequence 는하나의개체이고여러인수를갖고있다. 간략한설명을위해 MSDN 에서제공하는 Sequence 기본구문을인용하였다. CREATE SEQUENCE [schema_name. ] sequence_name [ AS [ built_in_integer_type user-defined_integer_type ] ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ { MINVALUE [ <constant> ] } { NO MINVALUE } ] [ { MAXVALUE [ <constant> ] } { NO MAXVALUE } ] [ CYCLE { NO CYCLE } ] [ { CACHE [ <constant> ] } { NO CACHE } ] [ ; ] CREATE SEQUENCE 기본구문을보면 Sequence 의자료형식을지정해줄수있고최소값, 최대값지정과함께최대값을초과할경우반복여부도선택가능하다. 해당기능들과함께 SQL Server 에서기본 50 으로지정되는 CACHE 의크기도지정할수있다. 캐시는 Sequencer 를생성하는데필요한디스크 IO 를최소화시켜 SQL 의성능을향상시킨다. 그러나전원오류와같은비정상적종료가발생할경우캐시에미리저장했던값들은사라지고시스템테이블에있는다음번호가 Sequence 번호로할당된다. 예를들어캐시 10 을설정할경우 1, 2, 3 이입력된후비정상적으로중지되면캐시에할당된시퀀스번호는모두사라진다. 1, 2, 3 을입력받고바로 11 부터다시 Sequence 가할당된다. NO CACHE 옵션을사용할경우캐시영역을 Part 3 SQL Server 491

5 사용하지않기때문에누락되는번호가발생하지않지만 LOCK 으로인해성능면에서취약해 질수있다. 이외에도유용한기능으로값이들어가있는기존사용테이블에도 OVER 절을통해특정열을정렬하여 Sequence 를적용할수있다. 아래는 SeqTest 로선언한 Sequence 를 ID 오름차순으로하여 Sequence_test 테이블에입력하는예제이다. 자주사용되지는않겠지만운영중인테이블에기본키가없어곤란한상황에서는두손들고반길만한기능이다. SELECT NEXT VALUE FOR SeqTest OVER (ORDER BY ID AS 'Seq', ID, Phone, Address FROM Sequence_test 이같이 Sequence 는사용자에게높은프로그래밍자유도를제공하고있다. 여러면에서 Sequence 는 Identity 에비해나아보인다. 그렇다면앞으로 Sequence 가 Identity 를대체하고 Identity 는사라지게될것인가? 앞서 Identity 와 Sequence 를설명하며많은부분에대해이미알았겠지만직접적인비교를통해더알아보고자한다. Identity vs. Sequence? 사실이둘을동등한입장에두고비교한다는자체가우둔한일이아닐수없다. 서로비슷한일을할뿐이지근본적으로다르기때문이다. 대결구도가안된다는말이다. 그도그럴것이 Identity 는테이블에종속된속성 (Attribute 중하나일뿐이고 Sequence 는하나의개체 (Object 이기때문이다. 그래도구태여비교해보이자면아래와같은도표로나타낼수있다. < 표1> Identity vs. Sequence Identity Attribute Sequence Object 코드작성 간단 복잡 명확성 ( 명료성 높음 낮음 프로그래밍자유도 낮음 높음 옵션기능 적음 많음 기술백서 White Paper

6 Identity 는테이블에종속되는속성에불과하기에작성이매우간단하다. 하나의테이블에단하나만존재할수있고테이블생성시함께속성을설정하기에명확하게나타내는데에도유용하다. Sequence 는테이블과별개로사용이가능한개체이다. 그렇기에테이블에대해개수제약없이사용할수있고여러개의테이블에서하나의 Sequence 를사용할수도있다. 무엇보다도강력한것은다양한기능을사용할수있는프로그래밍자유도이다. 몇가지예를들어본다면 Identity 의경우 INSERT 되기전에는입력될값을미리알고사용할수없지만 Sequence 의경우 INSERT 와관계없이쿼리상어디서든값을증분하여사용할수있다. Sequence 는복수의테이블에하나의시퀀스로채번할수있고이미여러데이터가입력되어있는테이블에대해서도채번할수있다. 문자와조합된조합문자형식의채번또한수월하게할수있다. C 과같은문자조합채번을 Identity 와 Sequence 로각각구현하여비교해봤다. CREATE TABLE TableName( Idt INT IDENTITY not null PRIMARY KEY, Name VARCHAR(20 -- Identity 칼럼을인수로받아 Sequence 로가공 CREATE FUNCTION INT RETURNS CHAR(7 AS BEGIN RETURN C + right( END -- Sequence 로가공한값을넣어줄칼럼테이블에추가 ALTER TABLE TableName ADD SeqNumber AS dbo.funcname(idt INSERT INTO TableName (Name VALUES ( name 3 SELECT * FROM TableName Idt Name SeqNumber 1 name C Part 3 SQL Server 493

7 2 name C name C 먼저 Identity 를이용한쿼리이다. 3 Function 을이용한방법으로 Identity 를이용해조합문 자를생성하는여러방법중하나이다. 테이블에서조합문자를위해칼럼을하나더사용하고함 수를통해재가공하고있다. Trigger 혹은채번테이블을통해조합문자를생성하는방법도있지 만다른방법들도마찬가지로 Identity 속성이있는칼럼에대해직접적인변경작업을할수없 어참조해야만한다. 다음은 Sequence 를사용해문자조합채번을한쿼리이다. CREATE TABLE Sequence_Test ( CustomSeq varchar(10 not null PRIMARY KEY, Name varchar(20 CREATE SEQUENCE SeqTest START WITH 1 INCREMENT BY 1 -- INSERT 구문내에서문자조합을만들어바로입력 INSERT INTO Sequence_Test(CustomSeq, Name VALUES ('C' + right('000000' + CONVERT(VARCHAR(6, NEXT VALUE FOR SeqTest,6, 'name' 3 SELECT * FROM Sequence_Test CustomSeq Name C name C name C name 3 원본출처 : By Jeff Smith, 해당사이트에서 Identity 를이용해더복잡한 Custom Sequence Number 를생성하는방법도제공하고있다 기술백서 White Paper

8 구조를자세히보면 Sequence 를소개하면서만들어본쿼리와동일하다. 단지 INSERT 부분만 이다를뿐이다. 쿼리의길이로만본다면 identity 와크게다를바없어보이지만채번을위해 따로칼럼을사용하지않았고함수를사용하지도않았다. 쿼리분석도훨씬직관적이다. 앞서언급한예들이외에도다양한기능을사용하거나옵션을두고자할때, 혹은 Identity 로작 성하기불편했던많은상황을 Sequence 로해결할수있다. 넝쿨째굴러들어온복, Sequence SQL Server 2012 의 New Feature, Sequence 는굴러온돌이아닌굴러들어온복덩이다. 왜이제서야나타났는지싶다. Identity 의단점인프로그래밍취약점을 Sequence 를통해커버할수있게되었다. 지금까지 Identity 를여러방법으로변형해서사용하는수고로움을덜수있게된것이다. 그렇다고 Identity 를버리고모두 Sequence 를사용할필요는없다. 숫자로된일련번호목적으로만사용할경우편리하고명확한 Identity 를사용하고추가적인기능이필요하다면 Sequence 를사용하면된다. 닭잡는데소잡는칼을쓸필요없지않나. MS 에서제공하는가이드나권장사항은없지만앞서말한용도에맞게 Identity 와 Sequence 를사용하는것이좋겠다. Part 3 SQL Server 495

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역시쉽게해결할수있다. 이번화이트페이퍼에서는 Window Function 중순위 RANK, ROW_NUMBER,