Есть sql-функция GetPhaseID, которая использует CTE запрос типа



Следующий запрос стал выдавать ошибку на 2019 сервере:



На 2017 сервере все было нормально. В чем причина?

Спасибо

Да, у меня выполняется без ошибки, у клиента - ошибка
нашел что-то похожее, рекомендуют просто перекомпилировать эту функцию:

https://dba.stackexchange.com/questions/256861/after-sql-restart-cte-causes-invalid-object-name-error-for-a-short-period-in-sq

Смог воспроизвести у себя на 2019 только когда меняю совместимость базы на 150 как у клиента (для 140 все нормально)
Это бага MS Sql?

ALTER DATABASE MYTest SET COMPATIBILITY_LEVEL = 150

select * from erp.ProjPhase as a where erp.[ GetPhaseID ](a.company, a.projectid, a.phaseid) <> '1'

Msg 208, Level 16, State 1, Procedure GetPhaseID, Line 36 [Batch Start Line 12]
Invalid object name 'parent_list'.

Спасибо, по документации, действительно говорится, что во встроенный функциях CTE использовать нельзя

The UDF does not contain references to Common Table Expressions (CTEs).

Проблема в том, что по умолчанию при переходе на 2019 параметр базы будет TSQL_SCALAR_UDF_INLINING = ON то есть все функции будут рассматриваться как встраиваемые. Отключать его не хочется.
Последние два вопроса

1) Можно проверить каждую существующую функцию, удовлетворяет ли она условиям на встраиваемость и если нет то в скриптах на их создание добавить хинт WITH INLINE = OFF , но этот хинт не поддерживается в ранних версиях.
Можно ли сделать универсальный скрипт для любой версии ?

2) Почему после перекомпиляции функции в 2019 сервер уже не ругается? Хотя рекурсивные CTE там остались.

Спасибо

Да, согласен, спасибо, хотя в этом направлении мне пока не все понятно и многому надо учиться. Рекурсивный CTE использовал для простого обхода дерева снизу до корня, для подобных целей его, наверное, и придумали.

По основному вопросу оказалось, что при переходе на (2019) с (2017) через простое восстановление базы sql для каждой функции сам выставляет новые параметры inline_type, is_inlineable, указывающие, может ли она быть встроенной или нет:

SELECT inline_type, is_inlineable FROM sys.sql_modules where OBJECT_NAME(object_id) = 'GetPhaseID'

Для большинства функций делает это правильно, типа, для простых - 1, для сложных - 0. Но именно для функций, использующих CTE он ошибается и ставит 1 (встраиваемая) и тогда при использовании ее в запросе вылезает ошибка.
Если же функцию перекомпилировать, то sql правильно ее распознает и ставит в эти поля 0.
Более того, в такой функции он не дает в скрипте на создание поставить хинт WITH INLINE = ON

Пока вижу один вокэраунд - при переходе на 2019 заставлять запустить скрипты на пересоздание функций.

Как-то можно это автоматизировать? клиентов много и каждый сам решает в какой момент перейти на 2019.