在SQL Server 2005中保留计算的datetime列

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:

我在表中有一个XML列;我希望将该XML中的某个值“提升”为计算列,并将其编入索引以便更快地进行搜索。我有一个函数,它接收XML信息并输出感兴趣的元素,如下所示:

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.

Msg 4936,Level 16,State 1,Line 2表'CustomMetadataTable'中的计算列'StartDate'无法保留,因为该列是非确定性的。

It works if I:

它适用于我:

work with varchar, int, double (i.e. other than datetime) values

使用varchar,int,double(即不是datetime)值

remove the PERSISTED keyword (but then I can't create an index on the column)

删除PERSISTED关键字(但后来我无法在列上创建索引)

I should also mention that datetime values are in XSD datetime format. Any ideas? Thanks.

我还应该提到日期时间值是XSD日期时间格式。有任何想法吗?谢谢。

1 个解决方案

#1

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:

除非存在以下条件之一,否则CONVERT是确定性的:

Source type is sql_variant.

源类型是sql_variant。

Target type is sql_variant and its source type is nondeterministic.

目标类型是sql_variant,其源类型是不确定的。

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.

源或目标类型是datetime或smalldatetime,另一个源或目标类型是字符串,并指定了非确定性样式。要确定性,样式参数必须是常量。此外,小于或等于100的样式是不确定的,样式20和21除外。大于100的样式是确定性的,样式106,107,109和113除外。

It might help if you use CONVERT with style 127

如果您将CONVERT与样式127一起使用可能会有所帮助

#1