Generate N numbers

• harsha.majety

  SSCommitted

  Points: 1537

  More actions

  April 4, 2013 at 8:20 pm

  #276095

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

• Steve JP

  Ten Centuries

  Points: 1070

  More actions

  April 9, 2013 at 5:21 am

  #1604763

  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: 1004591

  More actions

  April 14, 2013 at 3:13 pm

  #1606323

  @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.

  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)