MsSQL에서 identity 옵션은 MySQL의 auto increment 옵션과 같습니다. (insert시 컬럼 값 자동 증가)

위 옵션들은 해당 테이블에 종속적이며, 여러 테이블에 걸친 전역적인 값 증가는 sequence라는 객체를 사용합니다.

identity

-- 테이블 생성시
CREATE TABL]E (

   [컬럼명] [숫자데이터형식] IDENTITY ([초기값], [증가값]) [제약조건]

) 

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

-- 특정 테이블의 현재 IDENTITY 값을 확인 하는 방법
SELECT IDENT_CURRENT('테이블이름');

sequence

간단 예

-- 생성
CREATE SEQUENCE idSEQ
    START WITH 1
    INCREMENT BY 1;

-- 시퀀스 사용 : NEXT VALUE FOR [시퀀스이름]을 사용
INSERT INTO testTable values(NEXT VALUE FOR idSEQ, '', '')

-- 시퀀스 재설정
ALTER SEQUENCE idSEQ
    RESTART WITH 12;
    
-- 테이블에 디폴트로 시퀀스 값을 입력하면 시퀀스 표기를 생략해도 자동으로 입력 가능 == IDENTITY 와 마찬가지 
CREATE SEQUENCE idSEQ
    START WITH 1
    INCREMENT BY 1;
GO
CREATE TABLE testTB ( 
    id int DEFAULT (NEXT VALUE FOR idSEQ),
    name varchar(3)
);
GO
INSERT INTO testTB (name) value('이름'); -- 자동으로 id 지정됨

구문

CREATE SEQUENCE [schema_name . ] sequence_name  
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
    [ START WITH <constant> ]  
    [ INCREMENT BY <constant> ]  
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant> ] } | { NO CACHE } ]  
    [ ; ]

인수

sequence_name 데이터베이스에서 시퀀스를 식별하는 고유 이름을 지정합니다. 형식은 sysname입니다.

