TSQL that lists all numbers from 1..100

  • Comments posted to this topic are about the item TSQL that lists all numbers from 1..100

  • Gosh, Eli. Thanks for sharing but please consider the following, instead. It's faster, still doesn't require access to a table, can produce a much larger range of numbers, and takes a whole lot fewer rows of code especially for what it does.

    WITH

    E1(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), --10E1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E3 or 10000 rows

    E8(N) AS (SELECT 1 FROM E4 a, E4 b), --10E4 or 100000000 rows

    E16(N) AS (SELECT 1 FROM E8 a, E8 b) --10E16 or more rows than you can shake a stick at

    SELECT TOP (@DesiredRowCount) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16

    ;

    --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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Who was the first person to come up with this (Jeff's) idea in the context of SQL Server? Was it Itzik Ben-Gan?

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • GPO (2/20/2012)


    Who was the first person to come up with this (Jeff's) idea in the context of SQL Server? Was it Itzik Ben-Gan?

    Yes, it was Ben-Gan. He used a binary set of CTE's instead of a decimal set like many of us do here. If you'd like to see a comparison of some methods for counting, please see the following article.

    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Try this one:

    with t as (select 1 x

    union all

    select x + 1

    from t

    where x < 100)

    select x

    from t

  • ThomasGr (2/20/2012)


    Try this one:

    with t as (select 1 x

    union all

    select x + 1

    from t

    where x < 100)

    select x

    from t

    Read the article that Jeff pointed at to see why that isn't a good idea.[/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/

  • ThomasGr (2/20/2012)


    Try this one:

    with t as (select 1 x

    union all

    select x + 1

    from t

    where x < 100)

    select x

    from t

    Thomas, read the article at the link I provided in my last post and see why that's even worse than the original suggestion on this thread. Certainly, it's one of the worse ways of counting.

    --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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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