As always, the documentation is in the code. First, the run results.

OPERATION DURATION

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

Jeff's version, which also creates and populates the table... 3014173

Eirikur's version 4624264

sqldriver's version 16524945

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @INT_BUCKET INT = 0;

DECLARE @BIGINT_BUCKET BIGINT = 0;

DECLARE @TIMING_RESULTS TABLE

(

TR_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,TR_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())

,TR_TX VARCHAR(100) NOT NULL

);

DECLARE @SAMPLE_SIZE INT = 12963600;

-- Test one starts

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('sqldriver''s version');

;WITH E1(n) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),-- 10

E2(n) AS (SELECT 1 FROM E1 CROSS JOIN E1 AS b), -- 10*10

E3(n) AS (SELECT 1 FROM E1 CROSS JOIN E2),-- 10*100

E4(n) AS (SELECT 1 FROM E1 CROSS JOIN E3) ,-- 10*1000

E5(n) AS (SELECT 1 FROM E1 CROSS JOIN E4) ,-- 10*10000

E6(n) AS (SELECT 1 FROM E1 CROSS JOIN E5) ,-- 10*100000

E7(n) AS (SELECT 1 FROM E1 CROSS JOIN E6) ,-- 10*1000000

E8(n) AS (SELECT 1 FROM E1 CROSS JOIN E7) ,-- 10*10000000

EulerOrder AS (

SELECT TOP 12963600 -- using a variable here results in a non-parallel plan (@SAMPLE_SIZE)

[EulerOrder] = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL) ) -1) / 3600

FROM e8

),

EulerRuler AS (

SELECT eo.EulerOrder,

[EulerRuler] = ROW_NUMBER() OVER(PARTITION BY eo.EulerOrder ORDER BY (SELECT NULL) ) - 1

FROM EulerOrder eo

)

SELECT

@INT_BUCKET = el.EulerOrder

,@BIGINT_BUCKET = el.EulerRuler

FROM EulerRuler el

WHERE el.EulerRuler >= el.EulerOrder

ORDER BY el.EulerOrder

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('sqldriver''s version');

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

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('Eirikur''s version');

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

,EulerOrder AS

(

SELECT TOP (@SAMPLE_SIZE)

[EulerOrder] = NM.N / 3600

,[EulerRuler] = NM.N % 3600

FROM NUMS NM

)

SELECT

@INT_BUCKET = EU.EulerOrder

,@BIGINT_BUCKET = EU.EulerRuler

FROM EulerOrder EU

WHERE EU.EulerRuler >= EU.EulerOrder;

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('Eirikur''s version');

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

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('Jeff''s version, which also creates and populates the table...')

;

--=====================================================================================================================

-- Create and populate the test table with the product of a simple "TRIANGULAR JOIN" such that for every value of

-- [SID] (0 thru 3599), the Euler column will have a value greater than or equal to the [SID] thru 3599. For

-- reference purposes, a "Triangular Join" is half of a "Square Join", which is also known as a "Cartesian

-- Product" or "Cross Join".

--=====================================================================================================================

--===== If the test table alreeady exists, drop it to make reruns in SSMS easier

IF OBJECT_ID('dbo.EulerSource','U') IS NOT NULL

DROP TABLE dbo.EulerSource

;

--===== Create AND populate the table on-the-fly.

WITH

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

) -- 10^ 1 = 10

,E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) -- 10^ 4 = 10,000

,E16(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) -- 10^16 = 1 followed by more zero's than you can shake a stick at.

,Tally(N) AS (

SELECT 0 UNION ALL --This is faster than subtracting a 1 for every ROW_NUMBER().

SELECT TOP 3599 N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16

)

SELECT [SID] = ISNULL(CAST(t1.N AS INT),0) -- ISNULL makes the column NOT NULL

,Euler = ISNULL(CAST(t2.N AS INT),0) -- ISNULL makes the column NOT NULL

INTO dbo.EulerSource -- This creates the table

FROM Tally t1, Tally t2 -- Yep... 'nuther happy little Cross Join here...

WHERE t2.N >= t1.N -- ... and this limits it to a Triangular Join.

;

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('Jeff''s version, which also creates and populates the table...');

SELECT

TR.TR_TX AS OPERATION

,DATEDIFF(MICROSECOND, MIN(TR.TR_TS), MAX(TR.TR_TS)) AS DURATION

FROM @TIMING_RESULTS TR

GROUP BY TR.TR_TX

ORDER BY DURATION;

--ORDER BY 2; --ORDER BY column ordinal has been deprecated and should not be used.