기본 키와 식별자 전략

대리 키와 자연 키

실습 데이터베이스의 표 여섯 개는 모두 num 이라는 열 하나를 기본 키로 가지고 있습니다. 업무상 아무 뜻이 없는 번호이고, 오로지 행을 가리키려고 둔 것입니다. 이런 키를 대리 키라고 합니다.

그런데 Member.USERS 에는 이미 겹치지 않는 열이 있습니다. user_id 입니다. 게시판에도 code 가 있습니다. 자료 자체가 가진 이런 열을 자연 키라고 하고, 그것을 기본 키로 삼을 수도 있습니다.

먼저 흔히 드는 이유부터 확인합니다. "자연 키는 크니까 무겁다" 는 말입니다. 글과 댓글 1,520건이 작성자를 가리키는 데 드는 바이트를 재 봅니다.

SELECT SUM(DATALENGTH(U.user_id)) AS 자연키바이트
FROM (SELECT user_num FROM Board.POSTS
      UNION ALL
      SELECT user_num FROM Board.COMMENTS) X
    JOIN Member.USERS U ON U.num = X.user_num;

SELECT COUNT(*) * 4 AS 대리키바이트
FROM (SELECT user_num FROM Board.POSTS
      UNION ALL
      SELECT user_num FROM Board.COMMENTS) X;

크기는 이유가 되지 못했습니다. 진짜 이유는 다른 데 있습니다. 아이디는 바뀔 수 있습니다. 회원이 아이디 변경을 요청하면 어떻게 되는지 세어 봅니다.

-- 1번 회원(홍길동)이 아이디를 바꾼다면
SELECT (SELECT COUNT(*) FROM Board.POSTS    WHERE user_num = 1) AS 글,
       (SELECT COUNT(*) FROM Board.COMMENTS WHERE user_num = 1) AS 댓글;

대리 키를 두는 까닭은 크기가 아니라 바뀌지 않는다는 것입니다. 자연 키는 언젠가 바뀝니다. 아이디 변경, 사업자번호 개편, 상품 코드 체계 변경은 모두 실제로 일어나는 일이고, 그때마다 그것을 가리키던 모든 행이 따라 움직입니다. num 은 업무와 무관하므로 바뀔 까닭이 없습니다.

자연 키를 버리는 것은 아닙니다. 실습 데이터베이스는 UX_USERS_user_id 라는 유일 인덱스로 아이디가 겹치지 않도록 지키고 있습니다. 기본 키는 대리 키로, 유일성은 유일 제약으로 나누는 것이 일반적인 모양입니다. 제약 조건은 3.4 에서 다룹니다.

가장 흔한 선택

실습 데이터베이스의 여섯 표는 모두 IDENTITY(1,1) 입니다. 1부터 시작해 1씩 올라갑니다. 값을 적지 않으면 데이터베이스가 채웁니다.

1.8 에서 이미 본 성질이 하나 있습니다. 번호를 되돌려 주지 않는다는 것입니다. 트랜잭션을 되돌려도 마찬가지입니다.

-- 연습용 표를 하나 만듭니다. 세 행을 넣어 두었습니다.
CREATE TABLE Board.KEY_TEST (
    num  int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_KEY_TEST PRIMARY KEY,
    memo nvarchar(50)       NOT NULL
);
INSERT INTO Board.KEY_TEST (memo) VALUES (N'첫 행'), (N'둘째 행'), (N'셋째 행');
GO

BEGIN TRAN;
INSERT INTO Board.KEY_TEST (memo) VALUES (N'되돌릴 행');
SELECT SCOPE_IDENTITY() AS 방금받은번호;
ROLLBACK;

SELECT COUNT(*) AS 되돌린뒤행수 FROM Board.KEY_TEST;

INSERT INTO Board.KEY_TEST (memo) VALUES (N'다음 행');
SELECT SCOPE_IDENTITY() AS 다음번호;

