无法持久化计算列 - 不确定

2022-01-16 00:00:00 database calculated-columns sql-server sql-server-2008-r2

我有一个计算列的函数:

I have this function for a computed column :

CREATE FUNCTION [dbo].[GetAllocatedStartTime](@Year INT, @Week INT)
RETURNS DATETIME

WITH schemabinding
AS BEGIN
    RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT([varchar](4),@Year,(0))+'-01-01'),(1))))
END

GO

我添加了 WITH 架构绑定，希望它能够使其具有确定性，以便我可以持久保存它.应该是因为两个输入 [Week] 和 [Year] 将始终产生相同的结果.

I added the WITH schemabinding in the hope it would make it deterministic so I can persist it. It should be as the two inputs [Week] and [Year] will always yield the same results.

确切的错误是:

表Tmp_Bookings"中的计算列AllocatedTimeStart"无法持久化，因为该列是不确定的.

我在列中使用这个公式:

I am using this formula in the column :

([dbo].[GetAllocatedStartTime]([Year],[Week]))

还有列定义:

[Week] [int] NOT NULL,
[Year] [int] NOT NULL,
[AllocatedTimeStart]  AS ([dbo].[GetAllocatedStartTime]([Year],[Week])),

有什么想法吗?

将行改为:

RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)),(1))))

但现在我收到一条错误消息，提示该列的公式无效.即使函数保存得很好.

But now I get an error saying the formula for the column is invalid. Even though the function saves fine.

编辑 2:

我已经准确地展示了我在做什么(或者至少我已经尝试过).真的没有什么额外的.正如它所说的前一个函数(原始函数)与列中的公式 ref [dbo].AllocatedStartDate(...) 相结合有效，但没有持续存在，它表示它是不确定的.所以根据建议我改变了FUNCTION，用新代码替换了转换部分，所以函数现在看起来像:

I've shown exactly what I am doing (or atleast I've tried). There is nothing extra really. As it says the previous function (original one) coupled with the formula ref [dbo].AllocatedStartDate(...) to it in the column worked, but was not persisting, it said it was non deterministic. So according to the suggestion I changed the FUNCTION, replacing the conversion part with the new code, so the function now looks like :

FUNCTION [dbo].[GetSTime](@Year INT, @Week INT)

RETURNS DATETIME
WITH schemabinding
AS BEGIN
    RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)),(1))))
END

然后我在计算字段 (([dbo].[GetAllocatedStartTime]([Year],[Week]))) 中尝试了与以前相同的公式 ...它拒绝了该公式，说它无效......这很奇怪，因为公式是相同的，所以它必须对更改的函数进行某种检查并发现它无效，这也很奇怪，因为我做了一个简单的 SELECT dbo.GetAllocatedStartTime(2012,13)​​ 并且成功了...

Then I tried the same formula as before in the computed field (([dbo].[GetAllocatedStartTime]([Year],[Week]))) ... and it rejects the formula, says its not valid... which is strange as the formula is the same, so it must be doing some sort of check of the changed function and finding that to be invalid, which is also strange because I did a plain SELECT dbo.GetAllocatedStartTime(2012,13) and it worked...

所以是的，我很困惑，而且我从未见过 SqlFiddle 没关系使用它.但真的没有什么比我刚才说的更多了.

So yes I am confused, and I've never seen SqlFiddle never mind use it. But really there is nothing more than what I have just said.

推荐答案

CONVERT([varchar](4),@Year,(0))+'-01-01' 正在传递到 DATEDIFF 调用，在预期日期的位置，强制进行隐式转换.

CONVERT([varchar](4),@Year,(0))+'-01-01' is being passed to a DATEDIFF call, in a position where a date is expected, forcing an implicit conversion to occur.

来自确定性函数的规则:

CAST

确定性，除非与 datetime、smalldatetime 或 sql_variant 一起使用.

Deterministic unless used with datetime, smalldatetime, or sql_variant.

转换

确定性，除非存在以下条件之一:

Deterministic unless one of these conditions exists:

...

源或目标类型为datetime或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.

好吧，您都没有调用，但是您依赖于隐式转换，我希望它的行为类似于 CAST.我不依赖这个，而是改用 CONVERT 并给出一个确定的样式参数.

Well, you're calling neither, but you're relying on an implicit conversion, which I'd expect to act like CAST. Rather than rely on this, I'd switch to using CONVERT and give a deterministic style parameter.

所以，我会这样做: CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112) 代替它.这样做后，函数本身就变得确定了

So, I'd do: CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112) in its place. Having done so, the function itself becomes deterministic

相关文章