Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generate N numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, April 4, 2013 8:20 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, July 4, 2016 7:54 PM Points: 9, Visits: 448
 Comments posted to this topic are about the item Generate N numbers
Post #1439073
 Posted Tuesday, April 9, 2013 5:21 AM
 Valued Member Group: General Forum Members Last Login: Saturday, November 28, 2015 7:06 AM Points: 55, 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"
Post #1440269
 Posted Sunday, April 14, 2013 3:13 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 @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 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1442137

 Permissions