不能保留计算列 - 不是确定性的 [英] Cannot persist computed column - not deterministic

问题描述

我有一个计算列的函数：

  CREATE FUNCTION [dbo]。[GetAllocatedStartTime] @Week INT）
 RETURNS DATETIME 
 
与模式绑定
 AS BEGIN 
返回日期add（周，@周 - （1），dateadd（天， ，dateadd（week，datediff（week，（0），CONVERT（[varchar]（4），@ Year，（0））+' -  01-01'），（1））））
 END 
 
 GO 
  

我添加了 WITH schemabinding 希望它会使其确定性，所以我可以坚持。应该是两个输入 [Week] 和 [Year] 将始终产生相同的结果。

确切的错误是：

计算列'AllocatedTimeStart'

我在此列中使用此公式：

 （[dbo]。[GetAllocatedStartTime]（[Year]，[Week]））
  

和列defs：

 周] [int] NOT NULL，
 [Year] [int] NOT NULL，
 [AllocatedTimeStart] AS（[dbo]。[GetAllocatedStartTime] $ b  

任何想法？

将行更改为：

  week，@ Week-（1），dateadd（day，（ -  1），dateadd（week，datediff（week，（0），CONVERT（datetime，CONVERT（[varchar]（4），@ Year， +'0101'，112）），（1））））
  

说该列的公式无效。即使该功能保存得很好。

编辑2：

显示我正在做什么（或至少我试过）。没有什么额外的真的。因为它说的以前的函数（原始的）加上公式ref [dbo] .AllocatedStartDate（...）到它在列工作，但不是持久，它说它是非确定性的。因此，根据建议，我改变了FUNCTION，用新代码替换转换部分，所以函数现在看起来像：

  FUNCTION [dbo]。[GetSTime]（@ Year INT，@Week INT）
 
返回DATETIME 
与模式绑定
 AS BEGIN 
返回dateadd - （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）和它工作...

所以是的，我很困惑， SqlFiddle 不要使用它。

解决方案

CONVERT（[varchar] 4），@ Year，（0））+' - 01-01'正被传递到 DATEDIFF 日期，强制进行隐式转换。

从确定性函数：

CAST

确定性除非 datetime ， smalldatetime 或 sql_variant 。

CONVERT p>

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

...

$ b b

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

好吧，你不是调用，但是你依赖于一个隐式转换，我会期望像 CAST 。而不是依赖于此，我将切换到使用 CONVERT 并给出一个确定性的样式参数。

我会做： CONVERT（datetime，CONVERT（[varchar]（4），@ Year，（0））+'0101'，112）这样做后，函数本身变成确定性的

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

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.

The exact error is :

Computed column 'AllocatedTimeStart' in table 'Tmp_Bookings' cannot be persisted because the column is non-deterministic.

I am using this formula in the column :

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

And the column defs :

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

Any ideas?

EDIT:

Changed line to :

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.

EDIT 2:

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

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

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' is being passed to a DATEDIFF call, in a position where a date is expected, forcing an implicit conversion to occur.

From the rules for deterministic functions:

CAST

Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT

Deterministic unless one of these conditions exists:

...

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.

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.

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

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