在 SQL Server 2005 中保留计算的日期时间列 [英] Persisting a computed datetime column in SQL Server 2005

问题描述

我在表中有一个 XML 列；我想将该 XML 中的某个值提升"为计算列并为其编制索引以加快搜索速度.我有一个函数接收 XML 信息并输出感兴趣的元素，如下所示:

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

我收到以下错误:

消息 4936，级别 16，状态 1，第 2 行计算列'开始日期'在表 'CustomMetadataTable' 中不能是坚持，因为该列是非确定性.

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 日期时间格式.有任何想法吗?谢谢.

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

推荐答案

关于:

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 是确定性的，除非存在以下条件:

CONVERT is Deterministic unless one of these conditions exists:

源类型为 sql_variant.

Source type is sql_variant.

目标类型是 sql_variant 及其源类型是不确定的.

Target type is sql_variant and its source type is nondeterministic.

源或目标类型是日期时间或smalldatetime，其他来源或目标类型是一个字符串，并且指定了不确定的样式.为了确定性，风格参数必须是常数.此外，样式小于或等于 100 是不确定的，样式 20 和 21 除外. 样式大于 100 是确定性的，除了样式 106、107、109 和113.

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

这篇关于在 SQL Server 2005 中保留计算的日期时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！