MS SQL Server 计算列用到自定义函数创建索引

MS SQL Server2005

有一计算列用到了自定义函数

函数代码如下：

View Code

CREATE FUNCTION [dbo].[f_RecommendCondition]
    (
      @Views INT ,
      @Replies INT ,
      @Digest TINYINT
    )
RETURNS INT
AS
    BEGIN
        DECLARE @returnValue INT
        SELECT @returnValue=@Digest+@Replies+@Views;
        IF ( @Digest IN ( 1, 2, 3, 10 )
             AND @Views > 99
             AND @Replies > 9
           )
            SET @returnValue = 1
        ELSE
            SET @returnValue = 0

        RETURN @returnValue
    END

然后在此列上创建索引提示错误：

不能在此列上创建索引，因为此列不具有确定性。

于是在MSDN上查了一下，计算列加索引的要求如下：

1、所有权要求
计算列中的所有函数引用必须与表具有相同的所有者。

2、确定性要求
       如果对于一组指定的输入表达式始终返回相同的结果，则说明表达式具有确定性。
      computed_column_expression 必须具有确定性。如果下列一项或多项为真，则 computed_column_expression 具有确定性：
   A、表达式引用的所有函数都具有确定性，并且是精确的。这些函数包括用户定义函数和内置函数。

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

C、没有列引用从多行中请求数据。例如，聚合函数（如 SUM 或 AVG）依靠来自多行的数据，这使计算列表达式具有不确定性。

D、没有系统数据访问或用户数据访问。

3、精度要求

A、表达式的数据类型不是 float 或 real。

B、表达式定义中没有使用 float 或 real 数据类型。

4、数据类型要求

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

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

5、SET 选项要求

NUMERIC_ROUNDABORT 选项必须设置为 OFF，

且下列选项必须设置为 ON：

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIER

我仔细看了看这几项要求。都没有违背这些要求，特别是2的确定性要求。

为了证明我的函数没有违反确定性要求，我把函数改为：

View Code

CREATE FUNCTION [dbo].[f_return1]
    ()
RETURNS INT
AS
    BEGIN
        RETURN 1
    END

仍然错误。

后来想到Leader曾经讲索引视图时好像要加一个什么关键字。

查了下关于索引视图的sql 里面有个

WITH SCHEMABINDING

把之前的函数改成：

View Code

CREATE FUNCTION [dbo].[f_RecommendConditionWith]
    (
      @Views INT ,
      @Replies INT ,
      @Digest TINYINT
    )
RETURNS INT
WITH SCHEMABINDING
AS
    BEGIN
        DECLARE @returnValue INT
        SELECT @returnValue=@Digest+@Replies+@Views;
        IF ( @Digest IN ( 1, 2, 3, 10 )
             AND @Views > 99
             AND @Replies > 9
           )
            SET @returnValue = 1
        ELSE
            SET @returnValue = 0

        RETURN @returnValue
    END

之后创建索引成功

MS SQL Server 计算列用到自定义函数 创建索引

MS SQL Server 计算列用到自定义函数创建索引