## Generate N numbers

 Author Message harsha.majety Grasshopper Group: General Forum Members Points: 15 Visits: 448 Comments posted to this topic are about the item Generate N numbers Steve JP Valued Member Group: General Forum Members Points: 59 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-Forever Group: General Forum Members Points: 45050 Visits: 39899 @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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs