[MS-SQL]Identity 속성

SQL Server는 자동으로 값을 생성하는 방법으로 두 가지 방법을 제공하고 있다. 하나는 identity 컬럼 속성을 이용하는 것이며, 다른 하나는 시쿼스 개체를 이용하는 것이다. 

오늘은 identity에 대해서 알아보는 시간을 갖도록 하자 

identity 속성은 아주 오래 전의 SQL Server 버전부터 지원이 되었다. 이 기능은 일부 시나리오서는 잘 동작하지만, 많은 결점을 가지고 있다는 것을 알아둘 필요가 있다. 글을 쓰면서 하나하나 설명하도록 하겠다. 

identity는 SQL Server에서는 숫자 형식의 컬럼(단, 소수점이 아닌)에 대해서 identity라는 속성을 정의할 수 있다. 이 속성은 커럼의 정의 부분에서 지정된 초기 값과 증가치(단계 값)를 이용해서 INSERT가 실행될 때마다 자동으로 값을 발생시킨다. 일반적으로, 이 특성은 대리키를 생성할 때 많이 사용된다. 

대리키란, 애플리케이션의 데이터를 이용해서 만드는 키가 아닌 시스템에서 만드는 값을 이용하는 키를 말한다.

다음 코드를 통해 identity 컬럼이 포함되어 있는 Table을 만들어 보도록 하겠다. 

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
 
Create Table dbo.T1
(
    keycol    Int         Not Null Identity(1,1) Constraint PK_T1 Primary Key,
    datacol Varchar(10) Not Null Constraint CHK_T1_datacol CHECK(datacol Like '[A-Za-z]%') 
);

위의 Table에 대해 간략하게 설명을 하자면 

- keycol : 초기 값이 1이고 증가치가 1인 identity 속성

- datacol : 저장되는 데이터의 첫 글자가 알파벳인 경우만 허용하도록 하는 체크 제약 조건이 설정되어 있는 문자형 컬럼 

데이터를 넣어 보도록 하겠다. 넣기 전에 INSERT 구문에서는 identity 컬럼이 데이블에 존재하지 않는 것처럼, 완전히 무시하고 작업하면 된다. 

Insert Into dbo.T1(datacol) 
values     ('AAAAA')
    ,     ('BBBBB')
    ,     ('CCCCC')
-- 확인 쿼리 
 
Select * From dbo.T1

결과창 모습니다. 

keycol      datacol

----------- ----------

1           AAAAA

2           BBBBB

3           CCCCC

(3개 행이 영향을 받음)

Insert 구문에서는 존재하지 않는 것처럼 완전히 무시하고 작업했지만, 당연히 커럶 이름 (keycol)으로 identity 컬럼을 참조할 수 있다. SQL Server는 더욱 일반적인 형태인 $identity을 사용해서 identity 컬럼을 참조하는 방법도 제공하고 있다. 

Select $identity, datacol From dbo.T1

결과 값을 실행해보면 알겠지만 위와 동일하게 출력 된다. 

새로은 행을 테이블에 입력하게 되면, SQL Server는 테이블의 현재 identity 값과 증가치를 기반으로 새로운 identity 값을 만들게 된다. 만약, 새롭게 만들어진 identity 값이 필요하다면(예를 들어, 참조하는 테이블에 자식 행을 입력하기 위해) 쿼리에서 @@identity나 SCOPE_IDENTITY 함수 중 하나를 사용하면 된다. @@identity 함수는 오래도니 방식으로, 범위에 상관없이(예를 들어, 현재 프로시저와 INSERT를 통해 호출된 크리거는 다른 범위다) 세션에 의해 생성된 마지막 identity 값을 반환한다. SCOPE_IDENTITY는 현재 범위(예를 들어, 동일한 프로시저) 내에서 세션에 의해 생성된 최종 identity 값을 반환한다. 범위를 고려하지 않는 아주 특별한 경우가 아니라면, SCOPE_IDENTITY 함수를 사용해야 한다. 

Declare @new_key As Int; 
 
Insert Into dbo.T1(datacol) Values('AAAAA');
 
Set @new_key = Scope_Identity()
 
Select @new_key As new_key
 

결과 값은 

SCOPE_IDENTITY                          @@identity                              IDENT_CURRENT

--------------------------------------- --------------------------------------- -------------------------------

NULL                                    NULL                                    4

(1개 행이 영향을 받음)

