T-SQL Window Functions

“Window” sounds a bit like the singular of Microsoft’s Operating System, huh?

But no, imagine that each cell in a spreadsheet has two little glass “Windows”, one in the ceiling of its cell and one in the floor.

Then the occupant of cell C3 could look up at C2 and wave, or down at C4 and blow a raspberry.

But there’s more, C3 can now look up and down past C2 and C4 at ALL the values in the C column.

Now instead of cells in a spreadsheet imagine cells in a database table.

create table #t1 (c int)
insert into #t1 values (10), (20), (30), (40)

select * from #t1

select *,
    lag(c, 1) over(order by c) [Waving up],
    lead(c, 1) over(order by c) [Rasberrying down],
    SUM(c) OVER() [Sum of c]
from #t1

drop table #t1