如何使计算列（标量函数）保持不变？ [英] How to make computed column (scalar function) persisted ?

问题描述

大家好，

我有一个InvoiceHeader表，其定义如下：

InvoiceId int NOT NULL（Key）



和一个InvoiceDetail表，其定义如下：

DetailId int NOT NULL（Key）

InvoiceId int NOT NULL（FKey）

包smallint NULL

PackagePrice money NULL

Units smallint NULL

UnitPrice money NULL

Discount1 money NULL < br $>
Discount2 money NULL



我在InvoiceDetail表中添加了一个计算列：

Value

公式：

（（（ CONVERT （[十进制]（ 18 ， 6 ），[Packages]）* CONVERT（[< span class =code-keyword> decimal ]（ 18 ， 6 ），[ PackagePrice]）+ CONVERT（[ decimal ]（ 18 ， 6 ），[Units]）* CONVERT（[ decimal ]（ 18 ， 6 ），[UnitPrice]））*（（（ 100 ） -  CONVERT（[ decimal ]（ 18  ， 6 ），[Discount1]））/（ 100 ）））*（（< 100 ） -  CONVERT（[ decimal ]（ 18 ，  6 ），[Discount2]））/（ 100 ）））

这个看起来很可怕的公式简单得多了，但是我必须转换每一列才能让它坚持下去。



我知道原因就是设计不好，但现在不能让我改变:(

所有这些转换终于让我能够做到这一点专栏Pers是的。



然后我添加了一个函数来计算InvoiceTotal：

 ALTER函数[dbo]。[GetInvoiceTotal]（@ InvoiceID as int）
返回十进制（18,2）
 as 
 begin 
声明@total为十进制（18,2） 
 select @total = CONVERT（decimal（18,2），sum（convert（decimal（18,2），invd.Value）））
来自InvoiceDetails invd 
其中invd.InvoiceID = @InvoiceID 
 return convert（decimal（18,2），Isnull（Convert（decimal（18,2），@ total），0.00））
 
 end; 

然后在InvoiceHeader表中添加一个计算列：

InvoiceTotal

as dbo.GetInvoiceTotal（InvoiceId ）



但是当我试图让它持续存在时我收到一个错误：

因为列而无法保留是不确定的。

我的尝试：



我在以下网址阅读文档：

http://msdn.microsoft.com/en-us/library/ms178091.aspx



ISNULL始终是确定性的，

除非使用OVER和ORDER BY子句指定，否则所有聚合函数都是确定性的。

问题出现在：datetime，smalldatetime或sql_variant ......这里没有使用过这个问题。

我隐藏了代码的h * ll以保持持久性。



我缺少什么？

解决方案

您的UDF是非确定性的 - 给定相同的输入（发票ID），它可以并且将返回不同的值。



确定性和非确定性函数Microsoft Docs [ ^ ]



您不能拥有引用其他表的持久计算列。可能因为它几乎不可能更改数据 - 每次在任何表中插入，更新或删除行时，SQL Server都必须检查数据库中的每个其他表以查看是否持久化值需要更新。这意味着在每个表的每个行中为每个持久计算列运行UDF，因为它无法知道更改是否会改变该值。 />


想象一下，在一个有2000万张发票的系统上这样做 - 当你试图改变数据时，整个数据库都会停止运行。

< blockquote>

  ALTER   FUNCTION  [dbo]。[GetInvoiceTotal]（ @ InvoiceID   AS   INT ）
 < span class =code-keyword> RETURNS   DECIMAL （ 18 ， 2 ）
  AS  
  BEGIN  
  DECLARE   @total   AS  < span class =code-keyword> DECIMAL （ 18 ， 2 ）
  SELECT   @ total  = ISNULL（SUM（CAST（invd.Value  AS   DECIMAL （ 18 ， 2 ）））， 0 。 00 ）
  FROM  InvoiceDetails invd 
  WHERE  invd.InvoiceID =  @ InvoiceID  
  RETURN   @ total  
  END ; 

Hello everyone,
I have an InvoiceHeader table with the following definition:
InvoiceId int NOT NULL (Key)

and an InvoiceDetail table with the following definition:
DetailId int NOT NULL (Key)
InvoiceId int NOT NULL (FKey)
Packages smallint NULL
PackagePrice money NULL
Units smallint NULL
UnitPrice money NULL
Discount1 money NULL
Discount2 money NULL

I added a computed column to the InvoiceDetail table:
Value
formula:

(((CONVERT([decimal](18,6),[Packages])*CONVERT([decimal](18,6),[PackagePrice])+CONVERT([decimal](18,6),[Units])*CONVERT([decimal](18,6),[UnitPrice]))*(((100)-CONVERT([decimal](18,6),[Discount1]))/(100)))*(((100)-CONVERT([decimal](18,6),[Discount2]))/(100)))

This horrible looking formula has been much simpler, but I had to convert each and every column to allow it to be persisted.

I know the cause of that is poor design in the first place, but that is not for me to change now :(
and all these conversions finally made me able to make this column Persisted.

Then I added a function to calculate the InvoiceTotal :

ALTER function [dbo].[GetInvoiceTotal](@InvoiceID as int)
returns decimal(18,2)
as
begin
declare @total as decimal(18,2)
select @total =CONVERT(decimal(18,2) , sum(convert(decimal(18,2) ,invd.Value)))
from InvoiceDetails invd  
where invd.InvoiceID = @InvoiceID
return convert(decimal(18,2) , Isnull(Convert(decimal(18,2) , @total ) , 0.00) )

end;

Then added a computed column to the InvoiceHeader table :
InvoiceTotal
as dbo.GetInvoiceTotal(InvoiceId)

but when I try to make it persisted I get an error:

cannot be persisted because the column is non-deterministic.

What I have tried:

I read the documentation at :
http://msdn.microsoft.com/en-us/library/ms178091.aspx

ISNULL is always deterministic,
All aggregate functions are deterministic unless they are specified with the OVER and ORDER BY clauses.
Issues occur with : datetime, smalldatetime, or sql_variant... none of which is used here.
I coverted the h*ll of the code to make persisted.

What am I missing ?

解决方案

Your UDF is non-deterministic - given the same input (invoice ID), it can and will return different values.

Deterministic and Nondeterministic Functions | Microsoft Docs[^]

You cannot have a persisted computed column which references other tables. Probably because it would make it almost impossible to change the data - every time you inserted, updated, or deleted a row in any table, SQL Server would have to check every other table in the database to see if the persisted value needed to be updated. Which would mean running the UDF for every persisted computed column in every row in each table, since it would have no way to know whether the change would alter the value.

Imagine doing that on a system with 20 million invoices - the whole database would grind to a halt when you tried to change the data.

ALTER FUNCTION [dbo].[GetInvoiceTotal](@InvoiceID AS INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @total AS DECIMAL(18,2)
SELECT @total =ISNULL(SUM(CAST(invd.Value AS DECIMAL(18,2))),0.00)
                FROM InvoiceDetails invd  
                          WHERE invd.InvoiceID = @InvoiceID
RETURN @total
END;

这篇关于如何使计算列（标量函数）保持不变？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！