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

  • Статья

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

  • требования к владению;

  • требования к детерминированности;

  • требования к точности;

  • требования к типам данных;

  • требования к параметру SET.

Требования к владению

Все ссылки на функции в вычисляемом столбце должны иметь того же владельца, что и таблица.

Требования к детерминированности

Важное примечаниеВажно!

Выражения являются детерминированными, если они всегда возвращают один и тот же результат для определенного набора входных данных. Свойство IsDeterministic функции COLUMNPROPERTY определяет, детерминировано ли выражение computed_column_expression.

Выражение computed_column_expression должно быть детерминированным. Выражение computed_column_expression является детерминированным, если соблюдается одно или несколько следующих условий.

  • Все функции, на которые ссылается выражение, являются детерминированными и точными. Это относится как к пользовательским, так и к встроенным функциям. Дополнительные сведения см. в разделе Детерминированные и недетерминированные функции. Функции могут быть неточными, если вычисляемый столбец помечен как PERSISTED. Дополнительные сведения см. ниже в разделе Создание индексов материализованных вычисляемых столбцов.

  • Все столбцы, на которые ссылается выражение, берутся из таблицы, содержащей вычисляемый столбец.

  • Ни одна ссылка на столбец не запрашивает данные из нескольких строк. Например, агрегатные функции, такие как SUM или AVG, используют данные из нескольких строк и делают выражение computed_column_expression недетерминированным.

  • У выражения computed_column_expression нет доступа к системным данным или данным пользователя.

Для индексирования требуется, чтобы любой вычисляемый столбец, содержащий выражение CLR, перед индексированием был детерминированным и помеченным как PERSISTED. Выражения определяемого пользователем типа данных CLR допускаются в определениях вычисляемых столбцов. Вычисляемые столбцы, которые имеют определяемый пользователем тип данных CLR, могут быть индексированы, если этот тип сопоставим. Дополнительные сведения см. в разделе Определяемые пользователем типы данных CLR.

ПримечаниеПримечание

При указании в индексируемых вычисляемых столбцах SQL Server строковых литералов типа данных «дата», рекомендуется явно преобразовывать их в тип данных «дата» при помощи детерминированного стиля форматирования даты. Список детерминированных стилей форматирования даты см. в разделе CAST и CONVERT. Выражения, включающие неявные преобразования символьных строк в типы данных, считаются недетерминированными, если только не установлен уровень совместимости базы данных, равный 80 или менее. Это связано с тем, что результаты зависят от значений параметров LANGUAGE и DATEFORMAT, определенных для сеанса сервера. Например, результат выражения CONVERT (datetime, '30 listopad 1996', 113) зависит от значения параметра LANGUAGE, поскольку строка 30 listopad 1996 в различных языках обозначает разные месяцы. Аналогично, в выражении DATEADD(mm,3,'2000-12-01') компонент Компонент Database Engine интерпретирует строку '2000-12-01' в соответствии со значением параметра DATEFORMAT.

Неявное преобразование символьных данных не в Юникоде между различными параметрами сортировки также считается недетерминированным, если уровень совместимости не установлен равным или меньшим 80.

Если параметр уровня совместимости равен 90, создавать индексы на вычисляемых столбцах, содержащих эти выражения, нельзя. Это, однако, не относится к существующим вычисляемым столбцам, содержащим такие выражения из обновленной базы данных. Если используются индексированные вычисляемые столбцы, которые содержат неявные преобразования из строк в даты, то для предотвращения возможного повреждения индекса убедитесь, что параметры LANGUAGE и DATEFORMAT согласованы с базами данных и приложениями.

Требования к точности

Выражение computed_column_expression должно быть точным. Выражение computed_column_expression считается точным, если соблюдается одно или несколько следующих условий.

  • Выражение не относится к типу данных float или real.

  • В его определении не используется тип данных float или real. Например, в следующей инструкции столбец y имеет тип int и является детерминированным, но неточным.

    CREATE TABLE t2 (a int, b int, c int, x float, 
   y AS CASE x 
         WHEN 0 THEN a 
         WHEN 1 THEN b 
         ELSE c 
      END);
ПримечаниеПримечание

Любое выражение, которое имеет тип данных float или real, считается неточным и не может быть ключом индекса; выражения, которые имеют тип float или real, могут использоваться в индексированном представлении, но не в качестве ключа. Это верно и для вычисляемых столбцов. Любая функция, выражение или определяемая пользователем функция считается неточной, если содержит выражение типа float или real, включая логические выражения (сравнения).

Свойство IsPrecise функции COLUMNPROPERTY сообщает, точно ли выражение computed_column_expression.

Требования к типам данных

  • Выражение computed_column_expression, заданное для вычисляемого столбца, не может определять значения типов данных text, ntext или image.

  • Вычисляемые столбцы, производные от типов данных image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml, могут индексироваться, если тип данных вычисляемого столбца допускается в качестве ключевого столбца индекса.

  • Вычисляемые столбцы, производные от типов данных image, ntext и text, могут быть неключевыми (включенными) столбцами некластеризованного индекса, если тип данных вычисляемого столбца является допустимым в качестве неключевого индексного столбца.

Требования к параметру SET

  • Параметру уровня соединения ANSI_NULLS необходимо присвоить значение ON, если выполняется инструкция CREATE TABLE или ALTER TABLE, определяющая вычисляемый столбец. Функция OBJECTPROPERTY через свойство IsAnsiNullsOn сообщает, включен ли этот параметр.

  • Соединение, для которого создается индекс, и все соединения, выполняющие инструкции INSERT, UPDATE или DELETE, которые изменяют значения в индексе, должны иметь шесть параметров инструкции SET в значении ON и один параметр в значении OFF. Оптимизатор пропускает индекс вычисляемого столбца для любой инструкции SELECT, выполняемой соединением, в котором эти параметры не имеют таких же значений.

    • Параметр NUMERIC_ROUNDABORT должен быть установлен в OFF, а следующие параметры должны быть установлены в ON:

    • ANSI_NULLS;

    • ANSI_PADDING;

    • ANSI_WARNINGS;

    • ARITHABORT;

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER.

    Если уровень совместимости базы данных равен 90 или более, при установке параметра ANSI_WARNINGS в состояние ON параметр ARITHABORT также устанавливается в состояние ON.

Создание индексов материализованных вычисляемых столбцов

Индекс вычисляемого столбца, который определен с помощью детерминированного, но неточного выражения, можно создать, если в инструкции CREATE TABLE или ALTER TABLE этот столбец помечен как PERSISTED. Это значит, что компонент Компонент Database Engine хранит вычисляемые значения в таблице и обновляет их при изменении любого столбца, от которого зависит вычисляемый столбец. Компонент Компонент Database Engine использует эти сохраненные значения при создании индекса столбца и при появлении ссылки на этот столбец в запросе. Этот параметр позволяет создавать индекс для вычисляемого столбца, если компонент Компонент Database Engine не может точно определить, является ли функция, которая возвращает выражения вычисляемого столбца (в особенности функция CLR, созданная в .NET Framework) и детерминированной, и точной.

См. также

COLUMNPROPERTY (Transact-SQL)