Cross Tab vs Pivot - Simple, 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 400000 --<<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_CoverYear 
    ON dbo.#SomeTable3 (Year)
       INCLUDE (Amount, Quantity, Quarter) 
       
create statistics syear on #sometable3(year) with fullscan, norecompute;
create statistics syearquarter on #sometable3(year,quarter) with fullscan, norecompute;
GO
insert into #timer values ('Finished Loading Test Data',getdate());
go
---------------------------------------------------------------------------------------------------
--===== Simple Pivot
 SELECT Year, 
        COALESCE([1],0) AS [1st Qtr],
        COALESCE([2],0) AS [2nd Qtr],
        COALESCE([3],0) AS [3rd Qtr],
        COALESCE([4],0) AS [4th Qtr],
        COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
   into #SimplePivot_prep
   FROM (SELECT Year, Quarter,Amount FROM #SomeTable3)  AS src 
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt 
go
--insert into #timer values ('Simple Pivot',getdate());
go
---------------------------------------------------------------------------------------------------
--===== Simple Cross Tab
 SELECT Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
        SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
        SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
        SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
        SUM(Amount) AS Total
   into #simpleCrossTab_prep
   FROM #SomeTable3
  GROUP BY Year
go
--insert into #timer values ('Simple Cross Tab',getdate());
go
---------------------------------------------------------------------------------------------------
insert into #timer values ('Finished Prep',getdate());
go
---------------------------------------------------------------------------------------------------
--===== Simple Pivot
 SELECT Year, 
        COALESCE([1],0) AS [1st Qtr],
        COALESCE([2],0) AS [2nd Qtr],
        COALESCE([3],0) AS [3rd Qtr],
        COALESCE([4],0) AS [4th Qtr],
        COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
   into #SimplePivot
   FROM (SELECT Year, Quarter,Amount FROM #SomeTable3)  AS src 
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt 
go
insert into #timer values ('Simple Pivot',getdate());
go
---------------------------------------------------------------------------------------------------
--===== Simple Cross Tab
 SELECT Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
        SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
        SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
        SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
        SUM(Amount) AS Total
   into #simpleCrossTab
   FROM #SomeTable3
  GROUP BY Year
go
insert into #timer values ('Simple 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: 8,66 sec, rows selected: 5, rows affected: 400005, absolute service time: 9,61 sec