Share via

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

  • Статья

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

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

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

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

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

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

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

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

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

Важно!

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

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

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

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

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

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

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

Примечание

При ссылке на строковые литералы типа данных date в индексированных вычисляемых столбцах в 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. Это означает, что ядро СУБД использует эти сохраненные значения при создании индекса в столбце и при ссылке на индекс в запросе. Этот параметр позволяет создать индекс для вычисляемого столбца, когда платформа .NET Framework, является детерминированным и точным.

См. также

COLUMNPROPERTY (Transact-SQL)