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

Применимо к:SQL ServerAzure SQL База данныхAzure SQL Управляемый экземплярSQL База данных в Microsoft Fabric

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

• требования к владению;
• требования к детерминированности;
• требования к точности;
• требования к типам данных;
• 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.

CAST и CONVERT

Если вы ссылаетесь на строковые литералы типа данных даты в индексированных вычисляемых столбцах в SQL Server, рекомендуется явно преобразовать литерал в тип даты, который требуется с помощью детерминированного стиля формата даты. Список детерминированных стилей форматирования даты см. в разделе CAST и CONVERT.

Дополнительные сведения см. в разделе Недетерминированное преобразование строковых литералов даты в значения DATE.

Уровень совместимости

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

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

Уровень 90 совместимости соответствует SQL Server 2005 (9.x).

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

Выражение 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);
  

Свойство 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

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

Иногда можно создать вычисляемый столбец, определенный с помощью выражения, которое является детерминированным, но неточным. Это можно сделать, если столбец помечен как PERSISTED в операторе CREATE TABLE или ALTER TABLE.

Это означает, что ядро СУБД сохраняет вычисляемые значения в таблице и обновляет их при обновлении любых других столбцов, от которых зависит вычисляемый столбец. Ядро СУБД использует эти сохраненные значения при создании индекса в столбце и при ссылке на индекс в запросе.

Этот параметр позволяет создать индекс для вычисляемого столбца, если компонент ядро СУБД не может точно определить, является ли функция, возвращающая выражения вычисляемых столбцов, особенно функция CLR, созданная в .NET Framework, одновременно детерминированной и точной.

Следующие шаги

• COLUMNPROPERTY (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)