MS-SQL 자동증가 컬럼, Identity 사용하기

오라클의 Sequence 와 비슷한 기능으로
MS-SQL 에는 Identity 라는것이 있다.
Identity 는 오라클의 Sequence 와는 다르게, 테이블 컬럼의 속성으로 되어 있다.

 

CREATE TABLE test1 (     seq int identity(1,1),     val varchar(50) NULL )

 

identity(1,1) 에서 앞에 1은 시작값, 뒤에 1은 증감값이다.
즉, 최초값을 1로 할수도 있고, 1000000 으로 할수도 있으며,
증감을 1씩 증가하게 할수도 있고, 100씩 증가하게 할수도 있으며,
반대로 -1 씩 감소하게 할수도 있다는 뜻이다.

 

insert into test1(val)
vals ( '1' );
insert into test1(val)
vals ( '2' );

 

테이블에 인서트 할때는 identity 컬럼을 지정할수 없다.
identity 컬럼을 제외한 컬럼에만 값을 지정하면, identity 컬럼은 자동으로 값이 할당된다.

 

seq val
1 1
2 2

 

DBCC CHECKIDENT 를 이용하여, 특정 테이블의 identity 값을 확인해볼 수 있다.

 

현재 identity 컬럼의 값이 얼마인지 확인하는 방법

DBCC CHECKIDENT('test1', NORESEED)

 

identity 값을 변경하는 방법

DBCC CHECKIDENT('test1', RESEED, 1000)

 

이 값은 문자열 형태로 출력이 된다.

 

 

BTW, 프로시저에서 identity 컬럼이 있는 마스터 테이블에 insert 를 한 후에,
방금 insert 하면서 생성된 identity 컬럼의 값이 필요한 경우가 있다.

 

identity 값을 실제 값으로 가져오는 방법은 3가지가 있다.

 

SELECT @@IDENTITY SELECT IDENT_CURRENT('test1') SELECT SCOPE_IDENTITY()

 

1. @@IDENTITY

최종 입력된 identity 값을 가져오지만, 그 값이 a라는 테이블이든지 b라는 테이블이든지, 상관이 없이,
현재 세션내의 모든 범위에서 가져온다.

 

2. IDENT_CURRENT

지정한 테이블의 마지막 identity 값을 가져온다. 그러나, 동시에 테이블에 인서트 되는 경우에도,
다른 사람이 인서트한 마지막 값을 가져올수가 있다.

 

3. SCOPE_IDENTITY

현재 세션의 범위 내에서만 가져온다. 즉, insert 하고 나서, 생성된 identity 값만 가져온다.

 

일반적으로, @@IDENTITY 를 사용하는 경우가 있는데, 이런경우, 논리적 오류가 나올수 있다.
프로시저 안에서 insert 후에 identity 값을 가져올때는 항상 SCOPE_IDENTITY 를 사용하도록 하자.

 

추가적으로, 중간에 데이터가 삭제되면서, identity 컬럼의 값이 중간에 비어 있을때,
중간 값을 채워주고 싶다면 아래와 같이 하면 된다.

 

SET IDENTITY_INSERT test1 ON;   insert into test1(seq, val) vals(9, 'inserted');   SET IDENTITY_INSERT test1 OFF;

 

IDENTITY_INSERT ON 으로 하면, identity 컬럼에 수동으로 값을 넣겠다는 뜻이다.