One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them. In my previous posts I’ve discussed this topic with some good examples, link.

An incorrectly composed recursive CTE may cause an infinite loop. So recursive CTEs should be designed very carefully and the recursion level should be checked. To prevent it to run infinitely SQL Server’s default recursion level is set to 100. But you can change the level by using the MAXRECURSION option/hint. The recursion level ranges from 0 and 32,767.

If your CTEs recursion level crosses the limit then following error is thrown by SQL Server engine:Msg 530, Level 16, State 1, Line 11 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

A better thing would be to avoid the rCTE altogether because it’s as slow as a While Loop and uses a whole lot more resources. I tried to post a solution yesterday but it hasn’t made it through whatever review process there is. It contained a high speed integer function (fnTally) and the code to solve this problem with zero reads.

… This supports ALOT less recursion as it only loops through each month rather than every day … meaning that Recursions are less … with the max recursions yours supports 32767 days (about 90 years) where as mine supports 32767 months (2730 years)!…

Also the time difference is (with 90 years set):
Yours: 374ms
Mine: 31ms