Невозможно сохранить вычисляемый столбец - недетерминированный

У меня есть эта функция для вычисляемого столбца:

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] всегда будут давать одинаковые результаты.

Точная ошибка:

Невозможно сохранить вычисляемый столбец AllocatedTimeStart в таблице Tmp_Bookings, поскольку этот столбец недетерминирован.

Я использую эту формулу в столбце:

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

И столбец defs:

[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:

Я точно показал, что делаю (или, по крайней мере, пробовал). На самом деле ничего лишнего. Как говорится, предыдущая функция (исходная) в сочетании с формулой ref [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 не говоря уже о его использовании. Но на самом деле это не более чем то, что я только что сказал.


person sprocket12    schedule 02.01.2013    source источник
comment
Проблема не в вашей функции, а в вычисляемом столбце в вашей таблице. Разместите это определение в своем вопросе, чтобы мы могли вам помочь   -  person Lamak    schedule 02.01.2013
comment
Для какой версии SQL-сервера это нужно?   -  person ypercubeᵀᴹ    schedule 02.01.2013
comment
Какую строку вы изменили? Вы имеете в виду, что изменили определение вычисляемого столбца на прямое использование RETURN dateadd(...)?   -  person Andriy M    schedule 02.01.2013
comment
@AndriyM да, как предложил Дэмиен, я вынул исходное преобразование и заменил его более явной версией.   -  person sprocket12    schedule 02.01.2013
comment
Тогда проблема должна быть в ключевом слове RETURN. Синтаксис определения вычисляемого столбца - ColumnName AS expression. RETURN не может быть частью выражения, просто отбросьте его и оставьте dateadd(...). Сказав это, я действительно не понимаю, где Дэмиен предлагает заменить ваш UDF фактическим выражением. Он просто говорит вам, как сделать ваше выражение (и, как следствие, вашу функцию) детерминированным.   -  person Andriy M    schedule 02.01.2013
comment
Очевидно, вы не показали нам в точности, что вы делаете, поскольку в показанном вами определении вычисляемого столбца не указано PERSISTED. Не могли бы вы создать небольшой, но полный пример в виде единого скрипта, который демонстрирует проблему? (Возможно использование sqlfiddle для его размещения)   -  person Damien_The_Unbeliever    schedule 02.01.2013
comment
@Damien_The_Unbeliever см. Редактирование 2.   -  person sprocket12    schedule 02.01.2013
comment
Вот SQL Fiddle с последней версией вашей функции и постоянным столбцом. . Похоже, работает или ...?   -  person Mikael Eriksson    schedule 02.01.2013
comment
@MikaelEriksson Ссылка загружает страницу, на которой написано «Загрузка навсегда», а также указано «Схема сборки», на которую нельзя щелкнуть из-за маски над ней. Также я должен упомянуть, что все это я делаю через SSMS 2008 R2   -  person sprocket12    schedule 03.01.2013
comment
Похоже, возникла проблема с SQL Fiddle. это работает лучше? sqlfiddle.com/#!6/8fd88/1/0   -  person Mikael Eriksson    schedule 03.01.2013
comment
@MikaelEriksson Спасибо за это, ваш последний работает. Значит, возникла проблема с тем, как графический интерфейс работает в SSMS. Я постараюсь внести поправки в таблицу, чтобы использовать сохраненный код SQL. Затем отметьте ответ.   -  person sprocket12    schedule 03.01.2013


Ответы (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. Вместо того, чтобы полагаться на это, я бы переключился на использование CONVERT и дал параметр детерминированного стиля.

Итак, я бы сделал: CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112) на его месте. После этого сама функция становится детерминированной.

person Damien_The_Unbeliever    schedule 02.01.2013
comment
Если бы на SQL-Server 2012, они также могли бы использовать - вместо CONVERT([varchar](4),@Year,(0))+'-01-01' - функцию DATEFROMPARTS([Year],1,1): SQL-Fiddle тест - person ypercubeᵀᴹ; 02.01.2013
comment
Привет, я попытался заменить деталь, но обнаружил другую проблему: / см. Правку выше. - person sprocket12; 02.01.2013