Luis. Thanks for putting all this together; I wanted to reply soon but it's been a busy couple days.

I have been playing around with the code you posted and re-visited Jeff's article about cross-tabs and pivots. I never read it in it's entirety and never really played with pre-aggregated cross-tabs and pre-aggregated pivots. This was an extremely helpful and informative thread.

I always used the cross-tab approach when pivoting because it was easier to read/write. PIVOT is a real drag when you need to do multiple aggregations; I am now learning that through experience.

What I find interesting is that, on my system (08R2, 64bit, 16gb RAM) & when dealing with one aggregation, the cross-tab approach shows a faster estimated plan but PIVOT performs better. Using the tests below: at a million rows PIVOT generates a parallel plan and is like 4X faster. At 5million they both produce parallel plans and PIVOT is still twice as fast. I did not expect that.

-- (1) Sample data

IF OBJECT_ID('tempdb..#p') IS NOT NULL DROP TABLE #p;

CREATE TABLE #p

(id int primary key,

office int not null,

qtr int not null,

sales int not null);

GO

DECLARE @rows int = 2000000;

WITH iTally(n) AS

(SELECT TOP(@rows) ROW_NUMBER() OVER (ORDER BY (SELECT ($)))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT #p

SELECTn,

ceiling(60*rand(convert(varbinary, newid()))),

ceiling(4*rand(convert(varbinary, newid()))),

ceiling(80*rand(convert(varbinary, newid())))+20

FROM iTally;

GO

CREATE INDEX c_oqs ON #p(office) INCLUDE (qtr, sales);

CREATE INDEX c_qs ON #p(qtr) INCLUDE (sales);

GO

-- single aggregate: 1m rows=no paralellism for CROSS-TAB, but yes for piv

-- both get paralell plan at 5m rows

---------------------------------------------------------------------------

-- quarters only

SET NOCOUNT ON;

SET STATISTICS TIME ON;

SELECT

SUM(CASE qtr WHEN 1 THEN sales END) AS q1,

SUM(CASE qtr WHEN 2 THEN sales END) AS q2,

SUM(CASE qtr WHEN 3 THEN sales END) AS q3,

SUM(CASE qtr WHEN 4 THEN sales END) AS q4

FROM #p;

WITH p AS

(SELECT qtr, SUM(sales) sales

FROM #p

GROUP BY office, qtr)

SELECT

[1] AS q1,

[2] AS q2,

[3] AS q3,

[4] AS q4

FROM p

PIVOT

(SUM(sales)

FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;

SET STATISTICS TIME OFF;

GO

-- office & quarters only

SET STATISTICS TIME ON;

SELECT

office,

SUM(CASE qtr WHEN 1 THEN sales END) AS q1,

SUM(CASE qtr WHEN 2 THEN sales END) AS q2,

SUM(CASE qtr WHEN 3 THEN sales END) AS q3,

SUM(CASE qtr WHEN 4 THEN sales END) AS q4

FROM #p

GROUP BY office;

--piv

WITH p AS

(SELECT office, qtr, SUM(sales) sales

FROM #p

GROUP BY office, qtr)

SELECT

office,

[1] AS q1,

[2] AS q2,

[3] AS q3,

[4] AS q4

FROM p

PIVOT

(SUM(sales)

FOR qtr IN ([1],[2],[3],[4])) AS pivottbl;

SET STATISTICS TIME OFF;

GO

Results:

5M Rows (both create Parelell Plans)

crossTab

SQL Server Execution Times:

CPU time = 3120 ms, elapsed time = 783 ms.

pivot

SQL Server Execution Times:

CPU time = 1560 ms, elapsed time = 396 ms.

1M Rows (Only PIVOT creates a Parelell Plan)

crossTab

SQL Server Execution Times:

CPU time = 952 ms, elapsed time = 946 ms.

pivot

SQL Server Execution Times:

CPU time = 841 ms, elapsed time = 224 ms.

Comments questions welcome.

Thanks again Luis!