Geek Answers Handbook

Saving a computed datetime column in SQL Server 2005

I have an XML column in a table; I want to “push” a specific value in this XML as a computed column and index it for faster searching. I have a function that accepts XML information and displays the element of interest, for example:

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 a calculated 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 The calculated column 'Start Date' in the CustomMetadataTable table cannot be saved because the column is non-deterministic.

It works if I:

  • work with varchar, int, double values ​​(i.e. other than datetime)
  • remove the keyword PERSISTED (but then I cannot create an index in the column)

, datetime XSD. ? .

+5
1

:

CREATE FUNCTION [dbo].[fComputeValue] (@data XML)
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
  RETURN @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'varchar(50)')
END


ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS (convert(datetime,([dbo].[fComputeValue]([CustomMetadataColumn]), 127)) PERSISTED


return convert(datetime, @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'varchar(50)'), 127)

:

CONVERT , :

- sql_variant.

- sql_variant .

- smalldatetime, - , . , . , 100, , 20 21. 100 , 106, 107, 109 113.

, CONVERT 127

+6

More articles:


All Articles