Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate N numbers Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 8:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Comments posted to this topic are about the item Generate N numbers
Post #1439073
Posted Tuesday, April 9, 2013 5:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 2, 2014 11:13 AM
Points: 51, Visits: 279
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"

Post #1440269
Posted Sunday, April 14, 2013 3:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse