Index on a Computed Column(using udf) 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 &lt;= L2.DATE AND L1.ROWNUMBER&lt;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.
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 &quot;imprecise&quot; (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)?