새 창을 열어 위 쿼리를 실행하게 되면 위의 결과 값을 얻을 수 있을 것이다. 

새 창을 열지 않으면 모두 '4'라는 값을 얻게 될 것이다. 위의 설명을 잘 읽었다면 왜 이런 결과가 나오는 지는 알것이다. 

이제 마지막으로 Identity 속성을 사용하는데 있어서 알아두어야 할 것들에 대해서 집고 넘어가도록 하자. 

만약 identity 값을 발생시킨 INSERT 문이 실패하거나 구문이 속한 트랜잭션이 롤백을 하더라도, 테이블에서 변경된 identity 값은 다시 돌릴 수 없다. 예를 들어 아래의 쿼리를 실행해보자. 

Insert Into dbo.T1(datacol) values ('12345')

결과 값은 

메시지 547, 수준 16, 상태 0, 줄 1

INSERT 문이 CHECK 제약 조건 "CHK_T1_datacol"과(와) 충돌했습니다. 데이터베이스 "master", 테이블 "dbo.T1", column 'datacol'에서 충돌이 발생했습니다.

문이 종료되었습니다.

그리고 다시 아래 쿼리를 실행해보자. 

Insert Into dbo.T1(datacol) values ('EEEEE')
 

정상적으로 명령을 실행한뒤 T1 테이블을 조회하면 아래와 같은 결과가 나온다. 

keycol      datacol

----------- ----------

1           AAAAA

2           BBBBB

3           CCCCC

4           AAAAA

6           EEEEE

(5개 행이 영향을 받음)

keycol 이 4 다음 6인 것을 확인 할 수 있다. 위에서 보는 바와 같이 keycol이 5일때 Insert문을 실패 하면 identity 5는 사라지고 다음 INSERT 문이 실행될 때 6으로 keycol이 만들어 진다. 

따라서 중간에 누락된 값이 있더라도 상관이 없는 경우에만, 자동으로 값을 생성하는 방법으로 identity 속성을 사용해야 한다는 것을 의미한다. 만약, 누락된 값이 없도록 해야 한다면 별동의 다른 메커니즘의 사용을 고려해야 한다. 

identity 속성의 또 다른 중요한 점은 이미 존재하는 컬럼에 identity 속성을 추가할 수 없다는 것과 기존에 지정된 커럼에서 이 속을 제거할 수 없다는 것이다. 이 속성은 CREATE TABLE 구문 또는 ALTER TABLE 구문으로 새로운 컬럼을 만들 때에만 지정할 수 있다. 그러나 SQL Server에서는 INSERT 구문에서 identity 컬럼에 대한 값을 명시적으로 지정할 수 있는 옵션을 제공하는데, 테이블 이름과 같이 사용되는 IDENTITY_INSERT라는 세션 옵션이 바로 그것이다. 하지만 identity 컬럼에 대한 업데이트를 허용하는 옵션은 따로 존재하지는 않는다. 

Set identity_insert dbo.T1 On;
Insert Into dbo.T1(keycol, datacol) values(5, 'FFFFF')
Set identity_Insert dbo.T1 Off;

위의 쿼리를 실행한뒤 dbo.T1을 조회하면 아래와 같다. 

keycol      datacol

----------- ----------

1           AAAAA

2           BBBBB

3           CCCCC

4           AAAAA

5           FFFFF

6           EEEEE

(6개 행이 영향을 받음)

keycol에 '5' 값이 들어가 있는 것을 확인 할 수 있다. 

흥미롭게도, SQL Server는 테이블의 현재 identity 값보다 명시작으로 지정해서 입력한 identity 값이 더 클 땡만 현재 테이블의 identity 값을 변경한다.

위와 같이 identity 속성 자체만으로는 컬럼의 고유성을 강화시킬 수 없다는 점을 반드시 기억하도록 하자. IDENTITY_INSERT 옵션을 ON으로 설정하게 되면 명시적으로 값을 지정할 수 있다. 이미 테이블에 저장되어 잇는 값을 다시 입력할 수도 있다. 또한, DBCC CHECKIDENT 명령을 이용하면 테이블의 현재 identity 값을 초기화시킬 수도 있다. 

정리하면, identity 속성은 고유성을 강화하기 위한 것은 아니다. identity 컬럼에 고유성을 보장하려면, 컬럼에 기본기 또는 고유 제약 조건을 정의해야 한다.