Problem with CTE

Question

Post reply

Problem with CTE

dfarina

SSC Veteran

Points: 238
More actions
April 16, 2012 at 4:39 pm

#254833

Hi
I want to run a query to given a date return dates in 10 day interval here is the query
WITH fechas (fecha) AS (
SELECT GETDATE() AS fecha UNION ALL
SELECT DATEADD(day,10, fecha) from fechas )
select fecha from fechas
option (maxrecursion 12)
And when i run it gives the following error message
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.
Any guess?

Viewing 9 posts - 1 through 8 (of 8 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

i got it to work by using the following:

WITH fechas (fecha) AS (

SELECT GETDATE() AS fecha UNION ALL

SELECT DATEADD(day,10, fecha) from fechas )

select TOP 12 fecha from fechas -- instead of using the option max recursion use a top clause.

you can have a top x untill the max recursion level in sql server (100) since the recursion occurs in the cte an option on the outer query for the max recursion gives an error since you have no limit on how many rows you are selecting but limit the recursion to 12.

EDIT: to those with more experience with me let me know one way or the other if im interpreting the error correctly.

OUTPUT of the original CTE:

fecha

-----------------------

2012-04-16 20:12:22.387

2012-04-26 20:12:22.387

2012-05-06 20:12:22.387

2012-05-16 20:12:22.387

2012-05-26 20:12:22.387

2012-06-05 20:12:22.387

2012-06-15 20:12:22.387

2012-06-25 20:12:22.387

2012-07-05 20:12:22.387

2012-07-15 20:12:22.387

2012-07-25 20:12:22.387

2012-08-04 20:12:22.387

2012-08-14 20:12:22.387

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 12 has been exhausted before statement completion.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

vinu512 SSCoach Points: 15729 More actions · Answer 2

;WITH fechas (fecha) AS (

SELECT GETDATE() AS fecha UNION ALL

SELECT DATEADD(day,10, fecha) from fechas )

select fecha from fechas

option (maxrecursion 32767)

The above code causes an infinite recursion. "SELECT DATEADD(day,10, fecha) from fechas" is causing the recursion and if you just write "select fecha from fechas" then for every "fecha" it would execute the CTE again causing the infinite recursion. So, even if you give maxrecursion 32767(which is the allowed maximum), the query will still fail.

The code given by Capn. limits the Selection of "fecha" to the top 12. So after reaching the 12th value of "fecha" the query breaks out of the recursive loop. Hence, displaying the results.

Good one Capn.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

This is quicker: -

SELECT DATEADD(DD,N,GETDATE())

FROM (SELECT N

FROM (VALUES(0),(10),(20),(30),(40),(50),(60),

(70),(80),(90),(100),(110))a(N))b(N);

Using recursive CTEs to count is bad.

Let's count to 100: -

SET NOCOUNT ON;

--Used to take the display time out of the equation

DECLARE @HoldingVar INT;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH fechas (fecha) AS (

SELECT 0 AS fecha

UNION ALL SELECT fecha + 1 FROM fechas)

SELECT TOP 100 @HoldingVar = fecha

FROM fechas;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',81);

PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);

PRINT REPLICATE('=',81);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP 100 @HoldingVar = N-1

FROM dbo.Tally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),

CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),

CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTETally(N) AS (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE4 x, CTE4 y)

SELECT TOP 100 @HoldingVar = N

FROM CTETally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

================================================================================

===================================== rCTE =====================================

================================================================================

Table 'Worktable'. Scan count 2, logical reads 596, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 ms.

=================================================================================

===================================== Tally =====================================

=================================================================================

Table 'Tally'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

================================================================================

================================= Memory Tally =================================

================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

OK, quite close. Let's try counting to 1000.

SET NOCOUNT ON;

--Used to take the display time out of the equation

DECLARE @HoldingVar INT;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH fechas (fecha) AS (

SELECT 0 AS fecha

UNION ALL SELECT fecha + 1 FROM fechas)

SELECT TOP 1000 @HoldingVar = fecha

FROM fechas

OPTION (MAXRECURSION 1000);

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',81);

PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);

PRINT REPLICATE('=',81);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP 1000 @HoldingVar = N-1

FROM dbo.Tally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),

CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),

CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTETally(N) AS (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE4 x, CTE4 y)

