Computed Column cannot be Persisted

Question

I have function which I am using in one of the SQL Job (see function below). I am trying to created a persisted column on this function based on existing column on the same table.

It is giving me following error.

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

Please suggest if there is any way out. Thanks.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatSSN]
(
@SSN    VARCHAR(9)
)
RETURNS CHAR(11)
AS
BEGIN
    Declare @FormattedSSN char(11);
    IF(LEN(@SSN) = 9)
    BEGIN
         set @FormattedSSN = SUBSTRING(@SSN, 1, 3) + '-' + SUBSTRING(@SSN, 4, 2) + '-' + SUBSTRING(@SSN, 6,4) 
    END
    ELSE
    BEGIN
      set @FormattedSSN = @SSN
    END

return @FormattedSSN;

END

EDIT: Query used below

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

Answer 1

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

Answer 2

Instead of calling the UDF, Set the computed column expression to

Case When Len(EmployeeSSN) = 0 Then 
      SUBSTRING(EmployeeSSN, 1, 3) + '-' + 
      SUBSTRING(EmployeeSSN, 4, 2) + '-' + 
      SUBSTRING(EmployeeSSN, 6, 4)
    Else EmployeeSSN End

In the Create Table script you can add a column:

[NewColumnName]  As
   (Case When len([UpdateUserId])=(0) T
         Then (((substring([UpdateUserId],(1),(3))+'-')+
                 substring([UpdateUserId],(4),(2)))+'-')+
                 substring([UpdateUserId],(6),(4)) 
         Else [UpdateUserId] End) PERSISTED,

Answer 3

Add WITH SCHEMABINDING 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.