SQL SERVER - Identity Jump 1000 ( 자동증가 컬럼 )

Identity Jump 1000

테이블의 속성 중 Identity (1,1)으로 컬럼을 만드는 경우가 많습니다. 1에서 시작하여 1씩 증가하도록 만들어 데이터가 쌓이는 순서대로 채번을 하는 경우입니다.  이렇게 설정된 테이블의 정보를 보면 Seed 1 , Increment 1 로 되어 있습니다.

그런데 MS SQL 2012 이상부터는 SQL Service가 재시작 되는 경우 이 컬럼의 번호가 1000 씩 건너뛰는 경우를 보실 수 있습니다. 아래 그림을 보시면 208, 209, 210, 211, 212 였으나 갑자기 1195, 1196으로 번호가 증가하는 것을 확인할 수 있습니다.

 

Jump 1000 현상이 문제인가?

SQL Service가 재시작 되는 경우 Identity 값이 1000 씩 jump 하는 것이 잘못된 것인지는 생각해보아야 합니다. Identity 속성으로 컬럼을 지정하는 것은 데이터가 쌓이는 순서대로 번호가 생기고 중복이 발생하지 않기 위함입니다.

반드시 1,2,3,4,5 이렇게 숫자가 빠짐없이 생기는 것을 보장하지 않는 것이 기본입니다. 예를 들어 begin tran으로 트랜젝션을 오픈하고 데이터를 넣을 경우 1,2,3,4 채번을 하고 대기 중에 있습니다. 동시에 다른 세션에서 트랜젝션을 오픈하지 않은 상태에서 데이터를 1건을 넣으면 6번으로 채번이 되며, 이전에 begin tran으로 트랜젝션 오픈하고 1~5까지 넣었던 데이터를 롤백을 하게 되면 결국 데이터는 6부터 시작하는 데이터만 등록되게 됩니다.

이런 경우에서도 확인할 수 있듯이 반드시 1,2,3,4,5,6,7 처럼 중간에 숫자를 빠짐없이 채우는 기능은 Identity에서 제공하지 않음을 알 수 있습니다.

그렇기 때문에 SQL Service가 재시작 되고 Identity 값이 1000을 jump 한다고 문제될 일은 없습니다.

 

 

그래도 Jump 1000 을 하고 싶지 않다면..

그래도 jump 1000을 하지 않는 것으로 설정하고 싶다면 아래 방법 중 하나를 적용하시면 됩니다.

 

첫번째, 데이터베이스 수준에서 IDENTITY CACHE 옵션 비활성화를 합니다.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

데이터베이스 수준에서 설정하는 것이기 때문에 고려하시고 적용하셔야 합니다.

 

IDENTITY_CACHE = { ON | OFF }

적용 대상: SQL Server(SQL Server 2017(14.x)부터), Azure SQL Database 및 Azure SQL Managed Instance

데이터베이스 수준에서 ID 캐시를 사용하거나 사용하지 않도록 설정합니다. 기본값은 ON입니다. ID 캐싱은 ID 열이 있는 테이블에서 INSERT 성능을 개선하기 위해 사용됩니다. 서버가 예기치 않게 다시 시작하거나 보조 서버로 장애 조치(failover)되는 경우에 ID 열의 값이 차이 나지 않도록 IDENTITY_CACHE 옵션을 비활성화합니다. 이 옵션은 서버 수준에서만이 아니라 데이터베이스 수준에서 설정될 수 있다는 점을 제외하고 기존 추적 플래그 272와 비슷합니다.

 

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver16 

ALTER DATABASE SCOPED CONFIGURATION - SQL Server (Transact-SQL)

 

 

두번째, 서버 수준에서 추적 플래그 272를 사용하시면 됩니다.

272	서버가 예기치 않게 다시 시작되거나 보조 서버로 장애 조치되는 경우 ID 열의 값 차이를 방지하기 위해 ID 사전 할당을 사용하지 않도록 설정합니다. ID 캐싱은 ID 열이 있는 테이블에서 INSERT 성능을 향상시키는 데 사용됩니다. 참고: SQL Server 2017(14.x)부터 데이터베이스 수준에서 이를 수행하려면, ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)의 IDENTITY_CACHE 옵션을 참조하세요. 범위: 전역만

 

세번째, ID 옵션을 열로 대체할 때 NOCACHE 옵션이 있는 시퀀스를 사용합니다.

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16 

CREATE SEQUENCE(Transact-SQL) - SQL Server

[ CACHE [<constant> ] | NO CACHE ]
시스템 번호를 생성하는 데 필요한 디스크 IO 수를 최소화하여 시퀀스 개체를 사용하는 애플리케이션의 성능을 향상시킵니다. 기본값으로 CACHE가 됩니다. 예를 들어 캐시 크기 50을 선택한 경우 SQL Server에서는 50개의 개별 값을 캐시된 상태로 유지하지 않습니다. 현재 값 및 캐시에 남아 있는 값의 개수만 캐시합니다. 따라서 캐시 저장에 필요한 메모리 양은 항상 시퀀스 개체 데이터 형식의 인스턴스 두 개입니다.

캐시 크기를 지정하지 않고 캐시 옵션을 설정하면 데이터베이스 엔진에서 크기를 선택합니다. 그러나 선택의 일관성이 보장되지 않습니다. Microsoft는 캐시 크기 계산 방법을 예고 없이 변경할 수 있습니다.

CACHE 옵션을 사용하여 만들 경우 전원 오류와 같은 예기치 않은 종료로 인해 캐시에 남아 있는 시퀀스 번호가 손실될 수 있습니다.