Can't set PERSISTED on a computed column??

Tech-Archive recommends: Speed Up your PC by fixing your registry

Can't set PERSISTED on a computed column??


// Table structure
CREATE TABLE [dbo].[PatientNodes](
[PrimaryKey] [int] IDENTITY(1,1) NOT NULL,
[PatientNode] [xml] NOT NULL,
CONSTRAINT [PK__PatientNodes__062DE679] PRIMARY KEY CLUSTERED
([PrimaryKey] ASC)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

//Typical data in PatientNode column.
<Patient Guid="a3c03bf0-66a5-4561-8dcb-142dbd45bafb">
<Number>777777</Number>
<LegalNames>
<Name StartDate="2006-08-30T13:37:21.1212167-04:00">
<First>Paul</First>
<Last>Dickerson</Last>
</Name>
</LegalNames>
</Patient>


// UDF function
CREATE FUNCTION [dbo].[udf_GuidFromXML] ( @PatientNode xml)
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @Guid uniqueidentifier
SELECT @Guid = @PatientNode.value('/Patient[1]/@Guid',
'uniqueidentifier')
RETURN @Guid
END


/// Adding calculated column
ALTER TABLE PatientNodes
ADD PatientGuid AS [dbo].[udf_GuidFromXML](PatientNode) PERSISTED


This works fine without the PERSISTED keyword, but with it I get this:

"Computed column 'PatientGuid' in table 'PatientNodes' cannot be
persisted because the column is non-deterministic."

I have read the INFo in BOL: "Creating Indexes on Computed Columns",
"Deterministic and Nondeterministic Functions", but can't see my
problem. Getting that guid and returning it looks pretty deterministic
to me. Any suggestions.

Paul V. Sullivan
.

Quantcast