Индексированный вычисляемый столбец SQL Server, который суммирует другую таблицу

Question

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

Вычисляемый столбец, который я хотел бы добавить, будет выглядеть в Dream-SQL следующим образом:

ALTER TABLE Invoices ADD Balance
AS SUM(Transactions.Amount) WHERE Transactions.InvoiceId = Invoices.Id

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

Проект основан на Entity Framework Code First. Приложение должно быстро найти ненулевые балансы.

Предполагая, что индексированное представление - это путь, как лучше интегрировать его с таблицами счетов-фактур и транзакций, чтобы его было легко использовать с LINQ to Entities? Должно ли индексированное представление содержать все столбцы в таблице счетов-фактур или только баланс (что сохраняется)? Будет полезен фрагмент кода SQL для создания рекомендуемого представления и индекса.

Answer 1

Индексированное представление не будет работать, потому что оно будет индексировать только выражения в предложении GROUP BY, что означает, что оно не может индексировать сумму. Вычисляемый столбец не будет работать, потому что сумма не может быть сохранена или проиндексирована.

Триггер работает, однако:

CREATE TRIGGER UpdateInvoiceBalance ON Transactions AFTER INSERT, UPDATE AS
    IF UPDATE(Amount) BEGIN
        SET NOCOUNT ON;
        WITH InvoiceBalances AS (
            SELECT Transactions.InvoiceId, SUM(Transactions.Amount) AS Balance
            FROM Transactions
            JOIN inserted ON Transactions.InvoiceId = inserted.InvoiceId
            GROUP BY Transactions.InvoiceId)
        UPDATE Invoices
            SET Balance = InvoiceBalances.Balance
            FROM InvoiceBalances
            WHERE Invoices.Id = InvoiceBalances.InvoiceId
    END

Это также помогает обеспечить значение по умолчанию 0 для Balance столбец с тех пор, когда вы пометите его как DatabaseGeneratedOption.Computed, EF не предоставит никакого значения при добавлении строки Invoice.