ChrisM@Work - Friday, February 17, 2017 9:38 AM

Nice, Sergiy, and very useful.
I'm guessing that you've checked out all of the alternatives to get rid of the row count spool. I've only had a little time to play today and came up with this:

DECLARE

        @StartValue bigint = null, -- -32768,

     @EndValue bigint = null, -- 32767,

        @Rows INT = 980, -- number of rows to bereturned. Used only when either @StartValue or @EndValue is not supplied

     @Increment smallint=3;

 

WITH BaseNum (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

       ),

       L1 (N) as (SELECT 1 FROM BaseNum bn1 CROSS JOIN BaseNum bn2),

       L2 (N) as (SELECT 1 FROM L1 a1 CROSS JOIN L1 a2),

       _Tally (N) as (

              SELECT TOP(ISNULL(@EndValue - @StartValue + @Increment,@Rows)/@Increment)

                     rn= ISNULL(@StartValue,0) + ((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*@Increment)

              FROM L2 a1 CROSS JOIN L2 a2 CROSS JOIN L2 a3

              )

SELECT * FROM _Tally

[/code]