February 2, 2012 at 2:12 am
CREATE TABLE Table1 (ID1 int, ID2 int,ID3 int)
insert into Table1(ID1,ID2,ID3)
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9
How can I display just the number 9 as the highest value from all the three columns ?
February 2, 2012 at 2:28 am
This was removed by the editor as SPAM
February 2, 2012 at 2:35 am
Thank You so much. This is neat. I was going bonkers with #temp tables. Though it served the purpose, its not as efficient as this one is.
February 2, 2012 at 2:35 am
"diLip" (2/2/2012)
CREATE TABLE Table1 (ID1 int, ID2 int,ID3 int)insert into Table1(ID1,ID2,ID3)
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9
How can I display just the number 9 as the highest value from all the three columns ?
BEGIN TRAN
--Your sample data
CREATE TABLE Table1 (ID1 int, ID2 int,ID3 int)
INSERT INTO Table1 (ID1, ID2, ID3)
SELECT 1, 2, 3
UNION ALL
SELECT 4, 5, 6
UNION ALL
SELECT 7, 8, 9
--Actual query
SELECT MAX([data])
FROM (SELECT ID1, ID2, ID3
FROM Table1) innerQuery
UNPIVOT ([data] FOR [column] IN (ID1, ID2, ID3))AS pvt
ROLLBACK
Result: -
-----------
9
Comparison of the IO of UNPIVOT and the CTE
--==UNPIVOT METHOD
Table 'Table1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--==CTE
Table 'Table1'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
February 2, 2012 at 2:40 am
This was removed by the editor as SPAM
February 2, 2012 at 7:37 am
Stewart "Arturius" Campbell (2/2/2012)
Could kick myself, forgot about UNPIVOT, thanks Cadavre
In fairness, there's not much difference in execution times.
BEGIN TRAN
SET NOCOUNT ON
--1,000,000 Random rows of data
SELECT TOP 1000000 ABS(CHECKSUM(NEWID())) AS ID1, ABS(CHECKSUM(NEWID())) AS ID2, ABS(CHECKSUM(NEWID())) AS ID3
INTO Table1
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--1,000,000 Random rows of data
SELECT TOP 1000000 ABS(CHECKSUM(NEWID())) AS ID1, ABS(CHECKSUM(NEWID())) AS ID2, ABS(CHECKSUM(NEWID())) AS ID3
INTO Table2
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
DECLARE @EXECUTIONS INT = 0
WHILE @EXECUTIONS <= 10
BEGIN
PRINT 'UNPIVOT'
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT MAX([data])
FROM (SELECT ID1, ID2, ID3
FROM Table1) innerQuery
UNPIVOT([data] FOR [column] IN (ID1, ID2, ID3)) AS pvt;
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('-',80)
PRINT 'CTE'
SET STATISTICS IO ON
SET STATISTICS TIME ON;
WITH GetIntoSingleColumn (ID)
AS (SELECT ID1
FROM Table2
UNION ALL
SELECT ID2
FROM Table2
UNION ALL
SELECT ID3
FROM Table2)
SELECT MAX(ID)
FROM GetIntoSingleColumn
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET @EXECUTIONS = @EXECUTIONS + 1
END
ROLLBACK
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 874 ms, elapsed time = 243 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 702 ms, elapsed time = 195 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 889 ms, elapsed time = 241 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 188 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 904 ms, elapsed time = 230 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 293 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 920 ms, elapsed time = 250 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 191 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 872 ms, elapsed time = 229 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 748 ms, elapsed time = 187 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 907 ms, elapsed time = 240 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 654 ms, elapsed time = 261 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 905 ms, elapsed time = 279 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 686 ms, elapsed time = 222 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 844 ms, elapsed time = 227 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 748 ms, elapsed time = 198 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 873 ms, elapsed time = 282 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 278 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 826 ms, elapsed time = 226 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 734 ms, elapsed time = 186 ms.
UNPIVOT
-----------
2147483508
Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 874 ms, elapsed time = 228 ms.
--------------------------------------------------------------------------------
CTE
-----------
2147483637
Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 266 ms.
Unpivot
CPU - Average: 884.3 - MAX: 920 - MIN: 826
ELAPSED - Average: 243.2 - MAX: 282 - MIN: 226
IO - Table 'Table1'. Scan count 5, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CTE
CPU - Average: 713.4 - MAX: 748 - MIN: 654
ELAPSED - Average: 224.0 - MAX: 293 - MIN: 186
IO - Table 'Table2'. Scan count 15, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
February 2, 2012 at 1:51 pm
How about this?
; With EachRowMaxCol AS
(
SELECT CASE WHEN ID1 >= ID2 AND ID1 >= ID3 THEN ID1
WHEN ID2 >= ID1 AND ID2 >= ID3 THEN ID2
ELSE ID3
END As MaxinTheRow
FROM Table1
)
SELECT MAX( MaxinTheRow ) AS MaxAcrossAllColsAndRows
FROM EachRowMaxCol
And the test rig:
BEGIN TRAN
SET NOCOUNT ON
--1,000,000 Random rows of data
SELECT TOP 1000000 ABS(CHECKSUM(NEWID())) AS ID1, ABS(CHECKSUM(NEWID())) AS ID2, ABS(CHECKSUM(NEWID())) AS ID3
INTO Table1
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
----1,000,000 Random rows of data
--SELECT TOP 1000000 ABS(CHECKSUM(NEWID())) AS ID1, ABS(CHECKSUM(NEWID())) AS ID2, ABS(CHECKSUM(NEWID())) AS ID3
--INTO Table2
--FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
DECLARE @EXECUTIONS INT = 0 , @MaxVal INT
WHILE @EXECUTIONS <= 10
BEGIN
PRINT REPLICATE('*',80)
PRINT 'Test PASS' + CAST (@EXECUTIONS as varchar) + Char(10)
PRINT 'UNPIVOT'
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT @MaxVal = MAX([data])
FROM (SELECT ID1, ID2, ID3
FROM Table1) innerQuery
UNPIVOT([data] FOR [column] IN (ID1, ID2, ID3)) AS pvt;
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT 'Cadavre UNPIVOT Result: '+ CAST (@MaxVal AS VARCHAR(19));
PRINT REPLICATE('-',80)
PRINT 'CTE'
SET STATISTICS IO ON
SET STATISTICS TIME ON;
WITH GetIntoSingleColumn (ID)
AS (SELECT ID1
FROM Table1
UNION ALL
SELECT ID2
FROM Table1
UNION ALL
SELECT ID3
FROM Table1)
SELECT @MaxVal = MAX(ID)
FROM GetIntoSingleColumn
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT 'Stewart CTE Result: '+ CAST (@MaxVal AS VARCHAR(19));
PRINT REPLICATE('-',80)
PRINT 'ColdCoffe CTE'
SET STATISTICS IO,TIME ON;
; With EachRowMaxCol AS
(
SELECT CASE WHEN ID1 >= ID2 AND ID1 >= ID3 THEN ID1
WHEN ID2 >= ID1 AND ID2 >= ID3 THEN ID2
ELSE ID3
END As MaxinTheRow
FROM Table1
)
SELECT @MaxVal = MAX( MaxinTheRow )
FROM EachRowMaxCol
SET STATISTICS IO,TIME OFF;
PRINT 'ColdCoffee CTE Result: '+ CAST (@MaxVal AS VARCHAR(19));
SET @EXECUTIONS = @EXECUTIONS + 1
END
ROLLBACK
Test Results
********************************************************************************
Test PASS0
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 796 ms, elapsed time = 430 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 386 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 416 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS1
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 406 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 387 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 417 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS2
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 796 ms, elapsed time = 404 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 716 ms, elapsed time = 385 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 414 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS3
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 796 ms, elapsed time = 404 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 380 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 420 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS4
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 779 ms, elapsed time = 423 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 765 ms, elapsed time = 390 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 405 ms, elapsed time = 419 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS5
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 414 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 384 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 422 ms, elapsed time = 439 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS6
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 415 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 749 ms, elapsed time = 388 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 422 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS7
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 827 ms, elapsed time = 429 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 639 ms, elapsed time = 449 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 436 ms, elapsed time = 431 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS8
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 765 ms, elapsed time = 435 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 748 ms, elapsed time = 398 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 462 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS9
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 796 ms, elapsed time = 407 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 384 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 422 ms.
ColdCoffee CTE Result: 2147483044
********************************************************************************
Test PASS10
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 412 ms.
Cadavre UNPIVOT Result: 2147483044
--------------------------------------------------------------------------------
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 748 ms, elapsed time = 384 ms.
Stewart CTE Result: 2147483044
--------------------------------------------------------------------------------
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 415 ms.
ColdCoffee CTE Result: 2147483044
Time statistics
cpuelapsed
cadavrestewartCCcadavrestewartCC
pass0796717406430386416
pass1781733421406387417
pass2796716421404385414
pass3796718380404375420
pass4779765405423390419
MAX796765421430390420
Min779716380404375414
AVG789.6729.8406.6413.4384.6417.2
IO statistics
UNPIVOT
Table 'Table1'. Scan count 3, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CTE
Table 'Table1'. Scan count 9, logical reads 7797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
ColdCoffe CTE
Table 'Table1'. Scan count 1, logical reads 2599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply