MS-SQL Server Identity 옵션

DB 테이블을 만들 때 검색 속도의 향상(인덱스가 자동으로 생성되기 때문이죠.)과 검색의 편의성을 위해 대부분 기본키를 한 개 이상 반드시 사용하게 됩니다. 그런데 이 기본키로 설정된 컬럼은 거의 대부분 정수값을 사용하도록 하고, 중복된 값을 가지지 않으며, NULL값을 가질 수 없도록 하는 제약조건을 가지도록 합니다.

이러한 이유 때문에 서버딴에서 JSP나 PHP, ASP 등 Server side language 종류에 무관하게 기본키로 설정된 컬럼에 들어갈 값을 구한 후 해당 DB 테이블에 Insert 처리를 하게 됩니다.

기본키로 설정된 컬럼의 값을 구하는 방법은 대체로 아래 3가지를 사용하지 않을까 합니다.

1. DB에 시퀀스(Sequence)를 생성 후 이 시퀀스를 이용하여 고유값을 받아 처리하는 방법

2. 해당 DB Table의 기본키로 설정된 컬럼의 값 중에서 최대값을 구하여 +1 계산하는 방법

 - ex) select max(idx) + 1 as max_idx from dept;

3. 각 DBMS에 존재하는 함수나 옵션을 활용하는 방법

여기서 저는 3번에 대한 방법을 간략하게 소개하고자 합니다. 3번의 방법은 DBMS마다 다릅니다. DBMS 종류에 따라 시퀀스나 값 자동증가 옵션(혹은 함수)가 존재하지 않을 수 있고, MySQL에서는 AUTO_INCREMENT 라는 옵션을 이용하고 MS-SQL Server의 경우엔 Identity 옵션을 사용하는 등 차이가 존재합니다. 여기서는 MS-SQL에 대한 내용을 간략하게 다루겠습니다.

아래는 MS-SQL Server의 Identity 옵션을 사용한 예제입니다.

create table DEPT(

idx int primary key identity(1,1),

dept_name varchar(200),

dept_code varchar(200) 

);

위 SQL Query문은 DEPT라는 테이블을 생성하는 질의문입니다. 여기서 idx라는 컬럼이 기본키가 되며, identity라는 옵션을 통해 자동으로 기본키 값이 적용되도록 하였습니다.

identity(시작값, 증가값) 으로 적용되기 때문에 위에서 identity(1,1)은 "1부터 시작하여 1씩 증가해라." 라는 의미를 가지게 됩니다. 시작값과 증가값 두 가지를 이용하여 상황에 맞게 적절히 처리하시면 되겠습니다.

이것은 마치 시퀀스와 매우 흡사합니다. 시퀀스도 시작값과 증가값 모두 설정할 수 있고, 시퀀스를 호출할 때 마다 중복되지 않는 값을 가져올 수 있기 때문입니다. 따라서 MS-SQL에서 시퀀스가 필요한 상황이라면 identity 옵션을 사용해보시기 바랍니다.

identity 옵션이 설정된 경우에는 해당 컬럼의 값이 자동으로 삽입됩니다. 따라서 아래와 같은 질의문은 사용이 불가합니다.

insert into DEPT(idx, dept_name, dept_code)

values(1, '인사과', 'dept-100');

idx 컬럼이 identity 옵션이 적용되어 값이 자동으로 부여되는 컬럼으로 설정되었기 때문입니다. 따라서 위 질의문을 아래와 같이 수정하여야 합니다.

insert into DEPT(dept_name, dept_code)

values('인사과', 'dept-100');

identity 옵션이 설정된 컬럼을 제외한 나머지 컬럼의 데이터를 삽입하도록 하는 것입니다. 위 Insert 쿼리문을 실행하시면 idx 컬럼의 값이 자동으로 중복되지 않는 값이 들어가는 것을 확인할 수 있습니다.

아래는 URL은 identity에 대한 추가적인 내용이 나와있는 네이버 블로그 주소입니다. 참고하시면 좋을 것 같습니다.

 - 네이버 블로그 : http://cafe.naver.com/itinsuccess/5