계산 열

다른 열에서 값을 이끌어 냅니다

글이 몇 월에 올라왔는지, 첨부가 몇 KB 인지는 이미 표 안에 있는 값에서 나옵니다. reg_date 와 size_bytes 가 있으면 그만입니다. 그런데도 MONTH(reg_date) 를 문장마다 적는 것은 번거롭고, 적는 사람마다 다르게 적으면 결과가 어긋납니다.

계산 열은 그 식을 표에 적어 두는 것입니다. 열처럼 보이지만 값을 담고 있지는 않습니다.

ALTER TABLE Board.POSTS ADD reg_month AS MONTH(reg_date);

SELECT cc.name AS 열, cc.definition AS 정의, t.name AS 형식,
       cc.is_persisted AS 저장됨, cc.is_nullable AS NULL허용
FROM sys.computed_columns cc
    JOIN sys.types t ON t.user_type_id = cc.user_type_id
WHERE cc.object_id = OBJECT_ID('Board.POSTS');

MONTH 라고 적었는데 정의에는 datepart(month, …) 로 들어갔습니다. 같은 뜻이고, SQL Server 가 안쪽 표현으로 바꿔 담은 것입니다.

이 열이 표를 얼마나 무겁게 했는지 봅니다.

SELECT ps.page_count AS 페이지, ps.page_count * 8 AS KB, ps.record_count AS 행,
       CONVERT(decimal(5,1), ps.avg_record_size_in_bytes) AS 행바이트
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.POSTS'), 1, NULL, 'DETAILED') ps
WHERE ps.index_level = 0;

읽을 때 계산합니다. 그래서 공간을 쓰지 않고, reg_date 를 고치면 다음에 읽을 때 자동으로 새 값이 나옵니다. 어긋날 여지가 없습니다.

대신 값을 직접 넣을 수는 없습니다.

INSERT INTO Board.POSTS (board_num, user_num, group_num, depth, sort_no,
                         title, hit_count, reg_date, reg_month)
VALUES (1, 1, 999999, 0, 1, N'임시 글', 0, '2026-01-01', 1);

SELECT * 에는 계산 열도 함께 나옵니다. 표에 계산 열을 더하면 SELECT * 로 받던 응용 프로그램의 결과 모양이 바뀝니다. 열을 적어서 가져오는 편이 안전한 까닭이 하나 더 있는 셈입니다.

같은 행 안의 값만 봅니다

3.4 의 CHECK 와 같은 제약이 있습니다. 계산 열의 식은 그 행 안에서 끝나야 합니다. 다른 행이나 다른 표를 보려 하면 만들어지지 않습니다.

-- 다른 행의 평균을 보려 합니다.
ALTER TABLE Board.POSTS ADD is_hot AS
    (CASE WHEN hit_count > (SELECT AVG(hit_count) FROM Board.POSTS) THEN 1 ELSE 0 END);

-- 다른 표를 보려 합니다.
ALTER TABLE Board.POSTS ADD board_code AS
    (SELECT code FROM Board.BOARDS WHERE num = board_num);

같은 행 안이면 됩니다. 게시판에서 자주 필요한 원글과 답글 구분이 그렇습니다.

ALTER TABLE Board.POSTS ADD is_reply AS
    (CASE WHEN parent_num IS NULL THEN 0 ELSE 1 END);

SELECT is_reply, COUNT(*) AS 글수 FROM Board.POSTS GROUP BY is_reply;

parent_num IS NULL 을 문장마다 적는 대신 이름을 하나 두었습니다. 규칙을 표에 적어 두면 읽는 사람이 뜻을 다시 해석하지 않아도 됩니다.

3.5 에서 미뤄 둔 것

3.5 끝에서 열을 함수로 감싸면 인덱스를 사용하지 못한다고 했습니다. reg_date 로 정렬된 인덱스는 MONTH(reg_date) 값으로 정렬되어 있지 않기 때문입니다. 실습 데이터에서 2월 글을 세어 봅니다.

SET STATISTICS IO ON;
SELECT COUNT(*) FROM Board.POSTS WHERE MONTH(reg_date) = 2;
SET STATISTICS IO OFF;

계산 열에는 인덱스를 걸 수 있습니다. 식의 결과로 정렬된 구조를 만드는 것이므로, 함수를 씌운 조건도 탐색이 됩니다.

CREATE NONCLUSTERED INDEX IX_POSTS_month ON Board.POSTS (reg_month);

SET STATISTICS IO ON;
-- 계산 열 이름으로 적습니다.
SELECT COUNT(*) FROM Board.POSTS WHERE reg_month = 2;

-- 계산 열이 있는 줄 모르고 원래 식 그대로 적습니다.
SELECT COUNT(*) FROM Board.POSTS WHERE MONTH(reg_date) = 2;
SET STATISTICS IO OFF;

식을 그대로 적어도 엔진이 계산 열을 알아봅니다. 조건에 적힌 식과 계산 열의 정의가 같으면 인덱스를 사용합니다. 계획으로 확인합니다.

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) FROM Board.POSTS WHERE MONTH(reg_date) = 2;
GO
SET SHOWPLAN_TEXT OFF;

이미 돌고 있는 응용 프로그램을 고치지 않고 조회를 빠르게 만드는 방법입니다. 코드를 배포하지 않고 데이터베이스에서만 처리할 수 있어, 느린 문장을 당장 손볼 수 없을 때 사용할 수 있는 수단이 됩니다.

