Ledger query using window functions

Question

This is a ledger kind of table, initial amount is when the movement is null and all transactions on it is based on the Waste_Seq

the query take the last entry (id) based on the movement and change the initial amount

this query seem to work for my scenario and seem fast, is there a better or alternative way of doing what the query does?

long term this table will have more than 5-10 millions rows easily, this is a new empty table for now

---

Schema and data:

CREATE TABLE [dbo].[fredou](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Movement] [int] NULL,
 [Waste_Seq] [int] NOT NULL,
 [Amount] [int] NOT NULL,
 [operation] [int] NOT NULL,
 CONSTRAINT [PK_fun_fred_test] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[fredou]
(
 [Waste_Seq] ASC,
 [Movement] ASC,
 [Id] DESC
)
INCLUDE ( [Amount],
 [operation]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
insert into fredou values ( null, 1, 1000, 1)
insert into fredou values ( 1, 1, 500, -1)
insert into fredou values ( 1, 1, 450, -1)
insert into fredou values ( 1, 1, 475, -1)
insert into fredou values ( 1, 1, 475, -1)
insert into fredou values ( 2, 1, 300, 1)
insert into fredou values ( 2, 1, 200, 1)
insert into fredou values ( 2, 1, 100, 1)
insert into fredou values ( 3, 1, 300, 0)
insert into fredou values ( 3, 1, 350, 0)
insert into fredou values ( 4, 1, 50, -1)
insert into fredou values ( 4, 1, 50, 0)
insert into fredou values ( null, 2, 5000, 1)
insert into fredou values ( 50, 2, 1000, -1)
insert into fredou values ( 50, 2, 1000, 0)
insert into fredou values ( 51, 2, 500, -1)
insert into fredou values ( 53, 2, 400, -1)
insert into fredou values ( 51, 2, 300, -1);

Query:

DECLARE @SEQ int = 1
SELECT SUM(f.Amount * f.Operation) as Amount
FROM 
(
 SELECT FIRST_VALUE(a.Amount) OVER (partition BY a.Movement order by a.id desc) as Amount,
 FIRST_VALUE(a.Operation) OVER (partition BY a.Movement order by a.id desc) as Operation,
 ROW_NUMBER() OVER (partition BY a.Movement order by a.id desc) as rownum
 FROM [dbo].[fredou] a
 WHERE a.Waste_Seq = @SEQ 
) f
where f.rownum = 1;

Query plan

Link to sql fiddle

• dnoeth: As @paparazzo said, it's the same PARTITION BY and the same ORDER BY for FIRST_VALUE and ROW_NUMBER. Thus no need for FIRST_VALUE and a simplified plan, too.

Answer 1

I doubt FIRST_VALUE is necessary since you have where f.rownum = 1;