번호에 구멍이 나는 것은 고장이 아닙니다. 번호를 미리 나눠 주어야 여러 접속이 동시에 넣을 수 있기 때문입니다. 되돌린 번호를 다시 쓰려면 그때마다 모두를 기다리게 해야 합니다. 그래서 IDENTITY 값을 "몇 번째 글인지" 로 읽으면 안 됩니다. 순서를 보장할 뿐 개수를 보장하지 않습니다.

방금 넣은 번호를 받아 오는 법

셋이 있고 뜻이 다릅니다. 거의 항상 SCOPE_IDENTITY() 입니다.

@@IDENTITY 는 조용히 틀리기 때문에 위험합니다. 글을 넣을 때 이력 표에 자동으로 기록을 남기는 트리거가 걸려 있으면, 받아 온 번호가 글 번호가 아니라 이력 번호가 됩니다. 코드는 그대로인데 트리거를 추가한 날부터 틀리기 시작합니다. 트리거는 4.7 에서 다룹니다.

표에 매이지 않은 번호

IDENTITY 는 열에 붙어 있습니다. 표를 넘어 번호를 나눠 쓸 수 없고, INSERT 하기 전에는 번호를 알 수 없습니다. SEQUENCE 는 표와 떨어져 있는 번호 발급기입니다.

CREATE SEQUENCE Board.SEQ_DOC AS int
    START WITH 1000 INCREMENT BY 1;

SELECT NEXT VALUE FOR Board.SEQ_DOC AS 첫번째;
SELECT NEXT VALUE FOR Board.SEQ_DOC AS 두번째;
SELECT NEXT VALUE FOR Board.SEQ_DOC AS 세번째;

SELECT current_value, start_value, increment
FROM sys.sequences WHERE name = 'SEQ_DOC';

IDENTITY 와 견주어 달라지는 것은 셋입니다.

• 여러 표가 한 번호 체계를 나눌 수 있습니다. 주문과 반품에 같은 문서 번호를 매기는 경우입니다.
• 넣기 전에 번호를 받아 둘 수 있습니다. 부모와 자식을 한 번에 만들 때 부모 번호를 먼저 알 수 있습니다.
• 범위를 미리 받아 올 수 있습니다(sp_sequence_get_range). 대량 적재에서 한 번에 1,000개를 받아 쓰는 식입니다.

되돌려도 번호가 소비되는 것은 IDENTITY 와 같습니다. 구멍이 없는 번호가 필요하면 어느 쪽도 답이 아닙니다. 세금계산서처럼 빠짐없이 이어져야 하는 번호는 별도 표에 두고 트랜잭션 안에서 잠그며 올려야 합니다. 그 대가로 동시에 넣는 것이 줄어듭니다.

값을 미리 만들 수 있는 대신

uniqueidentifier 는 128비트 값입니다. NEWID() 로 어디서든 겹치지 않는 값을 만들 수 있습니다. 쓰는 까닭이 분명합니다.

• 여러 곳에서 만든 자료를 합칠 때 번호가 부딪히지 않습니다.
• 데이터베이스에 넣기 전에 값을 정할 수 있습니다. 응용 프로그램이 먼저 만들어 두고 나중에 저장합니다.
• 순번이 드러나지 않습니다. 주소창의 번호로 전체 회원 수를 짐작할 수 없습니다.

대가는 두 가지입니다. 하나는 크기입니다.

char(36) 으로 담는 것은 흔한 실수입니다. 같은 값을 담는 데 자리를 2.25배 사용하고, 비교도 글자 단위로 하게 됩니다. GUID 를 담을 열은 uniqueidentifier 입니다.

두 번째 대가 — 클러스터형 키로 삼았을 때

이쪽이 훨씬 큽니다. 실습 데이터베이스의 기본 키는 모두 클러스터형입니다. 클러스터형 키는 행이 디스크에 놓이는 차례를 정합니다. 그래서 새 값이 항상 끝에 붙으면 뒤에만 쌓이지만, 값이 무작위면 가운데를 비집고 들어가면서 페이지를 쪼갭니다.

