Cross Tab vs Pivot - Normal, Sql Server

Language: Layout:

create table #timer (what varchar(64), ended datetime);
insert into #timer values ('Start',getdate());
go
--===== Create and populate a 1,000,000 row test table.
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "Company" has a range of "AAA" to "BBB" non-unique 3 character strings
     -- Column "Amount has a range of 0.0000 to 9999.9900 non-unique numbers
     -- Column "Quantity" has a range of 1 to 50,000 non-unique numbers
     -- Column "Date" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Columns Year and Quarter are the similarly named components of Date
     -- Jeff Moden

SELECT TOP 300000 --<<Look!  Change this number for testing different size tables
        RowNum       = IDENTITY(INT,1,1),
        Company      = CHAR(ABS(CHECKSUM(NEWID()))%2+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%2+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%2+65),
        Amount       = CAST(ABS(CHECKSUM(NEWID()))%1000000/100.0 AS MONEY),
        Quantity     = ABS(CHECKSUM(NEWID()))%50000+1,
        Date         = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        Year         = CAST(NULL AS SMALLINT),
        Quarter      = CAST(NULL AS TINYINT)
   INTO #SomeTable3
   FROM Master.sys.SysColumns t1
  CROSS JOIN
        Master.sys.SysColumns t2

--===== Fill in the Year and Quarter columns from the Date column
 UPDATE #SomeTable3
    SET Year    = DATEPART(yy,Date),
        Quarter = DATEPART(qq,Date)

--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE #SomeTable3
        ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_#SomeTable3_Cover1 
    ON dbo.#SomeTable3 (Company, Year)
       INCLUDE (Amount, Quantity, Quarter)

create statistics scompanyyear on #sometable3(company, year) with fullscan, norecompute;
GO
insert into #timer values ('Finished Loading Test Data',getdate());
go
---------------------------------------------------------------------------------------------------
--===== "Normal" Pivot
 SELECT amt.Company,
        amt.Year,
        COALESCE(amt.[1],0) AS Q1Amt,
        COALESCE(qty.[1],0) AS Q1Qty,
        COALESCE(amt.[2],0) AS Q2Amt,
        COALESCE(qty.[2],0) AS Q2Qty,
        COALESCE(amt.[3],0) AS Q3Amt,
        COALESCE(qty.[3],0) AS Q3Qty,
        COALESCE(amt.[4],0) AS Q4Amt,
        COALESCE(qty.[4],0) AS Q5Qty,
        COALESCE(amt.[1],0)+COALESCE(amt.[2],0)+COALESCE(amt.[3],0)+COALESCE(amt.[4],0) AS TotalAmt,
        COALESCE(qty.[1],0)+COALESCE(qty.[2],0)+COALESCE(qty.[3],0)+COALESCE(qty.[4],0) AS TotalQty
   into #NormalPivot_prep
   FROM (SELECT Company, Year, Quarter, Amount FROM #SomeTable3) t1
        PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
  INNER JOIN 
        (SELECT Company, Year, Quarter, Quantity FROM #SomeTable3) t2
        PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
     ON qty.Company = amt.Company 
    AND qty.Year    = amt.Year         
  ORDER BY amt.Company, amt.Year
go
--insert into #timer values ('Finished Normal Pivot',getdate());
go
---------------------------------------------------------------------------------------------------
--===== "Normal" Cross Tab
 SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   into #NormalCrossTab_prep
   FROM #SomeTable3
  GROUP BY Company, Year
  ORDER BY Company, Year
go
--insert into #timer values ('Finished Normal Cross Tab',getdate());
insert into #timer values ('Finished Prep',getdate());
go
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--===== "Normal" Pivot
 SELECT amt.Company,
        amt.Year,
        COALESCE(amt.[1],0) AS Q1Amt,
        COALESCE(qty.[1],0) AS Q1Qty,
        COALESCE(amt.[2],0) AS Q2Amt,
        COALESCE(qty.[2],0) AS Q2Qty,
        COALESCE(amt.[3],0) AS Q3Amt,
        COALESCE(qty.[3],0) AS Q3Qty,
        COALESCE(amt.[4],0) AS Q4Amt,
        COALESCE(qty.[4],0) AS Q5Qty,
        COALESCE(amt.[1],0)+COALESCE(amt.[2],0)+COALESCE(amt.[3],0)+COALESCE(amt.[4],0) AS TotalAmt,
        COALESCE(qty.[1],0)+COALESCE(qty.[2],0)+COALESCE(qty.[3],0)+COALESCE(qty.[4],0) AS TotalQty
   into #NormalPivot
   FROM (SELECT Company, Year, Quarter, Amount FROM #SomeTable3) t1
        PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
  INNER JOIN 
        (SELECT Company, Year, Quarter, Quantity FROM #SomeTable3) t2
        PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
     ON qty.Company = amt.Company 
    AND qty.Year    = amt.Year         
  ORDER BY amt.Company, amt.Year
go
insert into #timer values ('Finished Normal Pivot',getdate());
go
---------------------------------------------------------------------------------------------------
--===== "Normal" Cross Tab
 SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   into #NormalCrossTab
   FROM #SomeTable3
  GROUP BY Company, Year
  ORDER BY Company, Year
go
insert into #timer values ('Finished Normal Cross Tab',getdate());
go
---------------------------------------------------------------------------------------------------
select 
    o.what
  , started=isnull(convert(varchar(30),x.ended),o.ended)
  , ended=convert(varchar(30),o.ended)
  , DurationInMs=datediff(millisecond,x.ended,o.ended)
from #timer o
  outer apply (select top 1 ended from #timer i where i.ended < o.ended order by i.ended desc) as x

View schema
Execution time: 7,32 sec, rows selected: 5, rows affected: 300005, absolute service time: 8,02 sec