SQL Server 에서 함수 기반 인덱스 만들기 — 계산 열 + 인덱스

Oracle 에서는 CREATE INDEX ... ON EMP (UPPER(NAME)) 처럼 식에 바로 인덱스를 건다. SQL Server 에는 이런 함수 기반 인덱스 문법이 없다. 대신 계산 열(Computed Column)을 만들고 그 열에 인덱스를 건다. Oracle 의 가상 열(virtual column)에 인덱스를 거는 것과 같은 방식이다 (Oracle 의 함수 기반 인덱스도 안에서는 숨은 가상 열을 만든다).

SQL Server 2022 기준으로 정리했다.

기본 방법

-- 1. 테이블
CREATE TABLE CUSTOMER (
    ID         INT         NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR(20) NULL,
    LAST_NAME  VARCHAR(20) NULL
);

-- 2. 계산 열 추가
ALTER TABLE CUSTOMER
  ADD FULL_NAME AS CAST(CONCAT_WS(' ', FIRST_NAME, LAST_NAME) AS VARCHAR(41));

-- 3. 계산 열에 인덱스
CREATE INDEX IX_CUSTOMER_FULL_NAME ON CUSTOMER (FULL_NAME);

조회할 때는 계산 열 이름으로 조건을 건다.

SELECT ID, FULL_NAME
  FROM CUSTOMER
 WHERE FULL_NAME = 'Gildong Hong';

쿼리에 계산 열과 똑같은 식을 그대로 써도 옵티마이저가 계산 열 인덱스를 써 주는 경우가 있지만, 식이 조금만 달라도 안 맞는다. 계산 열 이름을 직접 쓰는 쪽이 확실하다.

몇 가지 짚어 둔다.

원래 글에서는 TRIM(FIRST_NAME + ' ' + LAST_NAME) 을 썼다. + 는 한쪽이 NULL 이면 결과 전체가 NULL 이 된다. CONCAT_WS 는 NULL 을 건너뛰므로 성이나 이름 한쪽이 비어 있어도 값이 나온다. (CONCAT_WS·TRIM 은 SQL Server 2017 부터 있다)
CAST(... AS VARCHAR(41)) 로 열의 자료형과 길이를 못 박았다. 인덱스 키 길이 제한 때문인데, 아래에서 다시 설명한다.

인덱스를 걸 수 있는 계산 열의 조건

Microsoft 문서(Indexes on computed columns) 의 조건을 요약하면 이렇다.

조건	내용
결정적(deterministic)	같은 입력에 항상 같은 결과. `GETDATE()`, `NEWID()` 같은 것은 안 된다. 문자열 내장 함수 중에는 `FORMAT` 이 비결정적이다
정밀(precise)	`float`·`real` 이 식에 들어가면 정밀하지 않다고 본다. 이때는 `PERSISTED` 로 만들어야 인덱스를 걸 수 있다
같은 테이블·같은 행	다른 테이블의 열이나 여러 행(집계)을 읽으면 안 된다. 함수 안에서 테이블을 읽어도(데이터 접근) 안 된다
소유자	계산 열이 부르는 함수는 테이블과 소유자가 같아야 한다
SET 옵션	인덱스를 만들 때와 그 열을 바꾸는 DML 을 실행할 때 `ANSI_NULLS`, `ANSI_PADDING`, `ANSI_WARNINGS`, `ARITHABORT`, `CONCAT_NULL_YIELDS_NULL`, `QUOTED_IDENTIFIER` 가 `ON`, `NUMERIC_ROUNDABORT` 가 `OFF`

결정적·정밀한지는 직접 확인할 수 있다.

SELECT COLUMNPROPERTY(OBJECT_ID('CUSTOMER'), 'FULL_NAME', 'IsDeterministic') AS IS_DETERMINISTIC
     , COLUMNPROPERTY(OBJECT_ID('CUSTOMER'), 'FULL_NAME', 'IsPrecise')       AS IS_PRECISE
     , COLUMNPROPERTY(OBJECT_ID('CUSTOMER'), 'FULL_NAME', 'IsIndexable')     AS IS_INDEXABLE;

SET 옵션은 요즘 드라이버(JDBC, ADO.NET)와 SSMS 기본값이 모두 맞춰져 있어서 보통 문제가 안 된다. 오래된 프로그램이나 sqlcmd 스크립트에서 QUOTED_IDENTIFIER OFF 로 붙으면 그 연결의 INSERT·UPDATE 가 실패하니 그때 의심하면 된다.

사용자 함수를 계산 열에 쓸 때

계산 열 식이 길어지면 스칼라 함수로 빼고 싶어진다. 이때 실제로 두 번 막혔다.

