Индекс SQL Server по вычисляемому столбцу: моделирование индекса на основе функций

В этом руководстве вы узнаете, как моделировать индексы на основе функций в SQL Server, используя индексы по вычисляемым столбцам.

Введение в индексы вычисляемых столбцов

См. следующую таблицу sales.customers из примера базы данных.

Этот запрос находит клиента, локальная часть адреса электронной почты которого — «garry.espinoza»;

SELECT    
    first_name,
    last_name,
    email
FROM    
    sales.customers
WHERE 
    SUBSTRING(email, 0, 
        CHARINDEX('@', email, 0)
    ) = 'garry.espinoza';

Вот предполагаемый план выполнения запроса:

Как ясно видно из выходных данных, оптимизатору запросов необходимо сканировать весь кластеризованный индекс для поиска клиента, что неэффективно.

Если вы работали с Oracle или PostgreSQL, вы, возможно, знаете, что Oracle поддерживает индексы на основе функций, а PostgreSQL имеет индексы на основе выражений. Эти типы индексов позволяют индексировать результат функции или выражения, что улучшит производительность запросов, предложение WHERE которых содержит функцию и выражение.

В SQL Server можно использовать индекс по вычисляемому столбцу для достижения аналогичного эффекта индекса на основе функции:

Сначала создайте вычисляемый столбец на основе выражения в предложении WHERE.
Во-вторых, создайте некластеризованный индекс для вычисляемого столбца.

Например, для поиска клиентов по локальным частям их адресов электронной почты выполните следующие действия:

Сначала добавьте новый вычисляемый столбец в таблицу sales.customers:

ALTER TABLE sales.customers
ADD 
    email_local_part AS 
        SUBSTRING(email, 
            0, 
            CHARINDEX('@', email, 0)
        );

Затем создайте индекс для столбца email_local_part:

CREATE INDEX ix_cust_email_local_part
ON sales.customers(email_local_part);

Теперь вы можете использовать столбец email_local_part вместо выражения в предложении WHERE для поиска клиентов по локальной части адреса электронной почты:

SELECT    
    first_name,
    last_name,
    email
FROM    
    sales.customers
WHERE 
    email_local_part = 'garry.espinoza';

Оптимизатор запросов использует операцию поиска по индексу ix_cust_email_local_part, как показано на следующем рисунке:

Требования к индексам по вычисляемым столбцам

Для создания индекса по вычисляемому столбцу необходимо выполнить следующие требования:

Функции, участвующие в вычисляемом выражении столбца, должны иметь того же владельца, что и таблица.
Вычисленное выражение столбца должно быть детерминированным. Это означает, что выражение всегда возвращает один и тот же результат для заданного набора входных данных.
Вычисляемый столбец должен быть точным, то есть его выражение не должно содержать никаких типов данных FLOAT или REAL.
Результат вычисляемого выражения столбца не может быть оценен как тип данных TEXT, NTEXT или IMAGE.
Параметр ANSI_NULLS должен быть установлен в положение ON, когда вычисляемый столбец определяется с помощью оператора CREATE TABLE или ALTER TABLE. Кроме того, параметры ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER и CONCAT_NULL_YIELDS_NULL также должны быть установлены в положение ON, а NUMERIC_ROUNDABORT должен быть установлен в положение OFF.

В этом руководстве вы узнали, как использовать индексы SQL Server для вычисляемых столбцов для повышения скорости запросов, включающих выражения.