Sqlserver：计算列

CREATE TABLE CREATE TABLE [ database_name . [ owner ] . | owner . ] table_name ( { column_definition | column_name AS computed_column_expression | table_constraint } [ , ... n ] ) 计算 列是物理上并不存储在表中的虚拟列。 计算 列由同一表中的

CREATE TABLE

CREATE TABLE [ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > } [ ,...n ]
)

计算列是物理上并不存储在表中的虚拟列。计算列由同一表中的其它列通过表达式计算得到。例如，计算列可以这样定义：cost AS price * qty。表达式可以是非计算列的列名、常量、函数、变量，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。

计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何其它可使用常规表达式的位置，但下列情况除外：

• 计算列不能用作 DEFAULT 或 FOREIGN KEY 约束定义，也不能与 NOT NULL 约束定义一起使用。但是，如果计算列由具有确定性的表达式定义，并且索引列中允许计算结果的数据类型，则可将该列用作索引中的键列，或用作 PRIMARY KEY 或 UNIQUE 约束的一部分。

  例如，如果表中含有整型列 a 和 b，则可以在计算列 a+b 上创建索引。但不能在计算列 a+DATEPART(dd, GETDATE()) 上创建索引，因为在以后的调用中，其值可能发生改变。

• 计算列不能作为 INSERT 或 UPDATE 语句的目标。

  说明 表中计算列所使用的列值因行而异，因此每行的计算列值可能不同。

  计算列的为空性是由 SQL Server 根据使用的表达式自动确定的。即使只有不可为空的列，大多数表达式的结果也认为是可为空的，因为可能的下溢或溢出也将生成 NULL 结果。使用 COLUMNPROPERTY 函数（AllowsNull 属性）查看表中任何计算列的为空性。通过指定 ISNULL(check_expression, constant)，其中常量为替代任何 NULL 结果的非 NULL 值，可为空的表达式 expr 可以转换为不可为空的表达式。

在计算列上创建索引

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

• computed_column_expression 必须是确定性的。如果表达式对一组给定的输入总是返回同样的结果，则该表达式是确定性的。在下列情况中，computed_column_expression 是确定性的：
  • 表达式引用的所有函数都是确定和精确的。这包括用户定义的函数和内置函数。有关更多信息，请参见确定性函数和非确定性函数。
    
    
  • 表达式引用的所有列都来自包含计算列的表。
    
    
  • 没有列引用从多行拉数据。例如，聚合函数（如 SUM 或 AVG）依靠来自多行的数据，这使得 computed_column_expression 不确定。

  COLUMNPROPERTY 函数的 IsDeterministic 属性报告 computed_column_expression 是否确定。

  计算列表达式在下列情况中精确：

  • 非 float 数据类型表达式。
    
    
  • 其定义中不使用 float 数据类型。例如，在下列语句中，列 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)

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

  

  说明 任何 float 型表达式都被认为是不精确的，不能作为索引键；float 型表达式可以用在索引视图中，但不能作为键。对于计算列同样如此。如果函数、表达式、用户定义的函数或视图定义中包含任何 float 表达式（包括逻辑表达式（比较）），则被认为是不确定的。

• 执行 CREATE TABLE 语句时，必须将 ANSI_NULL 连接级选项设置为 ON。OBJECTPROPERTY 函数通过 IsAnsiNullsOn 属性报告此选项是否已打开。
  
  
• 为计算列定义的 computed_column_expression 不能取值为 text、ntext 或 image 数据类型。
  
  
• 对于创建索引的连接和所有尝试使用 INSERT、UPDATE 或 DELETE 语句更改索引值的连接，必须使六个 SET 选项设置为 ON，一个选项设置为 OFF。如果不具有上述选项设置的连接执行了任何 SELECT 语句，优化器将忽略计算列上的索引。

  下列选项必须设置为 ON：

  • ANSI_NULLS
    
    
  • ANSI_PADDING
    
    
  • ANSI_WARNINGS
    
    
  • ARITHABORT
    
    
  • CONCAT_NULL_YIELDS_NULL
    
    
  • QUOTED_IDENTIFIER

  除上述 ON 设置外，NUMERIC_ROUNDABORT 选项必须设置为 OFF。有关更多信息，请参见影响结果的 SET 选项。