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

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

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

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

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

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

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


sql-server azure-sql-database calculated-columns ef-code-first indexed-view
person Edward Brey    schedule 08.05.2014    source источник
comment
Я думаю, это то, что вам нужно - вычисляемое поле, которое вызывает функцию: stackoverflow.com/a/8725738/203656   -  person nstenz    schedule 08.05.2014
comment
@nstenz: мне удалось создать вычисляемый столбец, который ссылается на такую ​​функцию: CREATE FUNCTION SumInvoiceTransactions(@InvoiceId int) RETURNS decimal AS BEGIN RETURN (SELECT sum(Amount) FROM Transactions WHERE InvoiceId=@InvoiceId) END, за которой следует ALTER TABLE Invoices ADD Balance as dbo.SumInvoiceTransactions(Id). Проблема заключается в том, что при добавлении индекса происходит сбой: столбец «Баланс» в таблице «Счета-фактуры» нельзя использовать в индексе или статистике или в качестве ключа раздела, поскольку он недетерминирован. Аналогичная ошибка возникает, если я пытаюсь использовать PERSISTED при определении столбца.   -  person Edward Brey    schedule 10.05.2014

Ответы (1)


arrow_upward
2
arrow_downward

Индексированное представление не будет работать, потому что оно будет индексировать только выражения в предложении 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 не предоставит для него никакого значения при добавлении строки счета-фактуры.

person Edward Brey    schedule 18.05.2014