SELECT TOP 1000 @HoldingVar = N

FROM CTETally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

================================================================================

===================================== rCTE =====================================

================================================================================

Table 'Worktable'. Scan count 2, logical reads 5995, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 15 ms.

=================================================================================

===================================== Tally =====================================

=================================================================================

Table 'Tally'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

================================================================================

================================= Memory Tally =================================

================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

OK, the recursive CTE is creeping up a tad. Let's try 10000.

SET NOCOUNT ON;

--Used to take the display time out of the equation

DECLARE @HoldingVar INT;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH fechas (fecha) AS (

SELECT 0 AS fecha

UNION ALL SELECT fecha + 1 FROM fechas)

SELECT TOP 10000 @HoldingVar = fecha

FROM fechas

OPTION (MAXRECURSION 10000);

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',81);

PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);

PRINT REPLICATE('=',81);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP 10000 @HoldingVar = N-1

FROM dbo.Tally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),

CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),

CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTETally(N) AS (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE4 x, CTE4 y)

SELECT TOP 10000 @HoldingVar = N

FROM CTETally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

================================================================================

===================================== rCTE =====================================

================================================================================

Table 'Worktable'. Scan count 2, logical reads 59996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 187 ms, elapsed time = 202 ms.

=================================================================================

===================================== Tally =====================================

=================================================================================

Table 'Tally'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 3 ms.

================================================================================

================================= Memory Tally =================================

================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 4 ms.

So the tally tables are now between 50 and 60 times faster. Let's try the highest number a recursive CTE can count to : -

SET NOCOUNT ON;

--Used to take the display time out of the equation

DECLARE @HoldingVar INT;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH fechas (fecha) AS (

SELECT 0 AS fecha

UNION ALL SELECT fecha + 1 FROM fechas)

SELECT TOP 32767 @HoldingVar = fecha

FROM fechas

OPTION (MAXRECURSION 32767);

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',81);

PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);

PRINT REPLICATE('=',81);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT TOP 32767 @HoldingVar = N-1

FROM dbo.Tally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);

PRINT REPLICATE('=',80);

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),

CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),

CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTETally(N) AS (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE4 x, CTE4 y)

SELECT TOP 32767 @HoldingVar = N

FROM CTETally;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

================================================================================

===================================== rCTE =====================================

================================================================================

Table 'Worktable'. Scan count 2, logical reads 196598, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 499 ms, elapsed time = 509 ms.

=================================================================================

===================================== Tally =====================================

=================================================================================

Table 'Tally'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 3 ms.

================================================================================

================================= Memory Tally =================================

================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 10 ms.

So tally table is between 50 and 200 times faster.

Personally, I can't think of a good reason to use something that I know is massively slower than other alternatives.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

CapnHector SSCoach Points: 16143 More actions · Answer 4

