MSSQL 자동 증가값(IDENTITY) 설정 방법

MSSQL 을 사용할 때 거의 필수적으로 사용하는 자동 증가값(IDENTITY) 설정 방법에 대해 알아보겠습니다.

 

일반적으로 테이블에서 컬럼 생성 시에 자동 증가값을 설정하려면 다음과 같이 int identitity(1,1)을 설정해주시면 됩니다. 그러면 INSERT 시에 특정한 값을 주지 않아도 기존 값에 +1을 더한 값이 자동으로 들어가겠죠?

CREATE TABLE IDENT (
    T_IDX int identity(1,1) NOT NULL
)

혹시 그 값을 임의로 지정하거나 수정하고 싶다면 어떻게 해야할까요?

INSERT INTO IDENT(T_IDX) VALUES (100);

기본적으로 identity 값을 설정하면 수동으로 값을 넣을 수 없기 때문에 위와 같이 INSERT 구문을 사용하면 오류가 납니다. 따라서 아래와 같이 기본 세팅 변경 후 원하는 값을 삽입할 수 있습니다.

--증가값을 수동으로 지정할 수 있도록 수정
SET IDENTITY_INSERT [테이블명] ON;

--원하는 값 입력
INSERT INTO IDENT('[테이블명]') VALUES (100);

--증가값 자동 지정으로 세팅 변경
SET IDENTITY_INSERT [테이블명] OFF;

또 identity 값은 DELETE 문을 사용해 삭제하면 다시 그 값을 사용할 수 없습니다.

 

예를 들어 identity 값이 2인 데이터를 지우고 INSERT 문을 실행하면 다시 2가 들어가는 것이 아니라 2의 다음 값인 3이 들어가게 됩니다. 데이터 전체를 삭제하고 INSERT 문을 실행해도 그 값이 1부터 시작하는 것이 아니라 이전 데이터 identity 최대값의 다음번호부터 시작하게 되는 것이죠.

 

따라서 아래와 같이 현재 id값을 확인하고 원하는 값으로 세팅을 할 수 있습니다.

--현재 id값 확인
SELECT IDENT_CURRENT('[테이블명]')  

--증가값을 수동으로 지정할 수 있도록 수정
SET IDENTITY_INSERT [테이블명] ON;

--원하는 값으로 초기값 세팅
DBCC CHECKIDENT('[테이블명]', RESEED, 초기값)

--증가값 자동 지정으로 세팅 변경
SET IDENTITY_INSERT [테이블명] OFF;

모든 데이터를 삭제하고 아예 초기화를 시키고 싶으면 DBCC CHECKIDENT ('[테이블명]', RESEED, 0); 이런 식으로 초기값을 0으로 설정하면 되겠죠.

 

지금까지 MSSQL의 자동 증가값(IDENTITY) 설정 방법에 대해서 알아봤습니다. 하지만 일반적으로 id 값을 최대한 수정하지 않는 방향으로 설계와 운영을 해야할 것 같습니다. 개인적으로도 오픈 전 테스트 시 말고는 쓰지 않는 것 같습니다.