jw.lankhaar (6/13/2013)
For a limited number of rows (< 100) using a recursive common table expression is an alternative:
Please read the following article for why you should probably never use an rCTE for such a thing.
http://www.sqlservercentral.com/articles/T-SQL/74118/
Note that due to recursion, the number of returned rows may not exceed 99.
Careful now. It is true that the default for rCTEs is a max of 100 but that's easy to override using OPTION(MAXRECURSION x) where "x" can be any positive integer from 0 to 32,767 and "0" means "unlimited".
--Jeff Moden
Change is inevitable... Change for the better is not.