Generate N numbers

 Author Message harsha.majety Ten Centuries Group: General Forum Members Points: 1165 Visits: 449 Comments posted to this topic are about the item Generate N numbers Steve JP SSC Eights! Group: General Forum Members Points: 869 Visits: 328 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 xwhere [Number] between 0 and 1000order 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 Group: General Forum Members Points: 783129 Visits: 45899 @Harsha (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 ModenRBAR 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs