MSSQL Identity, Sequence

SQL Server는 자동으로 값을 생성하는 방법으로 두 가지 방법을 제공하고 있다.
1. Identity 컬럼 속성
2. Sequence 객체 사용

1. Identity 속성

컬럼의 정의 부분에서 지정된 초기값과 증가치를 이용해서 INSERT가 실행될 때마다 자동으로 값을 발생시킨다. Identity는 Table에 종속적이다.

사용법 : Identity가 설정된 컬럼은 따로 지정하지 않아도 새로운 컬럼이 추가되면 자동으로 값이 증가된다. Identity값을 임의로 수정하고 싶다면

SET IDENTITY_INSERT [테이블명] ON 으로 하면 된다.

특징

테이블에 종속적이기 때문에 INSERT가 실패하게 되면 Identity는 따로 증가하지 않는다. 예를들어 1자리숫자까지만 입력받을 수 있는 조건이 있을 때 10번째 행을 입력하려고 하면 Insert Error가 발생한다. 그리고 나서 2자리까지 입력받을 수 있도록 조건을 변경해주고 새로 Insert를 해주면 Identity는 10에서부터 시작한다.(SQQUENCE와 차이점이다)

다만 Identity 값을 발생시킨 INSERT문이 실패하거나 구문이 속한 트랜잭션이 롤백을 하더라도 Identity값은 증가한다.
예를들어 이미 4개가 INSERT되어 identity값이 4이고, 새로운 insert가 일어났지만 실패했다면 새로운 row가 삽입되지는 않았지만 내부적으로 identity값은 하나 증가된 상태를 가지게 된다. 따라서 다음번 INSERT가 실행되게 되면 새로 삽입된 row의 identity값은 5가 아닌 6을 가지게 된다.

2. SEQUENCE 속성

• 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
• 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
• 메모리에 Cache되었을 때 시퀀스값의 액세스 효율이 증가 한다.
• 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

생성

--시퀀스 생성
CREATE SEQUENCE [dbo].[SEQ이름]
AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
CYCLE
CACHE 50 -- 1이면 NOCACHE

사용법

NEXT VALUE FOR [시퀀스 이름]

특징

따라서 SEQUENCE를 사용하는 테이블에서 insert 오류가 발생하면 insert가 되지 않을 뿐이지 SEQUENCE객체는 그 값을 증가시킨다 ( 'next value for sequence' 구문을 사용하는 순가 SEQUENCE값은 증가한다)
예를들어 1자리숫자까지만 입력받을 수 있는 조건이 있을 때 10번째 행을 입력하려고 하면 Insert Error가 발생한다. 그리고 나서 2자리까지 입력받을 수 있도록 조건을 변경해주고 새로 Insert를 해주면 SEQUENCE는 11에서부터 시작한다.

아래 소스코드를 실행하면 보다 쉽게 이해할 수 있다

-------------- IDENTITY --------------

CREATE TABLE test1
(
    seq int identity(1,1),
    val varchar(50) NULL
)

insert into test1 (val) values ('first');

insert into test1 (val) values ('second');

insert into test1 (val) values ();

insert into test1 (val) values ('third');


select * from test1;


drop table dbo.test2;


create table dbo.test2
(
	col varchar(10) not null constraint chk check(col like '[A-Za-z]%'),
	ident numeric(1,0) not null identity(1,1)
)
;


alter table test2 alter column ident numeric(2,0);

insert into dbo.test2(col) values ('BB');

insert into dbo.test2(col) values ('1234');

insert into dbo.test2(col) values ('DD');

select * from dbo.test2;


------------- 시퀀스 ------------------

create sequence id start with 1 increment by 1;

drop table testSEQ;

create table testSEQ
(
	id numeric(1,0) default (next value for id),
	name varchar(10)
);


alter table testSEQ alter column id numeric(2,0);

insert into testSEQ (name) values('A');

insert into testSEQ (name) values('A');

select * from testSEQ;

Difference between Identity & Sequence in SQL Server

Difference 1:

The IDENTITY property is tied to a particular table and cannot be shared among multiple tables since it is a table column property.

On the flip side the SEQUENCE object is defined by the user and can be shared by multiple tables since is it is not tied to any table.

Difference 2:

To generate the next IDENTITY value, a new row has to be inserted into the table. On the other hand, the next VALUE for a SEQUENCE object can simply be generated using the NEXT VALUE FOR clause with the sequence object.

Difference 3

The value for the IDENTITY property cannot be reset to its initial value. In contrast, the value for the SEQUENCE object can be reset.

Difference 4

A maximum value cannot be set for the IDENTITY property. On the other hand, the maximum value for a SEQUENCE object can be defined.