Streamlining T-SQL user function for persisted computed column

Question

I am trying to streamline the following T-SQL user function to be used within a persisted computed column in one of my table. I understand that I can use a calculated column within a view among other methods but for various reasons due to user requirement this column is best implemented as such.

However in order for this scalar function to be used in a computer column, I am trying to make it as efficient and stream line as possible. There are parts of the function with the following SELECT COUNT statement

SELECT COUNT([JunctionID])
FROM dbo.ApplicationJunctionT
WHERE [AccountID] = @AccountID 
  AND [Outcome] IS NULL 
  AND [Change Account Status] IS NULL

that I use repeatedly and I'm wondering if there is a better way to reorganize this function for efficiency. I did try using CTE to wrap this select statement up but continually run into syntax issues and have since given up on that idea.

ALTER FUNCTION [dbo].[fnAccountReadyByID_Test] 
    (@JunctID INT = Null,
     @AccountID VARCHAR(20) = NULL)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @FamilyAccountReady as varchar(10),
            @TotalFamily as INT;

    SET @TotalFamily = (SELECT COUNT([JunctionID])
                        FROM dbo.ApplicationJunctionT
                        WHERE [AccountID] = @AccountID 
                          AND [Outcome] IS NULL 
                          AND [Change Account Status] IS NULL)

    IF @TotalFamily = 1
        SET @FamilyAccountReady = (SELECT AccountStatus 
                                   FROM dbo.ApplicationJunctionT 
                                   WHERE JunctionID = @JunctID)
    ELSE
        SET @FamilyAccountReady = (CASE 
                                      WHEN (SELECT COUNT([JunctionID]) 
                                            FROM dbo.ApplicationJunctionT 
                                            WHERE [AccountID] = @AccountID 
                                              AND [Outcome] IS NULL 
                                              AND [Change Account Status] IS NULL 
                                              AND [AccountStatus] = '-') > 0 
                                         THEN '-'
                                      WHEN (SELECT COUNT([JunctionID]) 
                                            FROM dbo.ApplicationJunctionT 
                                            WHERE [AccountID] = @AccountID 
                                              AND [Outcome] IS NULL 
                                              AND [Change Account Status] IS NULL 
                                              AND [AccountStatus] = 'Frozen') > 0 
                                         THEN 'Frozen'
                                      WHEN (SELECT COUNT([JunctionID]) 
                                            FROM dbo.ApplicationJunctionT 
                                            WHERE [AccountID] = @AccountID 
                                              AND [Outcome] IS NULL 
                                              AND [Change Account Status] IS NULL 
                                              AND [AccountStatus] = 'Closed') > 0 
                                         THEN 'Closed'
                                      WHEN (SELECT COUNT([JunctionID]) 
                                            FROM dbo.ApplicationJunctionT 
                                            WHERE [AccountID] = @AccountID 
                                              AND [Outcome] IS NULL 
                                              AND [Change Account Status] IS NULL 
                                              AND [AccountStatus] = 'Tentative') > 0 
                                         THEN 'Tentative'
                                      ELSE 'GRANT'
                               END)

       -- Return the result of the function
       RETURN UPPER(@FamilyAccountReady)
END

Additionally, even after reading through multiple articles online I am unable to find how the computer column is triggered. Is it triggered when any field on the record is updated? or is it only triggered when the variable fields are updated?

In this circumstance I am using the AccountID and JunctionID as variables and both are static fields that do not change. Will my computed column still work in this scenario?

As much as I dislike inefficiency, I may have to go with this design unless the expert community on here can suggest some improvement!

Many thanks in advance for your assistance!

I don't think this approach will work, as your function is non-deterministic. Were you able to make the whole setup work, even with this code? — Roger Wolf, Jun 12 '20 at 06:06
Hi Roger, Haven't tested to see if it works yet. At the moment I am using it within the trigger inside a view to update the designated column but I would like to delete that column and have it as a persisting computed column instead. — SeanY, Jun 12 '20 at 07:26
Aside: If nothing else you can replace your repeated `when ( select count ... ) > 0` logic with `when exists ( select 42 ... )`. There is no need to get a precise count that you are just going to compare to zero. — HABO, Jun 12 '20 at 11:32

score 0 · Accepted Answer · answered Jun 15 '20 at 12:23

Try something like this if you want to use a CTE:

WITH CTE AS (
    SELECT [AccountStatus]
    FROM dbo.ApplicationJunctionT 
    WHERE [AccountID] = @AccountID 
        AND [Outcome] IS NULL 
        AND [Change Account Status] IS NULL 
) 
SELECT @FamilyAccountReady = CASE 
    WHEN EXISTS (SELECT * FROM CTE WHERE [AccountStatus] = '-') THEN '-'
    WHEN EXISTS (SELECT * FROM CTE WHERE [AccountStatus] = 'Frozen') THEN 'Frozen'
    WHEN EXISTS (SELECT * FROM CTE WHERE [AccountStatus] = 'Closed') THEN 'Closed'
    WHEN EXISTS (SELECT * FROM CTE WHERE [AccountStatus] = 'Tentative') THEN 'Tentative'
    ELSE 'GRANT'
END

However, I don't think it would be a good idea to use a computed column based on a function which accesses other tables: you will get an error saying "Computed column 'ColumnName' in table 'TableName' cannot be persisted because the column is non-deterministic."

Good Mornign Razvan, Appreciate your advise along with Roger and HABO, I was exploring this option as calculating this on the trigger seems to require the sending of too much data back and forth from user to the server. I may need to rewrite the function to make it more streamline but your suggestion in using the CTE is definitely very helpful. Thank you very much guys for your input! — SeanY, Jun 16 '20 at 02:46
If you use a trigger the data does not get sent back and forth from the user to the server (the trigger is executed by the server). — Razvan Socol, Jun 16 '20 at 04:18

Streamlining T-SQL user function for persisted computed column

1 Answers1