sql - 在 SQL Server 2005 中保留计算的日期时间列

Question

我的表中有一个 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 

但是，当我尝试创建计算列时:

ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS ([dbo].[fComputeValue]([CustomMetadataColumn])) PERSISTED

我收到以下错误:

Msg 4936, Level 16, State 1, Line 2 Computed column 'StartDate' in table 'CustomMetadataTable' cannot be persisted because the column is non-deterministic.

如果我:

• 使用 varchar、int、double(即日期时间以外的值)
• 删除 PERSISTED 关键字(但随后我无法在该列上创建索引)

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

Answer 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 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.

如果您使用 CONVERT 和样式 127 可能会有所帮助