sql - TSQL OVER clause: COUNT(*) OVER (ORDER BY a) -

sql - TSQL OVER clause: COUNT(*) OVER (ORDER BY a) -

this code:

use [tempdb]; go if object_id(n'dbo.t') not null begin drop table dbo.t end go create table dbo.t ( nvarchar(8), b nvarchar(8) ); go insert t values ('a', 'b'); insert t values ('a', 'b'); insert t values ('a', 'b'); insert t values ('c', 'd'); insert t values ('c', 'd'); insert t values ('c', 'd'); insert t values ('c', 'd'); insert t values ('e', null); insert t values (null, null); insert t values (null, null); insert t values (null, null); insert t values (null, null); go select a, b, count(*) on (order a) t;

on this page of bol, microsoft says that:

if partition not specified, function treats rows of query result set single group.

so based on understanding, lastly select statement give me next result. since records considered in 1 single group, right?

a b -------- -------- ----------- null null 12 null null 12 null null 12 null null 12 b 12 b 12 b 12 c d 12 c d 12 c d 12 c d 12 e null 12

but actual result is:

a b -------- -------- ----------- null null 4 null null 4 null null 4 null null 4 b 7 b 7 b 7 c d 11 c d 11 c d 11 c d 11 e null 12

anyone can help explain why? thanks.

it gives running total (this functionality not implemented in sql server until version 2012.)

the order by defines window aggregated unbounded preceding , current row default when not specified. sql server defaults less performing range alternative rather rows.

they have different semantics in case of ties in window range version includes not current row (and preceding rows) additional tied rows same value of a current row. can seen in number of rows counted each in results below.

select a, b, count(*) on (order rows between unbounded preceding , current row) [rows], count(*) on (order range between unbounded preceding , current row) [range], count(*) over() [over()] t;

returns

a b rows range over() -------- -------- ----------- ----------- ----------- null null 1 4 12 null null 2 4 12 null null 3 4 12 null null 4 4 12 b 5 7 12 b 6 7 12 b 7 7 12 c d 8 11 12 c d 9 11 12 c d 10 11 12 c d 11 11 12 e null 12 12 12

to accomplish result expecting omit both partition by , order by , utilize empty over() clause (also shown above).

sql sql-server tsql sql-server-2012 window-functions