[DB] MSSQL 자동증가열(IDENTITY)사용법 및 주의사항

테이블을 설계하다보면 고유ID값으로 로그성 정보나 이력관련 부분등 자동증가열을 사용하는 경우가 있습니다. IDENTITY값관련 사용법 및 주의사항에 대하여 살펴보겠습니다. 아래 4가지 순서로 확인해보겠습니다. 

1. 자동증가열 생성방법
2. ID값이 중간에 빠지는 경우
3. ID값 재사용방법.
SET IDENTITY_INSERT .. ON
4. ID값 가져오기.
ID값을 확인 시스템함수. @@IDENTITY,IDENT_CURRENT(),SCOPE_IDENTITY()


1. 자동증가열 생성방법
아래 스크립트문을 살펴보면 id필드값이  IDENTITY(1,1) 으로 설정된것을 알수있습니다. 초기값이 1로해서 1씩 증가하겠다는 의미입니다. IDENTITY(10,5)이런식으로 지정하면 초기값을 10으로 해서 5씩증가하도록 설정된 것있습니다. 

CREATE TABLE [dbo].[MyUser](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [name] [varchar](50) NULL
) ON [PRIMARY]
GO
insert into MyUser (name) values( '홍길동1')insert into MyUser (name) values( '홍길동2')insert into MyUser (name) values( '홍길동3')
select * from MyUser

3. ID값 재사용방법. SET IDENTITY_INSERT .. ON
비어있는 ID값을 넣기위해는 자동증가열에 ID값을 넣을수있도록 설정해주어야합니다. ID값을 지정해서 데이터를 넣으면 아래 이미지와 같이 에러가 발생합니다. 기본적으로 ID값을 넣지 않게되어있어서 SET IDENTITY_INSERT .. ON  명령을 통해서 ID값을 넣을수 있도록 활성화 해주어야합니다.

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }

 

 

4. ID값을 가져오기
ID값을 확인 시스템함수. @@IDENTITY,IDENT_CURRENT(),SCOPE_IDENTITY()

@@IDENTITY	현재 세션의 테이블에서 생성된 마지막 ID 값을 반환합니다
IDENT_CURRENT()	임의 세션 및 범위에 있는 특정 테이블에 생성된 값을 반환합니다.
SCOPE_IDENTITY()	현재 세션의 테이블에서 생성된 마지막 ID 값을 반환합니다

IDENT_CURRENT()의 경우는 현재 세션에 국한되는게 아니고 특정 테이블에 생성된 ID값을 반환합니다. 아래이미지를 보면 먼저 INSERT구분으로 3개의 행에 데이터를 INSERT후 조회하면 @@IDENTITY,IDENT_CURRENT(),SCOPE_IDENTITY()값이 모두 3이 나옵니다.  별도의 쿼리창(별도세션)을 띄어서 INSERT를 1건더합니다. 이러면 별도쿼리창을 띄운 화면에서는 값이 4가 나오는것을 확인할수 있습니다.  다시 최초세션에서 조회해보면 IDENT_CURRENT()값만 4가 나오는것이 확인할수 있습니다.  테이블단위로 생성된값을 반환해서 처리해야 될경우에는 IDENT_CURRENT() 함수를 사용하면됩니다. 

@@IDENTITY,SCOPE_IDENTITY()의 차이점
현재세션의 마자믹 ID값을 반환하는 함수로 비슷하게 사용됩니다. 언뜻보면 2개의 값이 동일해보이지만 트리거나 복제등연관된 작업이 있을경우 값에 차이가 있을수 있습니다.  SCOPE_IDENTITY는 현재 범위 내에 삽입된 값을 반환합니다. @@IDENTITY은 특정 범위로 제한되지 않습니다.

 
 
CREATE TABLE [dbo].[MyUser](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [name] [varchar](50) NULL
) ON [PRIMARY]
GO
 
insert into MyUser values ('홍길동1');
insert into MyUser values ('홍길동2');
insert into MyUser values ('홍길동3');
select * from MyUser
select @@IDENTITY  ,IDENT_CURRENT('MyUser'), SCOPE_IDENTITY() 
CREATE TABLE [dbo].[NewUser](
    [id] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY,
    [name] [varchar](50) NULL
) ON [PRIMARY]
GO
insert into [NewUser] values ('1');
insert into [NewUser] values ('2');
insert into [NewUser] values ('3');
select * from [NewUser]
 
select @@IDENTITY  ,IDENT_CURRENT('NewUser'), SCOPE_IDENTITY() 

결과를 보면 @@IDENTITY, SCOPE_IDENTITY() 값이 동일하게 나오는것을 확인할수 있습니다. 

MyUser테이블에 NewUser테이블로 INSERT하는 쿼리문을 트리거로 만든후 결과값을 확인해보겠습니다.  

CREATE TRIGGER trgNewUser  
ON MyUser  
FOR INSERT AS   
   BEGIN  
   INSERT [NewUser] VALUES ('')  
   END;  
 
GO   
 
insert into MyUser values ('홍길동4');
 
select * from MyUser 
select * from NewUser 
 
select @@IDENTITY '@@IDENTITY'  ,IDENT_CURRENT('MyUser'), SCOPE_IDENTITY() 'SCOPE_IDENTITY()'
select @@IDENTITY '@@IDENTITY' ,IDENT_CURRENT('[NewUser]'), SCOPE_IDENTITY() 'SCOPE_IDENTITY()'

쿼리결과를 확인해보면 @@IDENTITY값과 SCOPE_IDENTITY()이 다른것을 알수있습니다. 차이점을 확실하게 숙지하시고 사용하셔야 원하는 결과를 얻을수 있습니다. 

MyUser테이의 INSERT완료후 해당 ID값을 얻기위해서는 SCOPE_IDENTITY()를 사용하셔야 원하는 값을 얻을수 있습니다. 

declare @id int
insert into MyUser values ('홍길동4');
set @id =SCOPE_IDENTITY()
print @id 
 
/*
  결과 : 8 
*/

 

참고)
docs.microsoft.com/ko-kr/sql/t-sql/functions/identity-transact-sql?view=sql-server-ver15

@@IDENTITY(Transact-SQL) - SQL Server

docs.microsoft.com/ko-kr/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15

SCOPE_IDENTITY(Transact-SQL) - SQL Server