I love the "don't know" option, and I agree with the poster above that those who rightfully tick that box should get a point. ;-)

In T-SQL, recursion is not as useful as it is in more traditional programming languages. Not only because of the 32-level restriction, but also because recursive algorithms are, by nature, iterative - and SQL Server is tuned to optimize set-based operations.

For the factorial example, the code below is not only more succinct, it also woud perform a lot better - if SQL Server actually had an aggregate PRODUCT() function (which, unfortunately, is not the case):

SELECT PRODUCT(n)

FROM dbo.Numbers

WHERE n BETWEEN 1 AND @in;

However, because there is no PRODUCT function, we have to work around that - using the mathematical fact that e

^{x} * e

^{y} = e

^{x+y}. (Note that the formula below works only for numbers above zero; there are also formulas to calculate aggregate product for sets that may include negative numbers or zero - google or bing if you need them, or ask me).

SELECT EXP(SUM(LOG(n)))

FROM dbo.Numbers

WHERE n BETWEEN 1 AND @in;

I'd argue that, with a comment thrown in to explain the EXP(SUM(LOG())) bit, this is still easier to maintain than the recursive procedure - and it'll definitely be faster!

* Note that the above is not meant to suggest that recursion has no place in SQL Server, I only wanted to point out that when possible, a set-based solution should be the first choice. There will always be situations where recursion is indeed the best solution.

Hugo Kornelis, SQL Server MVP

Visit my SQL Server blog:

http://sqlblog.com/blogs/hugo_kornelis