April 4, 2013 at 8:20 pm
Comments posted to this topic are about the item Generate N numbers
April 9, 2013 at 5:21 am
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"
April 14, 2013 at 3:13 pm
@harsha-2 (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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply