MSSQL 자동증가 열(컬럼) 추가 방법 (IDENTITY)

MS SQL에서는 행을 식별할 수 있는 고유한 일련번호를 자동으로 부여할 수 있게 컬럼(열) 속성으로 IDENTITY를 제공하고 있습니다. IDENTITY 속성을 많이 사용하지는 않지만 간혹 유용할 때가 있습니다. 저 같은 경우는 테이블에 PK(primary key)로 설정할만한 컬럼(열)이 없는 경우 IDENTITY 속성으로 PK를 설정합니다. 그리고 로그 테이블에서도 유용하게 사용하고 있습니다.

그럼 간단한 예제를 통해서 열(컬럼) 속성인 IDENTITY 대해서 알아보죠.

포스트 기준

– Microsoft SQL Server 2019

– SSMS 18.10

구문

구문 (syntaxsql)

IDENTITY [ (초기 값, 증분 값) ]

☆ 초기 값, 증분 값 둘 다 생략하면 초기 값 1, 증분 값 1이 적용됩니다.

사용 예제

1. 예제 1) 초기 값, 증분 값 생략한 경우

CREATE TABLE BS_DEMO_4 (
     COL_ID	INT IDENTITY	NOT NULL	PRIMARY KEY,	-- IDENTITY 초기 값, 증분 값 생략
     COL_NM	NVARCHAR(50)	NULL
)
     
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_1')
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_2')
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_3')
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_4')
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_5')
     
SELECT * FROM BS_DEMO_4

2. 예제 2) 초기 값, 증분 값 설정한 경우

CREATE TABLE BS_DEMO_5 (
     COL_ID	INT IDENTITY(100, 10)	NOT NULL	PRIMARY KEY,	-- IDENTITY 초기 값, 증분 값 추가
     COL_NM	NVARCHAR(50)		NULL
)
     
INSERT INTO BS_DEMO_5 (COL_NM) VALUES ('COL_1')
INSERT INTO BS_DEMO_5 (COL_NM) VALUES ('COL_2')
INSERT INTO BS_DEMO_5 (COL_NM) VALUES ('COL_3')
INSERT INTO BS_DEMO_5 (COL_NM) VALUES ('COL_4')
INSERT INTO BS_DEMO_5 (COL_NM) VALUES ('COL_5')
     
SELECT * FROM BS_DEMO_5

3. 예제 3) 초기 값, 증분 값 확인 방법

▶ IDENT_SEED: 초기 값 확인, IDENT_INCR : 증분 값 확인

SELECT IDENT_SEED('BS_DEMO_4') AS SEED_1	-- 초기 값 확인
     , IDENT_INCR('BS_DEMO_4') AS INCR_1	-- 증분 값 확인
     , IDENT_SEED('BS_DEMO_5') AS SEED_2	-- 초기 값 확인
     , IDENT_INCR('BS_DEMO_5') AS INCR_2	-- 증분 값 확인

4. 예제 4) 데이터를 삭제한 경우

DELETE FROM BS_DEMO_4 WHERE COL_ID = 3
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_6')
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_7')
     
SELECT * FROM BS_DEMO_4

5. 예제 5) ID 열(컬럼)을 저장하는 경우

☆ 기본적으로 ID 열(컬럼) 조작 불가능

INSERT INTO BS_DEMO_4 (COL_ID, COL_NM) VALUES (3, 'COL_3')

6. 예제 6) ID 열(컬럼)을 조작 가능하게 설정하는 방법

▶ ID 열(컬럼)을 굳이 변경하고자 할 때 설정 값을 변경

SET IDENTITY_INSERT BS_DEMO_4 ON	-- ID열 삽입 가능하게 설정
     
INSERT INTO BS_DEMO_4 (COL_ID, COL_NM) VALUES (3, 'COL_3')
     
SET IDENTITY_INSERT BS_DEMO_4 OFF	-- 원상 복구
     
SELECT * FROM BS_DEMO_4

7. 예제 7) 초기 값 세팅

▶ 테스트 완료 후 초기 값 세팅이 필요할 때가 있습니다.

▶ 구문 : DBCC CHECKIDENT(‘[테이블명]’, RESEED, 초기값)

☆ 데이터를 모두 삭제해도 ID 값은 삭제가 되지 않습니다.

DELETE FROM BS_DEMO_4
     
-- 원하는 값으로 초기값 세팅
DBCC CHECKIDENT(BS_DEMO_4, RESEED, 0)
     
INSERT INTO BS_DEMO_4 (COL_NM) VALUES ('COL_1')
     
SELECT * FROM BS_DEMO_4