Generate N numbers

  • harsha.majety

    SSCommitted

    Points: 1527

    Comments posted to this topic are about the item Generate N numbers

  • Steve JP

    Ten Centuries

    Points: 1069

    Interesting.

    Or old school:

    select distinct Number from master.dbo.spt_values where number between 0 and 1000

    Another one I have seen is:

    ;With CTE ( [Number] ) as (

    select Number = '0'

    union select Number = '1'

    union select Number = '2'

    union select Number = '3'

    union select Number = '4'

    union select Number = '5'

    union select Number = '6'

    union select Number = '7'

    union select Number = '8'

    union select Number = '9'

    )

    Select [Number]

    from (

    select [Number] = cast([3sp].[Number] + [2sp].[Number] + [1sp].[Number] as varchar(10))

    from CTE as [1sp]

    cross join CTE as [2sp]

    cross join CTE as [3sp]

    ) as x

    where [Number] between 0 and 1000

    order by 1

    But the best in a way is a tally table which can be indexed and used in a query plan.

    A slight problem that you might have with a recursive CTE is that the ".....the MAXRECURSION option exceeds the allowed maximum of 32767"

  • Jeff Moden

    SSC Guru

    Points: 995109

    @harsha-2 (and ayoe else interested),

    Thanks for taking the time to post a script but you have to be very careful with recursive CTEs. Please read the following article for why you should almost never use a recursive CTE to count. To summarize and contrary to what you published, they are far from "quick" an shouldn't be used even for the smallest of counts.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

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