Cadavre (4/17/2012)
This is quicker: -
SELECT DATEADD(DD,N,GETDATE())
FROM (SELECT N
FROM (VALUES(0),(10),(20),(30),(40),(50),(60),
(70),(80),(90),(100),(110))a(N))b(N);
Using recursive CTEs to count is bad.
Let's count to 100: -
SET NOCOUNT ON;
--Used to take the display time out of the equation
DECLARE @HoldingVar INT;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH fechas (fecha) AS (
SELECT 0 AS fecha
UNION ALL SELECT fecha + 1 FROM fechas)
SELECT TOP 100 @HoldingVar = fecha
FROM fechas;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',81);
PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);
PRINT REPLICATE('=',81);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 100 @HoldingVar = N-1
FROM dbo.Tally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTETally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT TOP 100 @HoldingVar = N
FROM CTETally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
================================================================================
===================================== rCTE =====================================
================================================================================
Table 'Worktable'. Scan count 2, logical reads 596, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
=================================================================================
===================================== Tally =====================================
=================================================================================
Table 'Tally'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
================================================================================
================================= Memory Tally =================================
================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
OK, quite close. Let's try counting to 1000.
SET NOCOUNT ON;
--Used to take the display time out of the equation
DECLARE @HoldingVar INT;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH fechas (fecha) AS (
SELECT 0 AS fecha
UNION ALL SELECT fecha + 1 FROM fechas)
SELECT TOP 1000 @HoldingVar = fecha
FROM fechas
OPTION (MAXRECURSION 1000);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',81);
PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);
PRINT REPLICATE('=',81);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 1000 @HoldingVar = N-1
FROM dbo.Tally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTETally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT TOP 1000 @HoldingVar = N
FROM CTETally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
================================================================================
===================================== rCTE =====================================
================================================================================
Table 'Worktable'. Scan count 2, logical reads 5995, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 15 ms.
=================================================================================
===================================== Tally =====================================
=================================================================================
Table 'Tally'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
================================================================================
================================= Memory Tally =================================
================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
OK, the recursive CTE is creeping up a tad. Let's try 10000.
SET NOCOUNT ON;
--Used to take the display time out of the equation
DECLARE @HoldingVar INT;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH fechas (fecha) AS (
SELECT 0 AS fecha
UNION ALL SELECT fecha + 1 FROM fechas)
SELECT TOP 10000 @HoldingVar = fecha
FROM fechas
OPTION (MAXRECURSION 10000);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',81);
PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);
PRINT REPLICATE('=',81);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 10000 @HoldingVar = N-1
FROM dbo.Tally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTETally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT TOP 10000 @HoldingVar = N
FROM CTETally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
================================================================================
===================================== rCTE =====================================
================================================================================
Table 'Worktable'. Scan count 2, logical reads 59996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 202 ms.
=================================================================================
===================================== Tally =====================================
=================================================================================
Table 'Tally'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
================================================================================
================================= Memory Tally =================================
================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
So the tally tables are now between 50 and 60 times faster. Let's try the highest number a recursive CTE can count to : -
SET NOCOUNT ON;
--Used to take the display time out of the equation
DECLARE @HoldingVar INT;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',37) + ' rCTE ' + REPLICATE('=',37);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH fechas (fecha) AS (
SELECT 0 AS fecha
UNION ALL SELECT fecha + 1 FROM fechas)
SELECT TOP 32767 @HoldingVar = fecha
FROM fechas
OPTION (MAXRECURSION 32767);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',81);
PRINT REPLICATE('=',37) + ' Tally ' + REPLICATE('=',37);
PRINT REPLICATE('=',81);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 32767 @HoldingVar = N-1
FROM dbo.Tally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT REPLICATE('=',33) + ' Memory Tally ' + REPLICATE('=',33);
PRINT REPLICATE('=',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTETally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT TOP 32767 @HoldingVar = N
FROM CTETally;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
================================================================================
===================================== rCTE =====================================
================================================================================
Table 'Worktable'. Scan count 2, logical reads 196598, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 509 ms.
=================================================================================
===================================== Tally =====================================
=================================================================================
Table 'Tally'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
================================================================================
================================= Memory Tally =================================
================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
So tally table is between 50 and 200 times faster.
Personally, I can't think of a good reason to use something that I know is massively slower than other alternatives.

thanks for the tally table code, im still working on getting it through my head exactly how to use them and keep reading every thing on the web i can find.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

capn.hector (4/17/2012)
thanks for the tally table code, im still working on getting it through my head exactly how to use them and keep reading every thing on the web i can find.

Have you ever read the article that explains how they work?

[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font]

[/url]

--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)

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 6

Cadavre (4/17/2012)
Using recursive CTEs to count is bad.
... {snip} ...
Personally, I can't think of a good reason to use something that I know is massively slower than other alternatives.

You did a really good job of demonstrating that fact. Let me help "rattle the rubble" a bit on just how bad counting with rCTEs actually is in more ways than one in an "Alice's Restaurant" fashion. 😀 Please see the following article.

[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font][/url]

--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)

dfarina SSC Veteran Points: 238 More actions · Answer 7

dfarina

SSC Veteran

Points: 238

April 17, 2012 at 9:50 pm

#1475142

Many thanks to all of you!!!

CapnHector SSCoach Points: 16143 More actions · Answer 8

Jeff Moden (4/17/2012)
capn.hector (4/17/2012)
thanks for the tally table code, im still working on getting it through my head exactly how to use them and keep reading every thing on the web i can find.
Have you ever read the article that explains how they work?
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font]
[/url]

i read it but im still working into set based thinking and using tally tables instead of loops. just need to play with them more.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]