I have to agree with the above. In your article, you indicate that this:

SELECT orderid, custid, val,

CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust,

val - AVG(val) OVER(PARTITION BY custid) AS diffcust,

CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pctall,

val - AVG(val) OVER() AS diffall

FROM dbo.tb_OrderValues;

Is equivalent to this:

WITH CustAggregates AS

(

SELECT custid, SUM(val) AS sumval, AVG(val) AS avgval

FROM dbo.tb_OrderValues

GROUP BY custid

),

GrandAggregates AS

(

SELECT SUM(val) AS sumval, AVG(val) AS avgval

FROM dbo.tb_OrderValues

)

SELECT O.orderid, O.custid, O.val,

CAST(100. * O.val / CA.sumval AS NUMERIC(5, 2)) AS pctcust,

O.val - CA.avgval AS diffcust,

CAST(100. * O.val / GA.sumval AS NUMERIC(5, 2)) AS pctall,

O.val - GA.avgval AS diffall

FROM dbo.tb_OrderValues AS O

JOIN CustAggregates AS CA

ON O.custid = CA.custid

CROSS JOIN GrandAggregates AS GA;

Which from the perspective of output results is true. Now try the following 1,000,000 row test harness:

IF OBJECT_ID('tb_OrderValues') IS NOT NULL

DROP TABLE tb_OrderValues;

GO

CREATE TABLE tb_OrderValues(

orderid int,

custid int,

empid int,

shipperid int,

orderdate datetime,

requireddate datetime,

shippeddate datetime,

qty int,

val numeric(12, 2)

);

WITH Tally (n) AS

(

SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b

)

INSERT INTO tb_OrderValues

SELECT a.n, b.n, 100*a.n, 100*b.n

,CAST('2006-01-01' AS DATETIME)+a.n-1

,CAST('2006-01-01' AS DATETIME)+a.n+60

,CAST('2006-01-01' AS DATETIME)+a.n+30

,1+ABS(CHECKSUM(NEWID()))%100

,1+ABS(CHECKSUM(NEWID()))%1000

FROM Tally a

CROSS APPLY Tally b;

DECLARE

@orderid int,

@custid int,

@pctcust NUMERIC(5,2),

@diffcust numeric(12, 2),

@pctall NUMERIC(5,2),

@diffall numeric(12, 2),

@shippeddate datetime,

@qty int,

@val numeric(12, 2);

PRINT 'Pre-aggregate';

SET STATISTICS TIME ON;

WITH CustAggregates AS

(

SELECT custid, SUM(val) AS sumval, AVG(val) AS avgval

FROM dbo.tb_OrderValues

GROUP BY custid

),

GrandAggregates AS

(

SELECT SUM(val) AS sumval, AVG(val) AS avgval

FROM dbo.tb_OrderValues

)

SELECT @orderid=O.orderid, @custid=O.custid, @val=O.val,

@pctcust=CAST(100. * O.val / CA.sumval AS NUMERIC(5, 2)), -- AS pctcust,

@diffcust=O.val - CA.avgval, -- AS diffcust,

@pctall=CAST(100. * O.val / GA.sumval AS NUMERIC(5, 2)), -- AS pctall,

@diffall=O.val - GA.avgval -- AS diffall

FROM dbo.tb_OrderValues AS O

JOIN CustAggregates AS CA

ON O.custid = CA.custid

CROSS JOIN GrandAggregates AS GA;

SET STATISTICS TIME OFF;

PRINT 'Aggregate window functions';

SET STATISTICS TIME ON;

SELECT @orderid=orderid, @custid=custid, @val=val,

@pctcust=CAST(100. * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)), -- AS pctcust,

@diffcust=val - AVG(val) OVER(PARTITION BY custid), -- AS diffcust,

@pctall=CAST(100. * val / SUM(val) OVER() AS NUMERIC(5, 2)), -- AS pctall,

@diffall=val - AVG(val) OVER() --AS diffall

FROM dbo.tb_OrderValues;

SET STATISTICS TIME OFF;

GO

DROP TABLE tb_OrderValues;

The timing results produce the following:

(1000000 row(s) affected)

Pre-aggregate

SQL Server Execution Times:

CPU time = 4494 ms, elapsed time = 2507 ms.

Aggregate window functions

SQL Server Execution Times:

CPU time = 11716 ms, elapsed time = 8533 ms.

Which indicates the approach that pre-aggregates is more that 70% faster than the window aggregates.

I've generally found this to be the case.

You should write every query as if it will be executed 1,000,000 times per day on 1,000,000 rows of data.