Computed column '...' in table '...' cannot be persisted because the column is non-deterministic?

Question

I have the following function.

create FUNCTION [dbo].[Valid](@c char(9))
RETURNs bit
as
begin
    DECLARE @sum int = 0;
    return 0 
end

And the following SQL

create table test(A char(10))
alter table test add C as dbo.Valid(A) persisted;

has the error of

Msg 4936, Level 16, State 1, Line 50

Computed column 'C' in table 'test' cannot be persisted because the column is non-deterministic.

Answer 1

Functions must be decorated with the WITH SCHEMABINDING hint, otherwise SQL Server skips the validation of determinism (a performance optimization), and treats that default result as not being deterministic.

Answer 2

As Elaskanator stated, you should add WITH SCHEMABINDING at the definition of your function like below:

alter FUNCTION [dbo].[Valid](@c char(9))
RETURNS BIT WITH SCHEMABINDING
as
begin
    DECLARE @sum int = 0;
    return 0 
END

create table test(A char(10))
alter table test add C AS dbo.Valid(A) persisted;

More info: https://www.mssqltips.com/sqlservertip/3338/change-all-computed-columns-to-persisted-in-sql-server/