How to make computed column (scalar function) persisted ?

Question

0.00/5 (No votes)

See more:

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:

SQL

(((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 ?

Posted 5-Jun-18 9:27am

Ahmad_kelany

Updated 6-Jun-18 4:16am

Add a Solution

Comments

Santosh kumar Pithani 6-Jun-18 1:32am

Hello, first you have to check column(invd.Value) datatype and then procedure what you want but no need to do convertion every time at each step.

Ahmad_kelany 6-Jun-18 6:34am

Hello,
Thanks for replying,

Its type is decimal.

At first I wasn't doing all these conversions, but that doesn't work.. so I am just trying blindly here because I don't know precisely what causes the issue.

2 solutions

Solution 1

SQL

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;

Posted 5-Jun-18 19:45pm

Santosh kumar Pithani

Comments

Ahmad_kelany 6-Jun-18 6:37am

That was what I did in the very beginning, but unfortunately that doesn't work either.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-06-06T04:17:00

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.