SUM OVER PARTITION BY in VIEW is NULL but works when used directly

user_159145 0
Viewed 865

In a SQL Server 2016 database I have a table containing financial transactions:

CREATE TABLE Transactions
    TxnId     int      NOT NULL IDENTITY(1,1) PRIMARY KEY,
    AccountId int      NOT NULL,
    DateTime  datetime NOT NULL,
    Amount    money    NOT NULL
)

I wrote a VIEW to display running balance information:

CREATE VIEW TransactionsWithBalance AS

SELECT
    *,
    SUM( Amount ) OVER ( PARTITION BY AccountId ORDER BY [DateTime], TxnId ) AS Balance
FROM
    Transactions

When I query this view, the Balance column contains all NULL values:

SELECT
    *
FROM
    TransactionsWithBalance

TxnId    AccountId    DateTime      Amount    Balance
    1            1    2017-01-01    100.00    NULL
    2            1    2017-01-02    200.00    NULL
    3            2    2017-01-01     10.00    NULL
    4            1    2017-01-03    300.00    NULL

But when I run the query (with the SUM( Amount ) OVER ( PARTITION BY... )) query directly in SSMS I get the expected values in the Balance column.

TxnId    AccountId    DateTime      Amount    Balance
    1            1    2017-01-01    100.00     100.00
    2            1    2017-01-02    200.00     300.00
    3            2    2017-01-01     10.00      10.00
    4            1    2017-01-03    300.00     600.00

Why is the aggregate returning NULL values when queried inside a VIEW?

sql-server
1 Answers

This is one of the things that can happen when metadata gets out of sync because you didn't either:

  • Schema bind the view; or

  • Call sp_refreshview whenever an object underlying the view changes

    Remarks

    If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

The better option would probably be to use schema binding. This will also force you to not use SELECT * in the view, and use a two-part name for the table, both best practices:

CREATE VIEW dbo.TransactionsWithBalance
WITH SCHEMABINDING
AS
SELECT
    TRN.TxnId, 
    TRN.AccountId,
    TRN.[DateTime],
    TRN.Amount,
    Balance =
        SUM(TRN.Amount) OVER (
            PARTITION BY TRN.AccountId 
            ORDER BY TRN.[DateTime], TRN.TxnId)
FROM
    dbo.Transactions AS TRN;
Admin1
Related