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

 之后创建索引成功

 

  

 

转载于:https://www.cnblogs.com/zhaoguo435/archive/2012/07/11/2586749.html