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 04, 2013 8:20 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 7:33 AM Points: 9, Visits: 432
 Comments posted to this topic are about the item Generate N numbers
Post #1439073
 Posted Tuesday, April 09, 2013 5:21 AM
 Valued Member Group: General Forum Members Last Login: Today @ 12:50 PM Points: 51, Visits: 266
 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-Dedicated Group: General Forum Members Last Login: Today @ 9:41 AM Points: 34,554, Visits: 28,731
 @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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1442137

 Permissions