다만 식이 정확히 같아야 합니다. DATEPART(month, reg_date) 는 같은 것으로 보지만, DATENAME(month, reg_date) 나 CONVERT(char(2), reg_date, 110) 처럼 결과가 다른 식은 물론 알아보지 못합니다. 근본은 문장을 고치는 것이고, 이것은 그때까지의 방법입니다. 조건을 어떻게 고쳐 적는지는 5.4 에서 다룹니다.

값을 실제로 저장합니다

인덱스를 걸려면 조건이 둘 있습니다. 같은 입력에 늘 같은 값을 내야 하고(결정적), 소수점 오차가 없어야 합니다(정확). 첨부 크기의 제곱근처럼 부동 소수점을 내는 식은 뒤쪽에 걸립니다.

ALTER TABLE Board.FILES ADD size_root AS SQRT(size_bytes);
CREATE NONCLUSTERED INDEX IX_FILES_root ON Board.FILES (size_root);

PERSISTED 를 붙이면 계산해서 나온 값을 실제로 저장합니다. 저장된 값이므로 읽을 때마다 다시 계산하지 않고, 그래서 인덱스를 걸 수 있습니다.

ALTER TABLE Board.FILES DROP COLUMN size_root;
ALTER TABLE Board.FILES ADD size_root AS SQRT(size_bytes) PERSISTED;
CREATE NONCLUSTERED INDEX IX_FILES_root ON Board.FILES (size_root);   -- 만들어집니다

결정적이지 않은 식은 PERSISTED 로도 구제되지 않습니다.

-- 며칠 지났는지. 오늘이 언제냐에 따라 값이 달라집니다.
ALTER TABLE Board.FILES ADD age_days AS
    DATEDIFF(day, reg_date, GETDATE()) PERSISTED;

저장하면 값을 저장하는 만큼 표가 무거워집니다. 제목에서 공백을 없앤 열을 붙여 재 봅니다.

ALTER TABLE Board.POSTS ADD title_norm AS
    UPPER(REPLACE(title, N' ', N'')) PERSISTED;

SELECT ps.page_count AS 페이지,
       CONVERT(decimal(5,1), ps.avg_record_size_in_bytes) AS 행바이트,
       CONVERT(decimal(5,1), ps.avg_fragmentation_in_percent) AS 단편화,
       CONVERT(decimal(5,1), ps.avg_page_space_used_in_percent) AS 사용률
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.POSTS'), 1, NULL, 'DETAILED') ps
WHERE ps.index_level = 0;

-- 붙인 뒤 다시 만듭니다.
ALTER INDEX PK_POSTS ON Board.POSTS REBUILD;

행이 27바이트 늘었으니 페이지는 14장이 되는 것이 맞습니다. 23장은 그 과정에서 생긴 것입니다. 이미 차 있는 페이지마다 값을 밀어 넣어야 하니 3.2 에서 본 페이지 분할이 일어나고, 단편화가 87.0% 까지 올라갑니다. 사용률 56.5% 는 페이지 절반이 비었다는 뜻입니다.

운영 중인 표에 PERSISTED 계산 열을 더했다면 인덱스를 다시 만드십시오. 크기가 작을 때는 이 정도로 끝나지만, 큰 표에서는 붙이는 동안 표가 잠기고 로그가 크게 늡니다. 3.10 과 6.3 에서 다시 다룹니다.

SET 옵션이 어긋나면 수정도 막힙니다

계산 열에 인덱스를 걸었다면 그 표를 다루는 연결의 SET 옵션이 정해진 값이어야 합니다. 식의 결과가 옵션에 따라 달라질 수 있어, 저장된 인덱스와 어긋나는 것을 막기 위함입니다.

SET QUOTED_IDENTIFIER OFF;
CREATE NONCLUSTERED INDEX IX_POSTS_reply ON Board.POSTS (is_reply);

-- 인덱스를 만드는 것만 막히는 것이 아닙니다.
UPDATE Board.POSTS SET hit_count = hit_count WHERE num = 1;

도구가 무엇으로 붙느냐에 따라 갈립니다. SSMS 와 최신 드라이버는 알맞은 값으로 붙지만, sqlcmd 는 -I 옵션을 주지 않으면 QUOTED_IDENTIFIER 가 꺼진 채 연결합니다. 배포 스크립트를 그렇게 돌리면 실행은 성공하는데 나중에 수정이 막히는 상태가 됩니다. 필요한 옵션은 여섯이고 (ANSI_NULLS · ANSI_PADDING · ANSI_WARNINGS · ARITHABORT · CONCAT_NULL_YIELDS_NULL · QUOTED_IDENTIFIER) 모두 켜져 있어야 하며, NUMERIC_ROUNDABORT 는 꺼져 있어야 합니다.

직접 해보기

이 단원에서 더한 열과 인덱스를 지웁니다.
DROP INDEX IX_POSTS_month ON Board.POSTS;
ALTER TABLE Board.POSTS DROP COLUMN reg_month, is_reply, title_norm;
DROP INDEX IX_FILES_root ON Board.FILES;
ALTER TABLE Board.FILES DROP COLUMN size_root;
ALTER INDEX PK_POSTS ON Board.POSTS REBUILD;