표 셋을 만들어 한 행씩 5,000번 넣고 재 보겠습니다.

CREATE TABLE Board.KEY_INT (
    num int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    title nvarchar(100) NOT NULL, reg_date datetime2 NOT NULL);

CREATE TABLE Board.KEY_GUID (
    id uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY CLUSTERED,
    title nvarchar(100) NOT NULL, reg_date datetime2 NOT NULL);

CREATE TABLE Board.KEY_SEQGUID (
    id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED,
    title nvarchar(100) NOT NULL, reg_date datetime2 NOT NULL);

-- 한 행씩 5,000번 넣습니다(응용 프로그램이 하는 방식).
DECLARE @i int = 1;
WHILE @i <= 5000
BEGIN
    INSERT INTO Board.KEY_INT     (title, reg_date) VALUES (N'글', '2026-01-01');
    INSERT INTO Board.KEY_GUID    (title, reg_date) VALUES (N'글', '2026-01-01');
    INSERT INTO Board.KEY_SEQGUID (title, reg_date) VALUES (N'글', '2026-01-01');
    SET @i = @i + 1;
END

SELECT OBJECT_NAME(s.object_id) AS 표,
       CONVERT(decimal(5,1), s.avg_fragmentation_in_percent) AS 단편화,
       s.page_count AS 페이지,
       CONVERT(decimal(5,1), s.avg_page_space_used_in_percent) AS 페이지사용률
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
WHERE OBJECT_NAME(s.object_id) IN ('KEY_INT', 'KEY_GUID', 'KEY_SEQGUID')
  AND s.index_level = 0
ORDER BY 1;

NEWID() 쪽은 단편화 97.6% 이고 페이지가 23장에서 41장으로 늘었습니다. 페이지 사용률이 73.1% 라는 것은 페이지마다 4분의 1 이 비어 있다는 뜻입니다. 쪼개진 자리가 그대로 남기 때문입니다. 같은 자료를 읽는 데 디스크와 메모리를 그만큼 더 사용합니다.

NEWSEQUENTIALID() 는 이전 값보다 큰 값을 만듭니다. 그래서 끝에만 붙고 단편화가 3.2% 로 내려갑니다. 페이지가 31장인 것은 값 자체가 16바이트라 어쩔 수 없는 몫입니다. 다만 다음 값을 짐작할 수 있으므로 주소창에 드러나는 자리에는 맞지 않습니다. GUID 를 사용하는 세 가지 까닭 가운데 "순번이 드러나지 않는다" 를 잃습니다.

이 측정은 한 행씩 넣었기 때문에 나온 것입니다. INSERT … SELECT 로 5,000행을 한 번에 넣으면 엔진이 정렬해서 넣으므로 세 표 모두 1% 아래로 나옵니다. 응용 프로그램은 한 행씩 넣으니 위쪽이 실제에 가깝습니다. 측정할 때는 실제와 같은 방식으로 넣어야 합니다.

무엇을 선택할지

GUID 가 필요하지만 단편화도 피하고 싶다면 둘을 나누는 방법이 있습니다. 클러스터형 기본 키는 int IDENTITY 로 두고, 바깥에 보일 값만 uniqueidentifier 열로 따로 두어 유일 인덱스를 겁니다. 안쪽은 촘촘하게 쌓이고 바깥으로는 순번이 드러나지 않습니다.

직접 해보기

이 단원에서 만든 KEY_TEST · KEY_INT · KEY_GUID · KEY_SEQGUID 와 SEQ_DOC 는 여기서만 사용합니다. 다음 단원으로 넘어가기 전에 지우십시오.
DROP TABLE Board.KEY_TEST, Board.KEY_INT, Board.KEY_GUID, Board.KEY_SEQGUID;
DROP SEQUENCE Board.SEQ_DOC;