The maximum recursion 100 has been exhausted before statement completion

Question

Post reply

The maximum recursion 100 has been exhausted before statement completion

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

alexander.semenov (2/23/2010)

Hi Grasshopper,

Just stumbled on your topic and thought about why one should limit oneself with maximum recursion limit? We can use even less memory if we want to... The only price for this is just a longer query execution period. So it's up to you what approach to use.

In fact we can generate as many records as we want without affecting that limit value and writing a stored procedure. We just need to reuse your initial approach several times using Cartesian Join... Lets say we want to count weekdays in several years period. Joining your DateList virtual table (which mast not exceed 100 rows ) for three times in Cartesian join we get third power of hundred which is a million rows record set. The more we join the more we get (use reasonable amount of joins;-) )

Here the solution:

DECLARE @STARTDATE datetime;

DECLARE @EntDt datetime;

set @STARTDATE = '01/01/2009';

set @EntDt = '12/31/2009';

WITH

hierarchy (level) as

(

select 0 level

union all

select h.level + 1

from hierarchy h

where h.level < 100

),

LotsOfRows(rownumber) as

(

select cast(@STARTDATE as datetime) + ROW_NUMBER() over (order by rand() /*h1.level*/) as rownumber --with Rand() it works a way faster

from hierarchy h1

,hierarchy h2

,hierarchy h3

)

select count(*) as DayCnt from (

select rownumber, DATENAME(WEEKDAY, rownumber ) as WEEKDAY

from LotsOfRows

where DATENAME(WEEKDAY, rownumber ) not IN ( 'Saturday','Sunday' )

and rownumber < convert(VARCHAR(15),@EntDt,101)

)a

In fact that's not my solution 😉 I spied it on from an Oracle guy who does the same using famous connect by approach.

http://blog.tanelpoder.com/2008/06/08/generating-lots-of-rows-using-connect-by-safely/%5B/quote%5D

Agreed. If you'd like to see a comparison of several counting solutions in SQL Server (not including the While Loop), please see the following article...

http://qa.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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

I see lot's of folks using Recursive CTE's (rCTE's) for this task. [font="Arial Black"]PLEASE [/font]don't use or recommend rCTE's for this or any simple counting task. Use one of the other solutions folks posted. Please see the following article for why...

http://qa.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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

radro23 SSC Veteran Points: 236 More actions · Answer 3

radro23

SSC Veteran

Points: 236

January 27, 2012 at 10:15 am

#1439514

Why the reference to sys.columns?

I think this solution works well...I'm just trying to figure out the purpose of the sys.columns reference?

Thanks,

Radro

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Sys.columns is simply being used as a row source to drive a cross join to produce a lot of rows instead of using an rCTE, WHILE loop, or Cursor. R.Barry Young calls the technique a "Pseudo-Cursor" because, behind the scenes, SQL Server is looping at light speed to produce the rows. Nothing in the sys.columns table is being used. It's just the "presence of rows" that are being used.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions