SQL Server indexed calculated column that sums another table

Question

I'd like to effectively add a calculated column, which sums a column from selected rows in another table. I need to to quickly retrieve and search for values in the calculated column without re-computing the sum.

The calculated column I'd like to add would look like this in Dream-SQL:

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

Of course, this doesn't work. My understanding is that you can't add a calculated column that references another table. However, it appears that an indexed view can contain such a column.

The project is based on Entity Framework Code First. The application needs to quickly find non-zero balances.

Assuming an indexed view is the way to go, what is the best approach to integrating it with the Invoices and Transactions tables to make it easy use with LINQ to Entities? Should the indexed view contain all the columns in the Invoices table or just the Balance (what gets persisted)? A code snippet of the SQL to create the recommended view and index would be helpful.

I think this is what you want- a calculated field that calls a function:http://stackoverflow.com/a/8725738/203656 — nstenz, May 08 '14 at 15:24
@nstenz: I was able to create a computed column that reference a function like this: `CREATE FUNCTION SumInvoiceTransactions(@InvoiceId int) RETURNS decimal AS BEGIN RETURN (SELECT sum(Amount) FROM Transactions WHERE InvoiceId=@InvoiceId) END` followed by `ALTER TABLE Invoices ADD Balance as dbo.SumInvoiceTransactions(Id)`. The problem is adding the index fails: Column 'Balance' in table 'Invoices' cannot be used in an index or statistics or as a partition key because it is non-deterministic. A similar error occurs if I try to use `PERSISTED` when defining the column. — Edward Brey, May 10 '14 at 10:13

score 2 · Accepted Answer · answered May 18 '14 at 12:47

An indexed view won't work because it would only index expressions in the GROUP BY clause, which means it can't index the sum. A computed column won't work because the sum can't be persisted or indexed.

A trigger works, however:

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

It also helps to provide a default value of 0 for the Balance column since when you mark it as DatabaseGeneratedOption.Computed, EF won't provide any value for it when adding an Invoice row.

SQL Server indexed calculated column that sums another table

1 Answers1