Использование нескольких строк из нескольких таблиц для сохраняемого вычисляемого столбца со скалярной UDF

Question

Я пытаюсь создать новое поле в таблице " Заказы транзакций" в качестве вычисляемого столбца с сохранением, используя значение Scalar UDF в качестве значения для поля.

Я понимаю, что требование к столбцу Persisted состоит в том, что значение является детерминированным, а это означает, что UDF для нескольких таблиц, который у меня есть, является недетерминированным, поскольку он не использует поля из исходной таблицы.

Функция:

USE [MyDatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[fnCalcOutstandingBalance]    
Script Date: 08/10/2018 14:01:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnCalcOutstandingBalance](@ItemReferance int)

RETURNS INT
WITH SCHEMABINDING 
AS
Begin
DECLARE @AcceptedQty INT
DECLARE @SumOfQty INT
DECLARE @Result INT

SELECT @AcceptedQty = 

    ISNULL([Accepted Quantity],0)
    FROM 
    dbo.[Order Transactions Table]
    WHERE @ItemReferance = [Item Referance] 

SELECT @SumOfQty =
    ISNULL(sum(Quantity),0)
    FROM dbo.[Delivery Table]
    GROUP BY [Item Referance]
    HAVING @ItemReferance = [Item Referance]

    SET @Result = ISNULL(@AcceptedQty,0) - ISNULL(@SumOfQty,0)

return @Result

END

Я ищу обходной путь, чтобы иметь возможность использовать значение, сгенерированное из вышеуказанной функции в таблице транзакций заказа.

Добавление столбца:

ALTER TABLE [Order Transactions Table]
ADD CalcOB AS [dbo].[fnCalcOutstandingBalance]([Item Referance]) PERSISTED

Я проверил эту функцию, и она работает как вызов отдельной функции в select, как и должно быть. Проблема в том, что мне нужно, чтобы это использовалось в вычисляемом столбце, не являясь виртуальным столбцом.

Answer 1

Для тех, кто заинтересован, мне удалось найти обходной путь для этой проблемы с помощью курсора (спасибо @gbn) для обработки вычислений на существующих данных и для заполнения нового поля (CalculatedOB) соответствующим вычисленным значением,

Я использовал триггеры (в [Таблица транзакций заказа].[Принятое количество] и [Таблица доставки].[Количество]) для обработки любых будущих изменений непогашенного остатка.

И Курсор, и все Триггеры используют функцию fnCalcOutstandingBalance() для обработки значений.

Курсор для заполнения существующих данных:

declare @refid int;
declare @Result int;
declare refcursor cursor for
select [Item Referance] from [Order Transactions Table];

open refcursor

fetch next from refcursor into @refid

while @@FETCH_STATUS = 0
begin 
print @refid

fetch next from refcursor into @refid
set @Result = [dbo].[fnCalcOutstandingBalance](@refid)

update [Order Transactions Table] set CalculateOB = @Result 
    where [Item Referance] = @refid
end 

close refcursor;
deallocate refcursor;

Пример триггера обновления:

CREATE TRIGGER [dbo].[UPDATE_AcceptedQty]
ON [dbo].[Order Transactions Table]
for update
AS

DECLARE @ItemRef int;
declare @result int;

IF UPDATE ([Accepted Quantity])
Begin

SELECT @ItemRef=i.[Item Referance] from INSERTED i;

SET @result = [dbo].[fnCalcOutstandingBalance](@ItemRef)

UPDATE [Order Transactions Table] set CalculateOB = @Result 
    where [Item Referance] = @ItemRef

END

GO

Сочетание этих двух методов позволило мне имитировать функциональность столбца Computed без ограничений требований детерминизма или снижения производительности.

Большое спасибо @gbn и @Alan Burstein за их вклад!

Answer 2

@gbn ударил его из парка своим ответом, но позвольте мне добавить свои 0,02 доллара. Поскольку ваш скалярный UDF обращается к таблицам, я уверен, что вы не сможете сохранить этот столбец. Тем не менее, давайте будем на 100% ясны:

Ничего не получится, если вы добавите вычисляемый столбец описанным способом, и вам будет что терять.

Во-первых, даже если вы можете сохранить этот столбец, любые запросы, которые обращаются к этой таблице, будут выполняться медленнее, а иногда даже медленнее. Скалярные пользовательские функции T-SQL для вычисляемых столбцов, в качестве ограничений или для значений по умолчанию делают запросы, ссылающиеся на эту таблицу, непараллелизуемыми; только серийное исполнение! Кроме того, доступные оптимизации становятся существенно ограниченными после введения скалярного UDF T-SQL. Опять же - плохая, плохая плохая идея.

Как сказал gbn - индексированное представление - это путь (если вы можете потерять это левое соединение). Другой вариант - использовать функцию Inline Table Valued, когда вам нужно это значение; он будет работать лучше, чем вычисляемый столбец (при условии, что вы добавите соответствующие индексы. Функция будет выглядеть так:

CREATE FUNCTION dbo.fnCalcOutstandingBalance(@ItemReferance int)
RETURNS TABLE WITH SCHEMABINDING  AS RETURN
SELECT   Result = ISNULL(sum(Quantity),0) -
         (
           SELECT ISNULL([Accepted Quantity],0)
           FROM   dbo.[Order Transactions Table]
           WHERE  @ItemReferance = [Item Referance] 
         )
FROM     dbo.[Delivery Table]
GROUP BY [Item Referance]
HAVING   @ItemReferance = [Item Referance];

Чтобы использовать эту функцию, вы должны понимать, ПРИМЕНИТЬ. Вот хорошее чтение о том, почему скалярные пользовательские функции T-SQL ужасны для вычисляемых столбцов и ограничений.

Вычисляемый столбец с [скалярным udf] может повлиять на производительность запроса - Кун Ченг (SQLCAT)

Еще один скрытый убийца параллелизма: скалярные UDF в ограничениях проверки - Эрик Дарлинг

Еще одна причина, по которой скалярные функции в вычисляемых столбцах - плохая идея - Эрик Дарлинг

Осторожно-ряд-ряд-операции-udf-одежда - Брайан Моран

Будьте осторожны с ограничениями, вызывающими UDFs - Tibor Karaszi

Почему План выполнения включает скалярный вызов udf для сохраняемого вычисляемого столбца? - Переполнение стека

Answer 3

Ты можешь попробовать WITH SCHEMABINDING в UDF.
Это означает, что базовые таблицы не могут быть изменены без удаления UDF (и вычисляемого столбца и т. Д.)

Без этого это точно помешает PERSISTED.

Вы понимаете, что такое использование UDF имеет огромное значение для производительности и параллелизма?

• Это курсор (для каждой строки сделайте агрегат один за другим)
• У вас странные параллельные поведения

После комментариев

CREATE VIEW dbo.SomeView
AS
SELECT
   ott.Col1, ott.Col2, ...,
   OutstandingBalance = ISNULL(ott.[Accepted Quantity],0) - ISNULL(SUM(dt.Quantity),0)
FROM
   dbo.[Order Transactions Table] ott
   LEFT JOIN
   dbo.[Delivery Table] dt ON ott.[Item Referance] = dt.[Item Referance]
GROUP BY
   ott.Col1, ott.Col2, ott.[Accepted Quantity], ...

Вы можете привязать схему к представлению, но не можете индексировать его с помощью LEFT JOIN