Using multiple rows from multiple tables for Persisted Computed Column with a Scalar UDF

Question

I'm trying to create a new field in the Order Transactions Table as a Persisted computed column using a Scalar UDF value as the value for the field.

I understand that a requirement for a Persisted column is that the value is deterministic, meaning that the multiple table UDF that I have is non-deterministic as it is not using fields from the source table.

Function:

USE [MyDatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[fnCalcOutstandingBalance]    
Script Date: 08/10/2018 14:01:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnCalcOutstandingBalance](@ItemReferance int)

RETURNS INT
WITH SCHEMABINDING 
AS
Begin
DECLARE @AcceptedQty INT
DECLARE @SumOfQty INT
DECLARE @Result INT

SELECT @AcceptedQty = 

    ISNULL([Accepted Quantity],0)
    FROM 
    dbo.[Order Transactions Table]
    WHERE @ItemReferance = [Item Referance] 

SELECT @SumOfQty =
    ISNULL(sum(Quantity),0)
    FROM dbo.[Delivery Table]
    GROUP BY [Item Referance]
    HAVING @ItemReferance = [Item Referance]

    SET @Result = ISNULL(@AcceptedQty,0) - ISNULL(@SumOfQty,0)

return @Result

END

I am looking for a workaround to be able to use the value that is generated from the above function within the Order Transactions Table.

Adding the column:

ALTER TABLE [Order Transactions Table]
ADD CalcOB AS [dbo].[fnCalcOutstandingBalance]([Item Referance]) PERSISTED

I have tested this function and it works as a standalone function call in a select as it should. The problem is that I need this to be used in a computed column without being a virtual column.

Answer 1

You can try WITH SCHEMABINDING in the UDF.
This means the underlying tables can't change without dropping the UDF (and computed column etc)

Without this, it will definitely prevent PERSISTED.

You do realise the massive performace and concurrency implications of using a UDF like this?

• It's a cursor (for each row, do an aggregate one by one)
• You have odd concurrent behaviours

After comments

CREATE VIEW dbo.SomeView
AS
SELECT
   ott.Col1, ott.Col2, ...,
   OutstandingBalance = ISNULL(ott.[Accepted Quantity],0) - ISNULL(SUM(dt.Quantity),0)
FROM
   dbo.[Order Transactions Table] ott
   LEFT JOIN
   dbo.[Delivery Table] dt ON ott.[Item Referance] = dt.[Item Referance]
GROUP BY
   ott.Col1, ott.Col2, ott.[Accepted Quantity], ...

You can schemabind the view but you can't index it with the LEFT JOIN

Answer 2

@gbn hit it out of the park with his answer but allow me to add my $0.02. Because your scalar UDF accesses tables I am confident that you won't be able to persist this column. That said, let's be 100% clear:

There is absolutely nothing to gain by adding a computed column in the manner you're describing and plenty to lose.

First even if you could persist this column, any queries that access this table will become slower, waaaay slower in some cases. T-SQL Scalar UDFs for computed columns, as constraints or for default values make queries that reference that table un-parallelizable; serial execution only! Furthermore, the available optimizations become dramatically limited once a T-SQL scalar UDF is introduced. Again - bad, bad bad idea.

As gbn said - an indexed view is the way to go (if you can lose that left join). Another option is to use an Inline Table Valued function when you need that value; it will perform better than a computed column (provided that you add the appropriate indexes. The function would look like this:

CREATE FUNCTION dbo.fnCalcOutstandingBalance(@ItemReferance int)
RETURNS TABLE WITH SCHEMABINDING  AS RETURN
SELECT   Result = ISNULL(sum(Quantity),0) -
         (
           SELECT ISNULL([Accepted Quantity],0)
           FROM   dbo.[Order Transactions Table]
           WHERE  @ItemReferance = [Item Referance] 
         )
FROM     dbo.[Delivery Table]
GROUP BY [Item Referance]
HAVING   @ItemReferance = [Item Referance];

To leverage this function you need to understand APPLY. Here's some good reading on why T-SQL scalar UDF's are terrible for computed columns and constraints.

A Computed Column with a [scalar udf] might Impact Query Performance –Kun Cheng (SQLCAT)

Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints – Erik Darling

Another reason why scalar functions in computed columns is a bad idea – Erik Darling

Beware-row-row-operations-udf-clothing – Brian Moran

Be careful with constraints calling UDFs – Tibor Karaszi

Why does the Execution Plan include a scalar udf call for a persisted computed column? – Stack Overflow

Answer 3

For anyone who is interested, I have managed to find a workaround to this problem via the use of a Cursor (Thank you @gbn) to handle the calculations on the existing data and to populate a new field (CalculatedOB) with the corresponding calculated value.

I have used Triggers (on [Order Transactions Table].[Accepted Quantity] and [Delivery Table].[Quantity]) to handle any future changes to the outstanding balance.

Both Cursor and all of the Triggers use the fnCalcOutstandingBalance() function to work out the values.

Cursor to populate existing data:

declare @refid int;
declare @Result int;
declare refcursor cursor for
select [Item Referance] from [Order Transactions Table];

open refcursor

fetch next from refcursor into @refid

while @@FETCH_STATUS = 0
begin 
print @refid

fetch next from refcursor into @refid
set @Result = [dbo].[fnCalcOutstandingBalance](@refid)

update [Order Transactions Table] set CalculateOB = @Result 
    where [Item Referance] = @refid
end 

close refcursor;
deallocate refcursor;

Update Trigger Example:

CREATE TRIGGER [dbo].[UPDATE_AcceptedQty]
ON [dbo].[Order Transactions Table]
for update
AS

DECLARE @ItemRef int;
declare @result int;

IF UPDATE ([Accepted Quantity])
Begin

SELECT @ItemRef=i.[Item Referance] from INSERTED i;

SET @result = [dbo].[fnCalcOutstandingBalance](@ItemRef)

UPDATE [Order Transactions Table] set CalculateOB = @Result 
    where [Item Referance] = @ItemRef

END

GO

The combination of these two techniques allowed me to mimic the functionality of a Computed column without the constraints of determinism requirements or a performance hit.

Big Thanks to @gbn and @Alan Burstein for their contributions!