计算列非确定性... [英] Computed column non-deterministic...

问题描述

大家好，

我在SQL Server 2005计算列中有一点问题。

我正在使用遗留表来存储字段中的日期定义为 NULLABLE CHAR（10），格式为dd-MM-yyyy。

当然这根本不起作用......所以我想创建一个持久计算列，将值存储为smalldatetime。



首先，以下查询运行时没有错误：

Hi all,
I have a little problem in a SQL Server 2005 computed column.
I'm working with a legacy table which stores a date in a field defined as NULLABLE CHAR(10) in the format dd-MM-yyyy.
Of course that doesn't work at all... So I want to create a persisted computed column that stores the value as a smalldatetime.

First of all, the following query runs with no errors:

SELECT CONVERT(SMALLDATETIME, RIGHT(MyField, 4) + SUBSTRING(MyField, 4, 2) + LEFT(MyField, 2))
  FROM MyTable

我把上面的公式放在SQL Serv的公式字段中呃它给我错误错误验证列'MyComputedColumn'的公式'。

我可以忽略错误并保存我的表格，一切看起来都很好。



但是现在我想让计算列保持不变并且我得到以下错误：

When I put the above formula in the formula field in SQL Server it gives me the error "Error validating the formula for column 'MyComputedColumn'".
I can ignore the error and save my table and everything looks fine.

But now I want to make the computed column persisted and I get the following error:

"Unable to modify table.  
Computed column 'MyComputedColumn' in table 'Tmp_MyTable' cannot be persisted because the column is non-deterministic."

我在Google上搜索过该问题，但这一切对我来说都没有意义......

有什么想法吗？

谢谢。

I've searched the problem on Google, but it all makes little sense to me...
Any idea's?
Thanks.

推荐答案

它对我有意义！：笑：

这解释了它： http：// msdn.microsoft.com/en-us/library/ms178091.aspx [ ^ ]：

It makes sense to me! :laugh:
This explains it: http://msdn.microsoft.com/en-us/library/ms178091.aspx[^]:

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

所以尝试添加一个大于100的样式作为第三个CONVERT参数。

So try adding a style greater than 100 to the end as the third CONVERT parameter.

这篇关于计算列非确定性...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！