TSQL that lists all numbers from 1..100

Question

TSQL that lists all numbers from 1..100

Eli Leiba

SSC-Addicted

Points: 461
More actions
February 18, 2012 at 12:22 pm

#268572

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

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

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

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 1

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), --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.

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

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 3

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.

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

ThomasGr SSC Journeyman Points: 92 More actions · Answer 4

Try this one:

with t as (select 1 x

union all

select x + 1

from t

where x < 100)

select x

from t

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

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/

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 6

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.

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

TSQL that lists all numbers from 1..100

Cookies on SQLServerCentral