Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Solution for "fastest number series generator" article on sqlperformance.com
DROP TABLE IF EXISTS dbo.GetNumsObbishTable;
CREATE TABLE dbo.GetNumsObbishTable (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);
GO
SET NOCOUNT ON;
DECLARE @c INT = 0;
WHILE @c < 128
BEGIN
INSERT INTO dbo.GetNumsObbishTable
SELECT TOP (1048576) @c * 1048576 - 1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
SET @c = @c + 1;
END;
GO
CREATE OR ALTER FUNCTION dbo.GetNumsObbish(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
SELECT @low + ID AS n
FROM dbo.GetNumsObbishTable
WHERE ID <= @high - @low
UNION ALL
SELECT @low + ID + CAST(134217728 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(134217728 AS BIGINT)
AND ID <= @high - @low - CAST(134217728 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(268435456 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(268435456 AS BIGINT)
AND ID <= @high - @low - CAST(268435456 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(402653184 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(402653184 AS BIGINT)
AND ID <= @high - @low - CAST(402653184 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(536870912 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(536870912 AS BIGINT)
AND ID <= @high - @low - CAST(536870912 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(671088640 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(671088640 AS BIGINT)
AND ID <= @high - @low - CAST(671088640 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(805306368 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(805306368 AS BIGINT)
AND ID <= @high - @low - CAST(805306368 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(939524096 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(939524096 AS BIGINT)
AND ID <= @high - @low - CAST(939524096 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1073741824 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1073741824 AS BIGINT)
AND ID <= @high - @low - CAST(1073741824 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1207959552 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1207959552 AS BIGINT)
AND ID <= @high - @low - CAST(1207959552 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1342177280 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1342177280 AS BIGINT)
AND ID <= @high - @low - CAST(1342177280 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1476395008 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1476395008 AS BIGINT)
AND ID <= @high - @low - CAST(1476395008 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1610612736 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1610612736 AS BIGINT)
AND ID <= @high - @low - CAST(1610612736 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1744830464 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1744830464 AS BIGINT)
AND ID <= @high - @low - CAST(1744830464 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(1879048192 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(1879048192 AS BIGINT)
AND ID <= @high - @low - CAST(1879048192 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2013265920 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2013265920 AS BIGINT)
AND ID <= @high - @low - CAST(2013265920 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2147483648 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2147483648 AS BIGINT)
AND ID <= @high - @low - CAST(2147483648 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2281701376 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2281701376 AS BIGINT)
AND ID <= @high - @low - CAST(2281701376 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2415919104 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2415919104 AS BIGINT)
AND ID <= @high - @low - CAST(2415919104 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2550136832 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2550136832 AS BIGINT)
AND ID <= @high - @low - CAST(2550136832 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2684354560 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2684354560 AS BIGINT)
AND ID <= @high - @low - CAST(2684354560 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2818572288 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2818572288 AS BIGINT)
AND ID <= @high - @low - CAST(2818572288 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(2952790016 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(2952790016 AS BIGINT)
AND ID <= @high - @low - CAST(2952790016 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3087007744 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3087007744 AS BIGINT)
AND ID <= @high - @low - CAST(3087007744 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3221225472 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3221225472 AS BIGINT)
AND ID <= @high - @low - CAST(3221225472 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3355443200 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3355443200 AS BIGINT)
AND ID <= @high - @low - CAST(3355443200 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3489660928 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3489660928 AS BIGINT)
AND ID <= @high - @low - CAST(3489660928 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3623878656 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3623878656 AS BIGINT)
AND ID <= @high - @low - CAST(3623878656 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3758096384 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3758096384 AS BIGINT)
AND ID <= @high - @low - CAST(3758096384 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(3892314112 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(3892314112 AS BIGINT)
AND ID <= @high - @low - CAST(3892314112 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(4026531840 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(4026531840 AS BIGINT)
AND ID <= @high - @low - CAST(4026531840 AS BIGINT)
UNION ALL
SELECT @low + ID + CAST(4160749568 AS BIGINT) AS n
FROM dbo.GetNumsObbishTable
WHERE @high - @low + 1 > CAST(4160749568 AS BIGINT)
AND ID <= @high - @low - CAST(4160749568 AS BIGINT);
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment