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

Question

我有这个用于计算列的函数：

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 schemabinding，希望它能使公式变为确定性的，这样我就可以将其持久化。因为两个输入 [Week] 和 [Year] 总是产生相同的结果。

具体错误如下：

表 'Tmp_Bookings' 中的计算列 'AllocatedTimeStart' 无法持久化，因为该列是非确定性的。

我在该列中使用了以下公式：

([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))))

但是现在我收到一个错误，说该列的公式无效。尽管函数保存得很好。

编辑2:

我已经展示了我正在做的事情（或者至少我已经尝试过了）。实际上没有什么额外的东西。正如它所说，原始函数加上公式引用[dbo].AllocatedStartDate(...)在列中起作用，但不会持久化，因为它被认为是非确定性的。所以根据建议，我更改了这个函数，用新代码替换了转换部分，所以现在这个函数看起来像：

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)就能运行...

所以对于我来说，我很困惑，而且我从未见过SqlFiddle更别说使用它了。但是确实没有什么比我刚才说的更多了。

- sprocket12

---

问题不在于你的函数，而是在于你表格上的计算列。请将该定义发布在你的问题中，以便我们帮助你。 - Lamak

这是针对哪个版本的SQL-Server？ - ypercubeᵀᴹ

@AndriyM 是的，就像Damien建议的那样，我去掉了原始的转换并用更明确的版本替换了它。 - sprocket12

@MikaelEriksson 这个链接加载页面，页面上一直显示“正在加载”，还有一个名为“构建模式”的按钮被遮挡住了，无法点击。此外，我应该在这里提到，我是通过 SSMS 2008 R2 进行所有操作的。 - sprocket12

好像 SQL Fiddle 出了问题。这个链接是否更好？http://sqlfiddle.com/#!6/8fd88/1/0 - Mikael Eriksson

Answer 1

CONVERT([varchar](4),@Year,(0))+'-01-01'被传递给DATEDIFF函数，在需要日期的位置上，强制进行隐式转换。

根据确定性函数规则：

CAST

除非与datetime、smalldatetime或sql_variant一起使用，否则是确定性的。

CONVERT

除非存在以下条件之一，否则为确定性：

...

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

嗯，你并没有调用CAST函数，而是依赖于隐式转换，我希望它能像CAST一样运行。但我建议你使用CONVERT函数，并给定一个确定的样式参数，以免依赖隐式转换。

因此，我会这样做：CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)代替它。这样做后，函数本身变得确定性更强。

- Damien_The_Unbeliever

---

如果在SQL-Server 2012上，他们也可以使用DATEFROMPARTS([Year],1,1)函数来代替CONVERT([varchar](4),@Year,(0))+'-01-01'。SQL-Fiddle测试 - ypercubeᵀᴹ

嗨，我试图替换这部分，但是我遇到了另一个问题:/ 请参见上面的编辑。 - sprocket12