[ built_in_integer_type | user-defined_integer_type 시퀀스는 모든 정수 유형으로 정의할 수 있습니다. 다음 형식이 허용됩니다.

tinyint - 0에서 255 사이의 범위
smallint - -32,767에서 32,768 사이의 범위
int - -2,147,483,648에서 2,147,483,647 사이의 범위
bigint - -9,223,372,036,854,775,808에서 9,223,372,036,854,775,807 사이의 범위
소수 자릿수가 0인 decimal 또는 numeric.
허용되는 형식 중 하나에 기반을 둔 사용자 정의 데이터 형식(별칭 유형)

데이터 형식을 제공하지 않은 경우 bigint 데이터 형식이 기본값으로 사용됩니다.

START WITH 시퀀스 개체가 반환하는 첫 번째 값입니다. START 값은 시퀀스 개체의 최대값보다 작거나 같고 최소값보다 크거나 같은 값이어야 합니다. 새 시퀀스 개체의 기본 시작 값은 오름차순 시퀀스 개체에 대해서는 최소값이고, 내림차순 시퀀스 개체에 대해서는 최대값입니다.

INCREMENT BY 각각의 NEXT VALUE FOR 함수 호출에 대해 시퀀스 개체의 값을 증가시키거나 감소시키는(음수인 경우) 데 사용되는 값입니다. 증가값이 음수이면 시퀀스 개체가 내림차순이고, 그렇지 않으면 오름차순입니다. 증가값은 0일 수 없습니다. 새 시퀀스 개체의 기본 증가분은 1입니다.

[ MINVALUE | NO MINVALUE ] 시퀀스 개체의 경계를 지정합니다. 새 시퀀스 개체의 기본 최소값은 해당 시퀀스 개체의 데이터 형식에 대한 최소값입니다. tinyint 형식에 대해서는 0이고 다른 모든 데이터 형식에 대해서는 음수입니다.

[ MAXVALUE | NO MAXVALUE 시퀀스 개체의 경계를 지정합니다. 새 시퀀스 개체의 기본 최대값은 해당 시퀀스 개체의 데이터 형식에 대한 최대값입니다.

[ CYCLE | NO CYCLE ] 시퀀스 개체를 최소값 또는 최대값(내림차순 시퀀스 개체의 경우)에서 다시 시작해야 하는지, 아니면 최소값 또는 최대값을 초과하는 경우 예외를 발생시켜야 하는지를 지정하는 속성입니다. 새 시퀀스 개체의 기본 순환 옵션은 NO CYCLE입니다.

참고

SEQUENCE 순환은 시작 값이 아니라 최솟값 또는 최댓값에서 다시 시작됩니다.

[ CACHE [ ] | NO CACHE ] 시스템 번호를 생성하는 데 필요한 디스크 IO 수를 최소화하여 시퀀스 개체를 사용하는 애플리케이션의 성능을 향상시킵니다. 기본값으로 CACHE가 됩니다.

예를 들어 캐시 크기 50을 선택한 경우 SQL Server에서는 50개의 개별 값을 캐시된 상태로 유지하지 않습니다. 현재 값 및 캐시에 남아 있는 값의 개수만 캐시합니다. 따라서 캐시 저장에 필요한 메모리 양은 항상 시퀀스 개체 데이터 형식의 인스턴스 두 개입니다.

참고

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

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

일반적인 주의 사항

시퀀스 번호는 현재 트랜잭션 범위 외부에서 생성되며, 시퀀스 번호를 사용하는 트랜잭션이 커밋되는지 또는 롤백되는지 여부에 관계없이 사용됩니다. 중복 유효성 검사는 레코드가 완전히 채워진 후에만 수행됩니다. 이 문제는 생성 중에 동일한 번호가 둘 이상의 레코드에 사용되지만 중복으로 식별되는 경우가 발생할 수 있습니다. 이 상황이 발생하고 다른 자동 번호 값이 후속 레코드에 적용되면 자동 번호 값 사이의 간격이 발생하여 예상되는 동작이 될 수 있습니다.

캐시 관리

성능 향상을 위해 SQL Server에서는 CACHE 인수로 지정된 수의 시퀀스 번호를 미리 할당합니다.

예를 들어 시작 값이 1이고 캐시 크기가 15인 새 시퀀스가 만들어집니다. 첫 번째 값이 필요한 경우 1에서 15 사이의 값을 메모리에서 사용할 수 있습니다. 마지막으로 캐시된 값(15)은 디스크의 시스템 테이블에 기록됩니다. 15개 번호를 모두 사용한 경우 다음 요청(16번) 시 캐시가 다시 할당됩니다. 마지막으로 캐시된 새 값(30)은 시스템 테이블에 기록됩니다.

22개 번호를 사용한 후에 데이터베이스 엔진이 중지된 경우 이전에 저장된 번호 대신 메모리에서 다음에 예정된 시퀀스 번호(23)가 시스템 테이블에 기록됩니다.

SQL Server를 다시 시작한 후 시퀀스 번호가 필요하면 시스템 테이블에서 시작 번호(23)를 읽습니다. 15개 번호(23-38)의 캐시 양이 메모리에 할당되고 캐시되지 않은 다음 번호(39)가 시스템 테이블에 기록됩니다.

전원 오류와 같은 상황에서 데이터베이스 엔진이 비정상적으로 중지된 경우에는 시스템 테이블에서 읽은 번호(39)로 시퀀스가 다시 시작됩니다. 메모리에 할당되었지만 사용자 또는 애플리케이션에서 요청한 적이 없는 시퀀스 번호는 모두 손실됩니다. 이 기능은 간격이 생길 수는 있지만 단일 시퀀스 개체가 CYCLE로 정의되거나 수동으로 다시 시작되지 않은 경우 해당 개체에 대해 같은 값이 두 번 발생하지 않도록 합니다.

캐시는 현재 값(마지막으로 발생한 값) 및 캐시에 남아 있는 값의 개수에 대한 추적을 통해 메모리에서 유지 관리됩니다. 따라서 캐시에서 사용하는 메모리 양은 항상 시퀀스 개체 데이터 형식의 인스턴스 두 개입니다.

캐시 인수를 NO CACHE로 설정하면 시퀀스가 사용될 때마다 현재 시퀀스 값이 시스템 테이블에 기록됩니다. 이로 인해 디스크 액세스가 증가하여 성능이 저하될 수는 있지만 의도하지 않은 간격이 발생할 수 있는 가능성은 줄어듭니다. NEXT VALUE FOR 또는 sp_sequence_get_range 함수를 사용하여 번호를 요청한 경우에도 간격이 발생할 수 있지만 이 경우에는 번호가 사용되지 않거나 커밋되지 않은 트랜잭션에 사용됩니다.

시퀀스 개체에서 CACHE 옵션을 사용하는 경우 해당 시퀀스 개체를 다시 시작하거나 INCREMENT, CYCLE, MINVALUE, MAXVALUE 또는 캐시 크기 속성을 변경하면 변경 내용이 적용되기 전에 캐시가 시스템 테이블에 기록됩니다. 그런 다음 건너뛰는 번호 없이 현재 값부터 캐시가 다시 로드됩니다. 캐시 크기 변경은 즉시 적용됩니다.

캐시된 값을 사용할 수 있는 경우의 CACHE 옵션

다음 프로세스는 시퀀스 개체에 대한 메모리 내 캐시에 사용하지 않은 값이 있는 경우 CACHE 옵션의 다음 값을 생성하도록 시퀀스 개체를 요청할 때마다 발생합니다.

시퀀스 개체의 다음 값이 계산됩니다.
시퀀스 개체의 새로운 현재 값이 메모리 내에서 업데이트됩니다.
문 호출 시 계산된 값이 반환됩니다.

캐시가 모두 사용된 경우의 CACHE 옵션

다음 프로세스는 캐시가 모두 사용된 경우 CACHE 옵션의 다음 값을 생성하도록 시퀀스 개체를 요청할 때마다 발생합니다.

시퀀스 개체의 다음 값이 계산됩니다.
새 캐시의 마지막 값이 계산됩니다.
시퀀스 개체에 대한 시스템 테이블 행이 잠기고, 2단계에서 계산된 값(마지막 값)이 시스템 테이블에 기록됩니다. 유지되는 새 값을 사용자에게 알리기 위해 캐시가 모두 사용된 xevent가 실행됩니다.

NO CACHE 옵션

다음 프로세스는 NO CACHE 옵션의 다음 값을 생성하도록 시퀀스 개체를 요청할 때마다 발생합니다.

시퀀스 개체의 다음 값이 계산됩니다.
시퀀스 개체의 새로운 현재 값이 시스템 테이블에 기록됩니다.
문 호출 시 계산된 값이 반환됩니다.

MsSQL 값 자동 증가 identity와 시퀀스(sequence)

identity

sequence

간단 예

구문

인수

일반적인 주의 사항

캐시 관리

Developing life