SUM OVER PARTITION BY VIEW - NULL, but works when used directly

Question

In the 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 VIEWto display information about the current balance:

CREATE VIEW TransactionsWithBalance AS

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

When I request this view, the column Balancecontains all the values NULL:

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 tag SUM( Amount ) OVER ( PARTITION BY... )) directly in SSMS, I get the expected values ​​in the column Balance.

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 does aggregation return values NULLwhen querying internally VIEW?

Answer 1

This is one of those things that can happen when metadata goes out of sync because you didn't do it either:

• ;

• sp_refreshview , , ,

  , sp_refreshview , , . .

, , . SELECT * , :

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;