通过

计算列上的索引

只要满足以下要求，就可以在计算列上定义索引：

  • 所有权要求

  • 确定性要求

  • 精度要求

  • 数据类型要求

  • SET 选项要求

所有权要求

计算列中的所有函数引用都必须具有与表相同的所有者。

确定性要求

重要

如果表达式始终为一组指定的输入返回相同的结果，则表达式是确定性的。 COLUMNPROPERTY 函数的 IsDeterministic 属性报告computed_column_expression是否具有确定性。

computed_column_expression必须是确定性的。 computed_column_expression 是确定性的当以下条件之一或多个为真：

  • 表达式引用的所有函数都是确定性的和精确的。 这些函数包括用户定义的函数和内置函数。 有关详细信息，请参阅 Deterministic and Nondeterministic Functions。 如果计算列是 PERSISTED，那么函数可能不够精确。 有关详细信息，请参阅本主题后面的“ 在持久化计算列上创建索引 ”。

  • 表达式中引用的所有列都来自包含计算列的表。

  • 由于没有列引用，无法从多行提取数据。 例如，SUM 或 AVG 等聚合函数依赖于来自多行的数据，因此会导致计算列表达式变得不确定。

  • 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')中，数据库引擎根据 DATEFORMAT 设置解释字符串 '2000-12-01' 。

排序规则之间的非 Unicode 字符数据的隐式转换也被视为不确定，除非兼容级别设置为 80 或更早版本。

当数据库兼容性级别设置为 90 时，无法针对包含这些表达式的计算列创建索引。 但是，升级的数据库中包含这些表达式的现有计算列是可维护的。 如果使用包含隐式字符串到日期转换的索引计算列，以避免可能的索引损坏，请确保语言和 DATEFORMAT 设置在数据库和应用程序中保持一致。

精度要求

computed_column_expression必须精确。 当以下一个或多个条件为 true 时，computed_column_expression 是精确的：

  • 它不是数据类型的floatreal表达式。

  • 它在其定义中不使用 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 表达式，则被视为不精确。 这包括逻辑上的比较。

COLUMNPROPERTY 函数的 IsPrecise 属性报告 computed_column_expression 是否精确。

数据类型要求

  • 为计算列定义的computed_column_expression不能计算为text、ntext或image数据类型。

  • 只要计算列的数据类型允许作为索引键列，就可以为派生自image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)和xml数据类型的计算列建立索引。

  • 只要计算列数据类型允许作为非键索引列，则由image、ntext和text数据类型派生的计算列都可以作为非聚集索引中的非键列。

SET 选项要求

  • 执行定义计算列的 CREATE TABLE 或 ALTER TABLE 语句时，必须将ANSI_NULLS连接级别选项设置为 ON。 OBJECTPROPERTY 函数报告该选项是否通过 IsAnsiNullsOn 属性打开。

  • 创建索引的连接以及尝试在索引中更改值的 INSERT、UPDATE 或 DELETE 语句的所有连接都必须将六个 SET 选项设置为 ON，并将一个选项设置为 OFF。 优化器忽略由没有这些相同选项设置的连接执行的任何 SELECT 语句的计算列上的索引。

    • NUMERIC_ROUNDABORT选项必须设置为 OFF，并且以下选项必须设置为 ON：

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • ARITHABORT

    • CONCAT_NULL_YIELDS_NULL（空值连接结果为空值）

    • 引用标识符

    当数据库兼容级别设置为 90 或更高时，如果将 ANSI_WARNINGS 设置为 ON，则将使 ARITHABORT 隐式设置为 ON。

在持久化计算列上创建索引

如果在 CREATE TABLE 或 ALTER TABLE 语句中将列标记为 PERSISTED，那么可以在一个确定性但不精确的表达式所定义的计算列上创建索引。 这意味着，数据库引擎在对列创建索引时以及查询中引用索引时使用这些持久化值。 使用此选项，可以在 .NET Framework 具有确定性和精确性时在计算列上创建索引。

相关内容

COLUMNPROPERTY （Transact-SQL）