Computed Column cannot be Persisted

Question

I have a custom function, and I am trying to created a persisted column using this function.

It is giving me following error.

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

Here is the function:

ALTER FUNCTION [dbo].[FormatSSN]() RETURNS VARCHAR(11) AS BEGIN     return ''; END 

Here is the query to add the column using the function:

ALTER TABLE SomeTable ADD FormattedSSN as dbo.FormatSSN() PERSISTED 

Please suggest if there is any way out. Thanks.

Answer 1

Add WITH SCHEMABINDING to the function like this:

ALTER FUNCTION [dbo].[FormatSSN] ( @SSN    VARCHAR(9) ) RETURNS CHAR(11) WITH SCHEMABINDING AS BEGIN   your stuff here END 

and then run this to verify:

IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 1    PRINT 'Function is detrministic.' ELSE IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 0    PRINT 'Function is NOT detrministic' GO 

Works here.

Answer 2

How about specifying the definition directly:

ALTER TABLE SomeTable ADD FormattedSSN as     case when len(EmployeeSSN) = 9 then             substring(EmployeeSSN, 1, 3) + '-' +             substring(EmployeeSSN, 4, 2) + '-' +             substring(EmployeeSSN, 6, 4)     else EmployeeSSN end PERSISTED