March 6, 2010 at 9:19 am
steve-893342 (3/6/2010)
Trillion Row Base Table - Pushing Multiple Unpivots to the LimitSo what do you think folks? Will this one go fast?
Sorry Jeff, no cross joins!
WITH cte1 AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row FROM
(
SELECT 1 AS A1,1 AS A2,1 AS A3,1 AS A4,1 AS A5,1 AS A6,1 AS A7,1 AS A8,1 AS A9,1 AS A10,
1 AS A11,1 AS A12,1 AS A13,1 AS A14,1 AS A15,1 AS A16,1 AS A17,1 AS A18,1 AS A19,1 AS A20,
1 AS A21,1 AS A22,1 AS A23,1 AS A24,1 AS A25,1 AS A26,1 AS A27,1 AS A28,1 AS A29,1 AS A30,
1 AS A31,1 AS A32,1 AS B1,1 AS B2,1 AS B3,1 AS B4,1 AS B5,1 AS B6,1 AS B7,1 AS B8,1 AS B9,
1 AS B10,1 AS B11,1 AS B12,1 AS B13,1 AS B14,1 AS B15,1 AS B16,1 AS B17,1 AS B18,1 AS B19,
1 AS B20,1 AS B21,1 AS B22,1 AS B23,1 AS B24,1 AS B25,1 AS B26,1 AS B27,1 AS B28,1 AS B29,
1 AS B30,1 AS B31,1 AS B32,1 AS C1,1 AS C2,1 AS C3,1 AS C4,1 AS C5,1 AS C6,1 AS C7,1 AS C8,
1 AS C9,1 AS C10,1 AS C11,1 AS C12,1 AS C13,1 AS C14,1 AS C15,1 AS C16,1 AS C17,1 AS C18,
1 AS C19,1 AS C20,1 AS C21,1 AS C22,1 AS C23,1 AS C24,1 AS C25,1 AS C26,1 AS C27,1 AS C28,
1 AS C29,1 AS C30,1 AS C31,1 AS C32,1 AS D1,1 AS D2,1 AS D3,1 AS D4,1 AS D5,1 AS D6,1 AS D7,
1 AS D8,1 AS D9,1 AS D10,1 AS D11,1 AS D12,1 AS D13,1 AS D14,1 AS D15,1 AS D16,1 AS D17,
1 AS D18,1 AS D19,1 AS D20,1 AS D21,1 AS D22,1 AS D23,1 AS D24,1 AS D25,1 AS D26,1 AS D27,
1 AS D28,1 AS D29,1 AS D30,1 AS D31,1 AS D32,1 AS E1,1 AS E2,1 AS E3,1 AS E4,1 AS E5,1 AS E6,
1 AS E7,1 AS E8,1 AS E9,1 AS E10,1 AS E11,1 AS E12,1 AS E13,1 AS E14,1 AS E15,1 AS E16,1 AS E17,
1 AS E18,1 AS E19,1 AS E20,1 AS E21,1 AS E22,1 AS E23,1 AS E24,1 AS E25,1 AS E26,1 AS E27,
1 AS E28,1 AS E29,1 AS E30,1 AS E31,1 AS E32,1 AS F1,1 AS F2,1 AS F3,1 AS F4,1 AS F5,1 AS F6,
1 AS F7,1 AS F8,1 AS F9,1 AS F10,1 AS F11,1 AS F12,1 AS F13,1 AS F14,1 AS F15,1 AS F16,1 AS F17,
1 AS F18,1 AS F19,1 AS F20,1 AS F21,1 AS F22,1 AS F23,1 AS F24,1 AS F25,1 AS F26,1 AS F27,
1 AS F28,1 AS F29,1 AS F30,1 AS F31,1 AS F32,1 AS G1,1 AS G2,1 AS G3,1 AS G4,1 AS G5,1 AS G6,
1 AS G7,1 AS G8,1 AS G9,1 AS G10,1 AS G11,1 AS G12,1 AS G13,1 AS G14,1 AS G15,1 AS G16,1 AS G17,
1 AS G18,1 AS G19,1 AS G20,1 AS G21,1 AS G22,1 AS G23,1 AS G24,1 AS G25,1 AS G26,1 AS G27,
1 AS G28,1 AS G29,1 AS G30,1 AS G31,1 AS G32,1 AS H1,1 AS H2,1 AS H3,1 AS H4,1 AS H5,1 AS H6,
1 AS H7,1 AS H8,1 AS H9,1 AS H10,1 AS H11,1 AS H12,1 AS H13,1 AS H14,1 AS H15,1 AS H16,1 AS H17,
1 AS H18,1 AS H19,1 AS H20,1 AS H21,1 AS H22,1 AS H23,1 AS H24,1 AS H25,1 AS H26,1 AS H27,
1 AS H28,1 AS H29,1 AS H30,1 AS H31,1 AS H32
) AS Z
UNPIVOT (A FOR O IN (A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,
A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32)) AS Z
UNPIVOT (B FOR P IN (B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,
B17,B18,B19,B20,B21,B22,B23,B24,B25,B26,B27,B28,B29,B30,B31,B32)) AS Z
UNPIVOT (C FOR Q IN (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,
C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32)) AS Z
UNPIVOT (D FOR R IN (D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,
D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31,D32)) AS Z
UNPIVOT (E FOR S IN (E1,E2,E3,E4,E5,E6,E7,E8,E9,E10,E11,E12,E13,E14,E15,E16,
E17,E18,E19,E20,E21,E22,E23,E24,E25,E26,E27,E28,E29,E30,E31,E32)) AS Z
UNPIVOT (F FOR T IN (F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,
F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30,F31,F32)) AS Z
UNPIVOT (G FOR U IN (G1,G2,G3,G4,G5,G6,G7,G8,G9,G10,G11,G12,G13,G14,G15,G16,
G17,G18,G19,G20,G21,G22,G23,G24,G25,G26,G27,G28,G29,G30,G31,G32)) AS Z
UNPIVOT (H FOR V IN (H1,H2,H3,H4,H5,H6,H7,H8,H9,H10,H11,H12,H13,H14,H15,H16,
H17,H18,H19,H20,H21,H22,H23,H24,H25,H26,H27,H28,H29,H30,H31,H32)) AS Z
) --1,099,511,627,776 rows
SELECT COUNT_BIG(ALL CASE
WHEN row%15 = 0 THEN 'FIZZBUZZ'
WHEN row%5 = 0 THEN 'BUZZ'
WHEN row%3 = 0 THEN 'FIZZ'
ELSE CAST(row AS VARCHAR(14))
END)
FROM cte1 WHERE row < 100000000001 --set limit to 100 billion
Heh... nice. Probably a bit difficult to memorize for an interview, though. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2010 at 9:40 am
Yeh, but it's quite symmetrical, albeit a little taller than the CTE version of the table join!
March 6, 2010 at 11:08 pm
Jeff Moden (3/6/2010)
Heh... nice. Probably a bit difficult to memorize for an interview, though. 😛
That is part of the beauty of this query.
😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2010 at 4:02 am
Multiple Unpivot Variation Using 16 Column Block and 5 Unpivots
I think this is just about my preferred refinement of the multiple unpivot base table approach just now.
It seems to edge out my other versions although it is very marginal. This particular variation uses a 16 column block and 5 unpivots. There is a trade off between not making the column block too long, but at the same time minimizing the number of unpivots. The ethos behind this query is to create a base table with 1 million rows using a single SELECT statement. Then via a single cross join, 1 trillion rows are released.
At the 100 billion limit, this query ran in 6 hours 10 minutes which is my fastest so far. Although under the same conditions, there is only a hair's breadth between this query, the 10 column block/6 unpivots alternative and the CTE version of the table join.
WITH cte1 AS
(
SELECT A FROM
(
SELECT 1 AS A1,1 AS A2,1 AS A3,1 AS A4,1 AS A5,1 AS A6,1 AS A7,1 AS A8,1 AS A9,1 AS A10,1 AS A11,1 AS A12,1 AS A13,1 AS A14,1 AS A15,1 AS A16,
1 AS B1,1 AS B2,1 AS B3,1 AS B4,1 AS B5,1 AS B6,1 AS B7,1 AS B8,1 AS B9,1 AS B10,1 AS B11,1 AS B12,1 AS B13,1 AS B14,1 AS B15,1 AS B16,
1 AS C1,1 AS C2,1 AS C3,1 AS C4,1 AS C5,1 AS C6,1 AS C7,1 AS C8,1 AS C9,1 AS C10,1 AS C11,1 AS C12,1 AS C13,1 AS C14,1 AS C15,1 AS C16,
1 AS D1,1 AS D2,1 AS D3,1 AS D4,1 AS D5,1 AS D6,1 AS D7,1 AS D8,1 AS D9,1 AS D10,1 AS D11,1 AS D12,1 AS D13,1 AS D14,1 AS D15,1 AS D16,
1 AS E1,1 AS E2,1 AS E3,1 AS E4,1 AS E5,1 AS E6,1 AS E7,1 AS E8,1 AS E9,1 AS E10,1 AS E11,1 AS E12,1 AS E13,1 AS E14,1 AS E15,1 AS E16
) AS Z
UNPIVOT (A FOR O IN (A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16)) AS Z
UNPIVOT (B FOR P IN (B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16)) AS Z
UNPIVOT (C FOR Q IN (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16)) AS Z
UNPIVOT (D FOR R IN (D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16)) AS Z
UNPIVOT (E FOR S IN (E1,E2,E3,E4,E5,E6,E7,E8,E9,E10,E11,E12,E13,E14,E15,E16)) AS Z
) --1,048,576 rows
,
cte2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row FROM cte1
CROSS JOIN cte1 AS T2
) --1,099,511,627,776 rows
SELECT COUNT_BIG(ALL CASE
WHEN row%15 = 0 THEN 'FIZZBUZZ'
WHEN row%5 = 0 THEN 'BUZZ'
WHEN row%3 = 0 THEN 'FIZZ'
ELSE CAST(row AS VARCHAR(14))
END)
FROM cte2 WHERE row < 100000000001 --set limit to 100 billion
March 7, 2010 at 2:02 pm
Reducing the Strain on Multiple Unpivots - who says you shouldn't read from a physical table?
This variation is very similar to the above. The difference is that this query only uses 2 unpivots (instead of 5), with the slack being taken up by the physical table RepeatBlock. The RepeatBlock table holds 4096 rows which is 16^3, and so this query has exactly the same threshold as the above.
So do you think this will go fast?
CREATE TABLE master.dbo.RepeatBlock
(
A1 tinyint not null default 1,
A2 tinyint not null default 1,
A3 tinyint not null default 1,
A4 tinyint not null default 1,
A5 tinyint not null default 1,
A6 tinyint not null default 1,
A7 tinyint not null default 1,
A8 tinyint not null default 1,
A9 tinyint not null default 1,
A10 tinyint not null default 1,
A11 tinyint not null default 1,
A12 tinyint not null default 1,
A13 tinyint not null default 1,
A14 tinyint not null default 1,
A15 tinyint not null default 1,
A16 tinyint not null default 1,
B1 tinyint not null default 1,
B2 tinyint not null default 1,
B3 tinyint not null default 1,
B4 tinyint not null default 1,
B5 tinyint not null default 1,
B6 tinyint not null default 1,
B7 tinyint not null default 1,
B8 tinyint not null default 1,
B9 tinyint not null default 1,
B10 tinyint not null default 1,
B11 tinyint not null default 1,
B12 tinyint not null default 1,
B13 tinyint not null default 1,
B14 tinyint not null default 1,
B15 tinyint not null default 1,
B16 tinyint not null default 1
)
GO
INSERT master.dbo.RepeatBlock default values
GO 4096
WITH cte1 AS
(
SELECT A FROM
(
SELECT
A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,
B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16
FROM master..RepeatBlock --4,096
) AS Z
UNPIVOT
(A FOR O IN (A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16)) AS Z
UNPIVOT
(B FOR Q IN (B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16)) AS Z
) --1,048,576 rows
,
cte2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row FROM cte1
CROSS JOIN cte1 AS T2
) --1,099,511,627,776 rows
SELECT COUNT_BIG(ALL CASE
WHEN row%15 = 0 THEN 'FIZZBUZZ'
WHEN row%5 = 0 THEN 'BUZZ'
WHEN row%3 = 0 THEN 'FIZZ'
ELSE CAST(row AS VARCHAR(14))
END)
FROM cte2 WHERE row < 100000000001 --set limit to 100 billion
March 8, 2010 at 6:15 pm
I have given in to curiosity and set off THE TRILLION - should be done in a few days I guess?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 8, 2010 at 7:15 pm
Yes it may try your patience Mister Magoo!
March 8, 2010 at 7:55 pm
Tall and Narrow Approach has the Edge
Well the tall and narrow query seems to work quite well. 10 unpivots X 4 column block is my fastest so far at 6 hours 7 minutes.
And it fits in the width of the code window to boot!
WITH cte1 AS
(
SELECT A FROM
(
SELECT
1 AS A1,1 AS A2,1 AS A3,1 AS A4,
1 AS B1,1 AS B2,1 AS B3,1 AS B4,
1 AS C1,1 AS C2,1 AS C3,1 AS C4,
1 AS D1,1 AS D2,1 AS D3,1 AS D4,
1 AS E1,1 AS E2,1 AS E3,1 AS E4,
1 AS F1,1 AS F2,1 AS F3,1 AS F4,
1 AS G1,1 AS G2,1 AS G3,1 AS G4,
1 AS H1,1 AS H2,1 AS H3,1 AS H4,
1 AS I1,1 AS I2,1 AS I3,1 AS I4,
1 AS J1,1 AS J2,1 AS J3,1 AS J4
) AS Z
UNPIVOT (A FOR O IN (A1,A2,A3,A4)) AS Z
UNPIVOT (B FOR P IN (B1,B2,B3,B4)) AS Z
UNPIVOT (C FOR Q IN (C1,C2,C3,C4)) AS Z
UNPIVOT (D FOR R IN (D1,D2,D3,D4)) AS Z
UNPIVOT (E FOR S IN (E1,E2,E3,E4)) AS Z
UNPIVOT (F FOR T IN (F1,F2,F3,F4)) AS Z
UNPIVOT (G FOR U IN (G1,G2,G3,G4)) AS Z
UNPIVOT (H FOR V IN (H1,H2,H3,H4)) AS Z
UNPIVOT (I FOR W IN (I1,I2,I3,I4)) AS Z
UNPIVOT (J FOR X IN (J1,J2,J3,J4)) AS Z
) --1,048,576 rows
,
cte2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row FROM cte1
CROSS JOIN cte1 AS T2
) --1,099,511,627,776 rows
SELECT COUNT_BIG(ALL CASE
WHEN row%15 = 0 THEN 'FIZZBUZZ'
WHEN row%5 = 0 THEN 'BUZZ'
WHEN row%3 = 0 THEN 'FIZZ'
ELSE CAST(row AS VARCHAR(14))
END)
FROM cte2 WHERE row < 100000000001 --set limit to 100 billion
March 8, 2010 at 8:28 pm
So will this 20 unpivot variation one go even faster?
WITH cte1 AS
(
SELECT A FROM
(
SELECT
1 AS A1,1 AS A2,
1 AS B1,1 AS B2,
1 AS C1,1 AS C2,
1 AS D1,1 AS D2,
1 AS E1,1 AS E2,
1 AS F1,1 AS F2,
1 AS G1,1 AS G2,
1 AS H1,1 AS H2,
1 AS I1,1 AS I2,
1 AS J1,1 AS J2,
1 AS AA1,1 AS AA2,
1 AS BB1,1 AS BB2,
1 AS CC1,1 AS CC2,
1 AS DD1,1 AS DD2,
1 AS EE1,1 AS EE2,
1 AS FF1,1 AS FF2,
1 AS GG1,1 AS GG2,
1 AS HH1,1 AS HH2,
1 AS II1,1 AS II2,
1 AS JJ1,1 AS JJ2
) AS Z
UNPIVOT (A FOR O IN (A1,A2)) AS Z
UNPIVOT (B FOR P IN (B1,B2)) AS Z
UNPIVOT (C FOR Q IN (C1,C2)) AS Z
UNPIVOT (D FOR R IN (D1,D2)) AS Z
UNPIVOT (E FOR S IN (E1,E2)) AS Z
UNPIVOT (F FOR T IN (F1,F2)) AS Z
UNPIVOT (G FOR U IN (G1,G2)) AS Z
UNPIVOT (H FOR V IN (H1,H2)) AS Z
UNPIVOT (I FOR W IN (I1,I2)) AS Z
UNPIVOT (J FOR X IN (J1,J2)) AS Z
UNPIVOT (AA FOR OO IN (AA1,AA2)) AS Z
UNPIVOT (BB FOR PP IN (BB1,BB2)) AS Z
UNPIVOT (CC FOR QQ IN (CC1,CC2)) AS Z
UNPIVOT (DD FOR RR IN (DD1,DD2)) AS Z
UNPIVOT (EE FOR SS IN (EE1,EE2)) AS Z
UNPIVOT (FF FOR TT IN (FF1,FF2)) AS Z
UNPIVOT (GG FOR UU IN (GG1,GG2)) AS Z
UNPIVOT (HH FOR VV IN (HH1,HH2)) AS Z
UNPIVOT (II FOR WW IN (II1,II2)) AS Z
UNPIVOT (JJ FOR XX IN (JJ1,JJ2)) AS Z
) --1,048,576 rows
,
cte2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row FROM cte1
CROSS JOIN cte1 AS T2
) --1,099,511,627,776 rows
SELECT COUNT_BIG(ALL CASE
WHEN row%15 = 0 THEN 'FIZZBUZZ'
WHEN row%5 = 0 THEN 'BUZZ'
WHEN row%3 = 0 THEN 'FIZZ'
ELSE CAST(row AS VARCHAR(14))
END)
FROM cte2 WHERE row < 100000000001 --set limit to 100 billion
March 9, 2010 at 3:10 am
Taller and Narrower 20 Unpivot X 2 Column Block Query has the Edge
Well the answer to the above (SQL script enclosed) is absolutely yes. The 20 unpivot x 2 column block query has a marginal advantage over the corresponding 10 X 4 version. This elongated query fares better in side by side comparisons at the 4 billion limit, where measurements in the region of 14.5 minutes allow multiple comparisons.
Yes it would seem the taller and narrower the better, well at least up to the mark of 20 unpivots.
March 9, 2010 at 9:57 am
Remarkable.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 9, 2010 at 1:48 pm
steve-893342 (3/9/2010)
Taller and Narrower 20 Unpivot X 2 Column Block Query has the Edge
That's a very interesting redult, and quite remarkable. Congratulations on finding this method.
Tom
March 11, 2010 at 11:51 am
THE TRILLION Revisited - The 40 Unpivot Skyscraper
Well I feel compelled to push the multiple unpivots approach to extremes. 40 unpivots against a 2 column block yields over 1 trillion rows in the base table. Has the appearance of a skyscraper, wouldn't you say?
The competing 20 unpivot/million row base table/single cross join variation has been submitted previously (cf Posts #879060 & #879206).
So folks, what do you say?
Do you think the skyscraper wins the day?
--THE SKYSCRAPER
--40 Unpivots on a 2 column block release over 1 trillion rows in the base table
--No cross joins required
WITH cte1 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row FROM
(
SELECT
1 AS A1,1 AS A2,
1 AS B1,1 AS B2,
1 AS C1,1 AS C2,
1 AS D1,1 AS D2,
1 AS E1,1 AS E2,
1 AS F1,1 AS F2,
1 AS G1,1 AS G2,
1 AS H1,1 AS H2,
1 AS I1,1 AS I2,
1 AS J1,1 AS J2,
1 AS AA1,1 AS AA2,
1 AS BB1,1 AS BB2,
1 AS CC1,1 AS CC2,
1 AS DD1,1 AS DD2,
1 AS EE1,1 AS EE2,
1 AS FF1,1 AS FF2,
1 AS GG1,1 AS GG2,
1 AS HH1,1 AS HH2,
1 AS II1,1 AS II2,
1 AS JJ1,1 AS JJ2,
1 AS AAA1,1 AS AAA2,
1 AS BBB1,1 AS BBB2,
1 AS CCC1,1 AS CCC2,
1 AS DDD1,1 AS DDD2,
1 AS EEE1,1 AS EEE2,
1 AS FFF1,1 AS FFF2,
1 AS GGG1,1 AS GGG2,
1 AS HHH1,1 AS HHH2,
1 AS III1,1 AS III2,
1 AS JJJ1,1 AS JJJ2,
1 AS AAAA1,1 AS AAAA2,
1 AS BBBB1,1 AS BBBB2,
1 AS CCCC1,1 AS CCCC2,
1 AS DDDD1,1 AS DDDD2,
1 AS EEEE1,1 AS EEEE2,
1 AS FFFF1,1 AS FFFF2,
1 AS GGGG1,1 AS GGGG2,
1 AS HHHH1,1 AS HHHH2,
1 AS IIII1,1 AS IIII2,
1 AS JJJJ1,1 AS JJJJ2
) AS Z
UNPIVOT (A FOR O IN (A1,A2)) AS Z
UNPIVOT (B FOR P IN (B1,B2)) AS Z
UNPIVOT (C FOR Q IN (C1,C2)) AS Z
UNPIVOT (D FOR R IN (D1,D2)) AS Z
UNPIVOT (E FOR S IN (E1,E2)) AS Z
UNPIVOT (F FOR T IN (F1,F2)) AS Z
UNPIVOT (G FOR U IN (G1,G2)) AS Z
UNPIVOT (H FOR V IN (H1,H2)) AS Z
UNPIVOT (I FOR W IN (I1,I2)) AS Z
UNPIVOT (J FOR X IN (J1,J2)) AS Z
UNPIVOT (AA FOR OO IN (AA1,AA2)) AS Z
UNPIVOT (BB FOR PP IN (BB1,BB2)) AS Z
UNPIVOT (CC FOR QQ IN (CC1,CC2)) AS Z
UNPIVOT (DD FOR RR IN (DD1,DD2)) AS Z
UNPIVOT (EE FOR SS IN (EE1,EE2)) AS Z
UNPIVOT (FF FOR TT IN (FF1,FF2)) AS Z
UNPIVOT (GG FOR UU IN (GG1,GG2)) AS Z
UNPIVOT (HH FOR VV IN (HH1,HH2)) AS Z
UNPIVOT (II FOR WW IN (II1,II2)) AS Z
UNPIVOT (JJ FOR XX IN (JJ1,JJ2)) AS Z
UNPIVOT (AAA FOR OOO IN (AAA1,AAA2)) AS Z
UNPIVOT (BBB FOR PPP IN (BBB1,BBB2)) AS Z
UNPIVOT (CCC FOR QQQ IN (CCC1,CCC2)) AS Z
UNPIVOT (DDD FOR RRR IN (DDD1,DDD2)) AS Z
UNPIVOT (EEE FOR SSS IN (EEE1,EEE2)) AS Z
UNPIVOT (FFF FOR TTT IN (FFF1,FFF2)) AS Z
UNPIVOT (GGG FOR UUU IN (GGG1,GGG2)) AS Z
UNPIVOT (HHH FOR VVV IN (HHH1,HHH2)) AS Z
UNPIVOT (III FOR WWW IN (III1,III2)) AS Z
UNPIVOT (JJJ FOR XXX IN (JJJ1,JJJ2)) AS Z
UNPIVOT (AAAA FOR OOOO IN (AAAA1,AAAA2)) AS Z
UNPIVOT (BBBB FOR PPPP IN (BBBB1,BBBB2)) AS Z
UNPIVOT (CCCC FOR QQQQ IN (CCCC1,CCCC2)) AS Z
UNPIVOT (DDDD FOR RRRR IN (DDDD1,DDDD2)) AS Z
UNPIVOT (EEEE FOR SSSS IN (EEEE1,EEEE2)) AS Z
UNPIVOT (FFFF FOR TTTT IN (FFFF1,FFFF2)) AS Z
UNPIVOT (GGGG FOR UUUU IN (GGGG1,GGGG2)) AS Z
UNPIVOT (HHHH FOR VVVV IN (HHHH1,HHHH2)) AS Z
UNPIVOT (IIII FOR WWWW IN (IIII1,IIII2)) AS Z
UNPIVOT (JJJJ FOR XXXX IN (JJJJ1,JJJJ2)) AS Z
) --1,099,511,627,776 rows
SELECT COUNT_BIG(ALL CASE
WHEN row%15 = 0 THEN 'FIZZBUZZ'
WHEN row%5 = 0 THEN 'BUZZ'
WHEN row%3 = 0 THEN 'FIZZ'
ELSE CAST(row AS VARCHAR(14))
END)
FROM cte1 WHERE row < 1000000000001 --set limit to 1 trillion
March 11, 2010 at 11:56 am
I am quite interested to see the results.
of course, this method would be highly impractical to recite during an interview:Wow:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 12:35 pm
mister.magoo (3/8/2010)
I have given in to curiosity and set off THE TRILLION - should be done in a few days I guess?
CPU time = 228886430 ms, elapsed time = 229215575 ms.
That is 3814 minutes
OR 63.58 hours
OR 2.65 days
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 256 through 270 (of 363 total)
You must be logged in to reply to this topic. Login to reply