I have an XML column in a table; I want to "promote" a certain value in that XML as a computed column and index it for faster searching. I have a function that takes in the XML information and outputs the element of interest, like this:
CREATE FUNCTION [dbo].[fComputeValue] (@data XML)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
RETURN @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'datetime')
END
However when I try to create the computed column:
ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS ([dbo].[fComputeValue]([CustomMetadataColumn])) PERSISTED
I get the following error:
Msg 4936, Level 16, State 1, Line 2
Computed column
'StartDate'
in table 'CustomMetadataTable' cannot be
persisted because the column is
non-deterministic.
It works if I:
- work with varchar, int, double (i.e. other than datetime) values
- remove the PERSISTED keyword (but then I can't create an index on the column)
I should also mention that datetime values are in XSD datetime format.
Any ideas? Thanks.
What about:
CREATE FUNCTION [dbo].[fComputeValue] (@data XML)
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
RETURN @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'varchar(50)')
END
and:
ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS (convert(datetime,([dbo].[fComputeValue]([CustomMetadataColumn]), 127)) PERSISTED
or:
return convert(datetime, @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'varchar(50)'), 127)
From books online:
CONVERT
is Deterministic unless one
of these conditions exists:
Source type is sql_variant.
Target type is sql_variant and its
source type is nondeterministic.
Source or target type is datetime or
smalldatetime, the other source or
target type is a character string, and
a nondeterministic style is specified.
To be deterministic, the style
parameter must be a constant.
Additionally, styles less than or
equal to 100 are nondeterministic,
except for styles 20 and 21. Styles
greater than 100 are deterministic,
except for styles 106, 107, 109 and
113.
It might help if you use CONVERT
with style 127