[MSSQL] identity 컬럼

1. identity 컬럼이란?

---

[identity 컬럼 정의]

identity 컬럼이란 MSSQL에서 사용되는 숫자 자동 증가 기능입니다. 테이블에 추가되는 각 행의 고유 숫자 값을 자동으로 생성하는 방법입니다. 주로 PK(Primary Key)로 많이 사용합니다. int와 같은 숫자형 컬럼 타입에만 적용이 가능하며, varchar, char와 같은 문자형 컬럼에는 적용이 불가합니다.

 

[identity 컬럼 특징]

  - 하나의 테이블은 하나의 identity 컬럼만을 가질 수 있습니다.

  - 숫자형의 테이터 타입만을 정의합니다.

  - 암호화 알고리즘을 적용할 수 있습니다.

 

2. identity 컬럼 사용 방법

---

[테이블을 생성할 때 identity 컬럼을 적용]

CREATE TABLE table 
(
	col1 INT,
	col2 v,
	col3 INT NOT NULL IDENTITY(START WITH 100, INCREMENT BY 2)
)

[기존 컬럼을 identity 컬럼으로 변경]

identity 컬럼은 데이터를 수반해 생성되기 때문에 컬럼 자체를 변경하기 위해서는 ALTER COLUMN을 사용할 수가 없습니다.

ALTER COLUMN을 사용하지 않고 기존 컬럼을 identity 컬럼으로 변경하는 방법은 데이터를 TEMP테이블로 모두 이동 시킨다음 데이터를 삭제합니다. 데이터 없는 테이블에는 identity 컬럼을 적용할 수 있으므로 적용 후 다시 TEMP테이블의 데이터를 원래 테이블로 옮기는 방식입니다.

-- 원본 데이터 복사
select * into [temp테이블] FROM [테이블]

-- 원본 데이터 삭제
delete from [테이블] 

-- identity 컬럼 적용
ALTER TABLE [테이블] ADD [컬럼] [숫자형 데이터형식] IDENTITY ([초기값], [증가값]) [제약조건]

-- 원본 데이터 복구
insert into [테이블] select * from [temp테이블]

[identity 속성 초기화 ]

DBCC CHECKIDENT('[테이블명]', RESEED, 초기값)

[identity 증가값 수동 여부 설정]

ON : 증가값을 수동으로 지정할 수 있도록 세팅 수정

OFF : 증가값을 자동으로 지정할 수 있도록 세팅 수정

SET IDENTITY_INSERT [테이블명] ON;
SET IDENTITY_INSERT [테이블명] OFF

 

3. identity 컬럼 사용 중 주의사항

---

[identity  컬럼 삭제할 경우]

category	name	idx(identity)
DOG	happy	1
CAT	nabi	2
DOG	nuri	3

이 테이블에서 3을 삭제 한 후 새로운 값을 삽입할 시에는 3이 아닌 4로 값이 들어가게됩니다.

DELETE FROM [테이블] WHERE IDX = 3;

INSERT INTO [테이블] (CATEGORY, NAME) VALUES ('DOG', 'BANA')

category	name	idx(identity)
DOG	happy	1
CAT	nabi	2
DOG	bana	4

 

[identity 컬럼 삽입 중 트랜잭션이 실패했을 경우]

category	name	idx(identity)
DOG	happy	1

이렇게 idx 컬럼에 identity를 적용한 테이블에 새로운 값을 insert를 하는 도중 트랜잭션이 실패하면 identity값은 어떻게 될까요? identity 칼럼을 가지고 있는 테이블에 insert를 하는 도중 트랜잭션이 실패하여 rollback 작업을 하게 될 수도 있습니다. 이렇게 되면 다음 테이블 insert 때 identity 칼럼은 값을 이전에 실패했던  identity 칼럼은 insert 할 때마다 증가하기 때문에 rollback과는 상관없이 증가합니다.

[identity 컬럼 중복으로 입력 가능 여부]

 identity 컬럼에 PK가 적용되어 있지 않다면 identity컬럼은 유일성이 보장되지 않기 때문에 중복으로 입력이 가능합니다.

[identity 컬럼  초기화 값 재설정]

테이블 생성 시 identity 칼럼에 적용했던 초기화 값은 재설정이 가능합니다. identity 컬럼에 PK가 적용되지 않아 유일성이 보장되지 않는 상황에서 초기화 값을 재설정했을 시 기존에 있던 값과 중복이 발생할 수 있습니다. 유일성이 보장되지 않는 컬럼이라면 초기화 값을 재설정 하는 것을 주의해야 합니다.

DBCC CHECKIDENT([테이블명], reseed, [초기화 시킬 값])