MSSQL IDENTITY(자동증가열) 설정, 초기화, 해제

MSSQL에서 자동증가열이 필요한 경우 IDENTITY를 사용하며, IDENTITY 열은 대부분 생성된 테이블의 기본 키로 사용합니다.

IDENTITY 특징

• 새로운 행이 insert 할 때마다 자동으로 값이 증가합니다.
• IDENTITY 열은 초깃값을 설정할 수 있는데 보통은 1부터 시작합니다.
• 중간에 데이터를 삭제하면 해당 row의 IDENTITY 값은 재사용되지 않습니다.
• 중간에 비어있는 IDENTITY 값을 넣어주기 위해서는 IDENTITY_INSERT 사용해야 합니다.
• IDENT_INCR 사용하여 IDENTITY 자동 증가 값을 확인할 수 있습니다.
• IDENT_CURRENT 사용하여 IDENTITY 마지막 반환 값을 확인할 수 있습니다.
• @@IDENTITY, SCOPE_IDENTITY()의 차이점에 관해 설명합니다.

IDENTITY 사용 방법 및 예제

Auto-Increment 설정.

• IDENTITY 열은 자동으로 값을 증가시킵니다. 1부터 시작해서 행이 insert 될 때마다 1씩 증가합니다.
• CREATE TABLE 문에서 id 필드를 IDENTITY(1, 1)로 설정합니다.

-- 1. TAuto-Increment 설정 1씩 증가.
 
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForIDENTITY;
CREATE TABLE UserInfoForIDENTITY (
	IdKey   int  IDENTITY (1, 1)  PRIMARY KEY,
	UserId  int,
	UserNm varchar(255)  
) ON [PRIMARY];

INSERT INTO dbo.UserInfoForIDENTITY(UserId,UserNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda')
;

SELECT * 
FROM UserInfoForIDENTITY;

IDENTITY 사용법 설명 입니다.

IDENTITY_INSERT 이용하여 IDENTITY 재사용 방법.

• 중간에 비어있는 IDENTITY 값을 넣기 위해 SET IDENTITY_INSERT 명령을 사용하여 IDENTITY 값을 넣을 수 있습니다.

-- IDENTITY_INSERT 이용하여 IDENTITY 재사용 방법.
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForIDENTITY;
CREATE TABLE UserInfoForIDENTITY (
    IdKey   int  IDENTITY (1, 1)  PRIMARY KEY,
    UserId  int,
    UserNm varchar(255)  
) ON [PRIMARY];
INSERT INTO dbo.UserInfoForIDENTITY(UserId,UserNm) 
VALUES (1, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda')
;
-- 삭제
DELETE 
FROM UserInfoForIDENTITY  
WHERE USERID  IN ('273','274')
;
-- 확인
SELECT * 
FROM UserInfoForIDENTITY;

-- IDENTITY_INSERT on
SET IDENTITY_INSERT [dbo].UserInfoForIDENTITY ON;
INSERT INTO [dbo].UserInfoForIDENTITY(IdKey,UserId,UserNm) VALUES (2,273, N'IDENTITY_INSERT1');
INSERT INTO [dbo].UserInfoForIDENTITY(IdKey,UserId,UserNm) VALUES (3,274, N'IDENTITY_INSERT2');
-- IDENTITY_INSERT off
SET IDENTITY_INSERT [dbo].UserInfoForIDENTITY OFF;
--  결과 확인
 SELECT * 
FROM UserInfoForIDENTITY;

IDENT_SEED 함수.

• 테이블에 IDENTITY 열을 만들 때 지정된 초깃값을 확인할 때 사용합니다.
• DBCC CHECKIDENT 명령어를 사용하여 IDENTITY 초깃값을 변경해도 IDENT_SEED 함수에서 반환되는 값은 변경되지 않습니다.

-- IDENT_SEED 함수.
DROP TABLE IF EXISTS UserInfoForIDENT_SEED;
CREATE TABLE UserInfoForIDENT_SEED (
    IdKey   int  IDENTITY (3, 5)  PRIMARY KEY,
    UserId  int,
    UserNm varchar(255)  
) ON [PRIMARY];
-- 결과 확인
SELECT IDENT_SEED('[dbo].UserInfoForIDENT_SEED') AS 'IDENT_SEED';

IDENT_INCR() 함수.

• IDENTITY를 사용하는 테이블에 증가 값을 확인할 때 사용합니다.

-- IDENT_INCR 함수.
CREATE TABLE UserInfoForIDENT_INCR (
    IdKey   int  IDENTITY (1, 5)  PRIMARY KEY,
    UserId  int,
    UserNm varchar(255)  
) ON [PRIMARY];
-- 결과 확인
SELECT IDENT_INCR('[dbo].UserInfoForIDENT_INCR') AS 'IDENT_INCR';

IDENT_CURRENT() 함수.

• IDENT_CURRENT 함수는 지정한 테이블에 대해 최근 생성된 ID 값을 반환합니다.

-- IDENT_CURRENT 함수.
CREATE TABLE UserInfoForIDENT_CURRENT (
    IdKey   int  IDENTITY (1, 5)  PRIMARY KEY,
    UserId  int,
    UserNm varchar(255)  
) ON [PRIMARY];
-- 결과 확인
SELECT IDENT_INCR('[dbo].UserInfoForIDENT_CURRENT') AS 'IDENT_CURRENT';

@@IDENTITY, SCOPE_IDENTITY()의 차이점.

• 현재 세션의 IDENTITY 값을 반환하는 함수로 비슷하게 사용됩니다.
• 현재 세션의 IDENTITY 값을 반환하는 함수로 비슷하게 생각하지만 트리거로 사용할 때 IDENTITY 값을 확인하면 다르게 반환됩니다.
• SCOPE_IDENTITY는 현재 범위 내에 insert 된 IDENTITY 값을 반환합니다.
• @@IDENTITY는 특정 범위로 제한하지 않고 IDENTITY 값을 반환합니다.

@@IDENTITY,SCOPE_IDENTITY() 설명 입니다.

IDENTITY 주의 사항

• 데이터 삭제와 IDENTITY 값은?
• 중간 데이터를 삭제하면 해당 IDENTITY 값은 재사용되지 않습니다.
• 즉, 삭제된 IDENTITY 값은 다시 채우지 않고 계속 증가합니다.
• 그러므로 IDENTITY 값이 중간에 빠지는 경우가 발생할 수 있습니다.
• 복구 방법은 IDENTITY_INSERT 함수를 사용하면 됩니다.