1. 다른 소유자의 함수는 못 쓴다. 처음에는 다른 곳(Oracle 식으로 말하면 다른 유저)에 만들어 둔 공용 함수를 불렀는데 인덱스를 만들 수 없었다. 위 표의 “소유자” 조건 때문이다. 테이블과 같은 소유자(보통 같은 스키마)에 같은 기능의 함수를 만들어서 해결했다.

2. “결정적이지 않다” 는 오류. 함수 본문이 결정적이어도 SQL Server 는 WITH SCHEMABINDING 이 없는 사용자 함수를 결정적이라고 판단하지 않는다. 함수에 WITH SCHEMABINDING 을 붙이면 해결된다.

CREATE FUNCTION dbo.FN_ONLY_DIGITS (@VALUE VARCHAR(20))
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
    RETURN REPLACE(REPLACE(@VALUE, '-', ''), ' ', '');
END;
GO

ALTER TABLE CUSTOMER ADD PHONE VARCHAR(20) NULL;
ALTER TABLE CUSTOMER ADD PHONE_DIGITS AS dbo.FN_ONLY_DIGITS(PHONE);
CREATE INDEX IX_CUSTOMER_PHONE_DIGITS ON CUSTOMER (PHONE_DIGITS);

SCHEMABINDING 함수는 참조하는 객체를 dbo.TABLE 처럼 두 부분 이름으로 써야 하고, 다른 데이터베이스의 객체는 참조할 수 없다
반대로 함수가 묶여 있는 동안에는 그 함수를 ALTER/DROP 하려면 계산 열(과 인덱스)을 먼저 지워야 한다
내장 함수만으로 쓸 수 있는 식이면 굳이 사용자 함수로 빼지 않는 편이 관리가 쉽다

인덱스 키 길이

원래 글에서 “계산 열이 너무 크면 인덱스를 걸 수 없었다” 고 적었는데, 정확히는 인덱스 키 길이 제한이다.

인덱스 키 최대 길이: 클러스터형 900바이트, 비클러스터형 1,700바이트 (SQL Server 2016 부터)
VARCHAR(MAX)·NVARCHAR(MAX) 같은 큰 자료형은 키 열이 될 수 없다
가변 길이 열의 선언 길이가 제한을 넘으면 경고와 함께 인덱스는 만들어지지만, 실제 값이 제한을 넘는 행은 INSERT·UPDATE 가 실패한다

CONCAT, REPLACE 같은 함수 결과는 생각보다 긴 자료형이 될 수 있으니, 계산 열 식을 CAST(... AS VARCHAR(n)) 으로 감싸 필요한 만큼으로 잘라 두는 것이 안전하다. 이렇게 하면 열 자료형이 VARCHAR(n) 으로 정해진다.

PERSISTED 를 붙여야 할까

계산 열은 기본적으로 저장되지 않고 읽을 때 계산한다. PERSISTED 를 붙이면 테이블에 값을 저장하고, 원본 열이 바뀔 때 함께 갱신한다.

ALTER TABLE CUSTOMER
  ADD FULL_NAME_P AS CAST(CONCAT_WS(' ', FIRST_NAME, LAST_NAME) AS VARCHAR(41)) PERSISTED;

	비저장(기본)	PERSISTED
인덱스	결정적·정밀하면 걸 수 있다 (인덱스에는 값이 저장된다)	결정적이면 걸 수 있다 (정밀하지 않은 `float` 식도 가능)
테이블 공간	안 쓴다	쓴다
쓰기 비용	인덱스를 걸면 인덱스 갱신 비용만	테이블·인덱스 모두 갱신
그 밖에		`NOT NULL`·`CHECK`·외래 키 같은 제약, 파티션 열로 쓰려면 필요

검색용 인덱스만 필요하면 비저장 계산 열 + 인덱스로 충분하다. 식이 무거워서 인덱스를 못 타는 조회(전체 읽기)에서도 계산을 줄이고 싶을 때 PERSISTED 를 고려한다.

정리

SQL Server 의 함수 기반 인덱스 = 계산 열 + 그 열의 인덱스. 조회 조건에는 계산 열 이름을 쓴다
식은 결정적·정밀해야 하고, 사용자 함수는 테이블과 같은 소유자 + WITH SCHEMABINDING
키 길이 제한(1,700/900바이트)에 걸리지 않게 CAST 로 길이를 정해 둔다

다시 쓰며 바로잡은 점

“다른 유저의 함수를 쓸 수 없었다” 의 원인을 공식 문서의 소유자 조건(함수와 테이블의 소유자가 같아야 함)으로 밝혔다
“컬럼 크기가 크면 인덱스를 못 건다” 를 인덱스 키 길이 제한(900/1,700바이트, MAX 형 불가)으로 구체화했다
예제의 FIRST_NAME + ' ' + LAST_NAME 은 한쪽이 NULL 이면 전체가 NULL 이 되므로 CONCAT_WS 로 바꿨다
PERSISTED 와 비저장 계산 열의 차이를 덧붙였다