Get a range of numbers

  • Comments posted to this topic are about the item Get a range of numbers

  • wouldn't this be easier to do with a recursive CTE?

    declare @start bigint=99;

    declare @end bigint=147;

    with x (num) as

    (

    select @start as num

    UNION ALL

    SELECT num+1 from x

    where num <@end

    )

    select * from x where num between @start and @end

    option (maxrecursion 10000);

    MVDBA

  • michael vessey (5/2/2012)


    wouldn't this be easier to do with a recursive CTE?

    declare @start bigint=99;

    declare @end bigint=147;

    with x (num) as

    (

    select 1 as num

    UNION ALL

    SELECT num+1 from x

    where num <@end

    )

    select * from x where num between @start and @end

    option (maxrecursion 10000);

    Or . . .

    DECLARE @start BIGINT = 97;

    DECLARE @end BIGINT = 250000;

    WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),

    CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),

    CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),

    CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),

    CTE5(n) AS (SELECT 1 FROM CTE4 x, CTE4 y),

    CTE6(n) AS (SELECT 0 UNION ALL

    SELECT TOP (@end-@start)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM CTE5 x, CTE5 y)

    SELECT @start+n

    FROM CTE6

    WHERE @start+n <= @end;

    Or. . .

    DECLARE @start BIGINT = 97;

    DECLARE @end BIGINT = 1000000;

    SELECT @start+n

    FROM (SELECT 0 UNION ALL

    SELECT TOP (@end-@start)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM master.sys.columns a, master.sys.columns b, master.sys.columns c) a(n)

    WHERE @start+n <= @end;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version

    MVDBA

  • michael vessey (5/2/2012)


    depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version

    Actually, you should pretty much never use recursive CTEs for counting. See this article for why[/url].


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Cadavre (5/2/2012)


    michael vessey (5/2/2012)


    depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version

    Actually, you should pretty much never use recursive CTEs for counting. See this article for why[/url].

    run the CTE and the cross join version you posted and look at the execution plans.... then see the difference

    the CTE has it's place when dealing with small numbers

    MVDBA

  • michael vessey (5/2/2012)


    Cadavre (5/2/2012)


    michael vessey (5/2/2012)


    depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version

    Actually, you should pretty much never use recursive CTEs for counting. See this article for why[/url].

    run the CTE and the cross join version you posted and look at the execution plans.... then see the difference

    the CTE has it's place when dealing with small numbers

    Did you have a gander at Jeff Moden's article that I linked? --> http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Using a table with numbers is best way

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • This may be only slightly related, but thought I'd share.

    I used this solution for creating sequential carton labels for shipping.

    I created a procedure that populates a table of sequential numbers by feeding in parameters from a SSRS report. In this case I feed the beginning and ending number.

    CREATE PROCEDURE SEQUENTIAL

    (

    @BEG_NUM INT,

    @END_NUM INT

    )

    AS

    DECLARE @start INT

    DECLARE @end INT

    SELECT @start = @BEG_NUM, @end = @END_NUM

    WHILE @start <= @end

    begin

    INSERT INTO BUR20VM.dbo.SEQUENTIAL_TABLE(CTN_NUM_SEQ)

    VALUES(@start)

    SET @start = @start + 1

    END

    GO

    I then use the resulting table results in a cross join with another table, to get the label output.

    I should also note, when I go to run the next sequence of numbers (for the next shipment), the SSRS report first EXECutes a second stored procedure to delete all values from the previosly created SEQUENTIAL_TABLE, and then re-populates with the new sequence of numbers.

    I think this is a good illustration of a real world application for the sequential table. 🙂

  • Thanks for the script.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply