noob.spt noob.spt - 7 months ago 21
SQL Question

Computed Column cannot be Persisted

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

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.