David's Blog

There are occasions when it’s useful to have a function that returns a sequence. Here’s the function that I use for this purpose:

CREATE FUNCTION [dbo].[sequence_generator] (@iterations INT, @base INT, @increment SMALLINT)
    RETURNS TABLE
    WITH SCHEMABINDING
AS RETURN
WITH [seq] AS (
    SELECT d = ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
)
    SELECT TOP(@iterations) i = (@base) + ((ROW_NUMBER() OVER(ORDER BY s1.d) - 1) * @increment)
        FROM [seq] s1, [seq] s2, [seq] s3, [seq] s4, [seq] s5, [seq] s6, [seq] s7, [seq] s8

To get the first 100 rows of a zero-based sequence with +1 increments:

SELECT *
    FROM [dbo].[sequence_generator](100, 0, 1)

I’ll be referring back to this function in future posts.