Index on a Computed Column(using udf)

Question

I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for trying to make the column persisted.

This is the udf func i am using :

CREATE FUNCTION x.bal(@ID NVARCHAR(128))
RETURNS DECIMAL WITH SCHEMABINDING
AS 
BEGIN
DECLARE @SUM DECIMAL ;
    WITH CTE AS (
    SELECT ROW_NUMBER() OVER (ORDER BY date ASC, type DESC) AS ROWNUMBER,[Id]
     T_id
    FROM x.entries
    )
    
    SELECT @SUM = (SELECT SUM(AMOUNT) FROM CTE L1
    WHERE L1.B_ID = L2.B_ID  AND L1.DATE <= L2.DATE AND L1.ROWNUMBER<L2.ROWNUMBER)
    FROM CTE L2
    WHERE L2.ID = @ID
    RETURN @SUM
END
GO 

to add the column :

ALTER TABLE x.Entries
ADD bal AS dbo.bal(id) 
CREATE INDEX IDX_bal ON x.Entries(bal)

I have also found that the error is only on 2017 local sql server where as for Azure sql ,I see the following : If I run the Column Property check before creating the index, it works but if missed, it fails.

Answer 1

A PERSISTED COMPUTED column can't use Window functions like ROW_NUMBER(). It's one of the limitations because ROW_NUMBER() is not a deterministic function and only deterministic functions can be used with PERSISTED COMPUTED columns.

Paul White does a great write up on how to properly use PERSISTED COMPUTED columns in his article here.

Specifically he mentions how PERSISTED was added only for deterministic columns:

Persisted computed columns were added to the product specifically to allow indexes to be built on deterministic but "imprecise" (floating point) columns

If you could add a few more details on what your end goal is, I could try to suggest an alternative solution to solve the problem (e.g. what is the purpose of your UDF)?