Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fastest number series generator challenge.
DROP TABLE IF EXISTS dbo.CSBits;
CREATE TABLE dbo.CSBits (b BIT NOT NULL);
CREATE CLUSTERED COLUMNSTORE INDEX Idx_cci_CSBits
ON dbo.CSBits
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );
--Populate with 4294967296 rows.
INSERT INTO dbo.CSBits (b)
SELECT TOP(1048576) 0
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO 4096
CREATE OR ALTER FUNCTION dbo.GetNumsDaveMason_CSBits(@low AS BIGINT, @high AS BIGINT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@high - @low + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + (@low - 1) AS n
FROM dbo.CSBits
ORDER BY 1;
GO
--CPU time = 8078 ms, elapsed time = 9702 ms.
--CPU time = 8453 ms, elapsed time = 9828 ms.
--CPU time = 8437 ms, elapsed time = 9745 ms.
SELECT n
FROM dbo.GetNumsDaveMason_CSBits(1, 100000000)
OPTION(MAXDOP 1);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.