[dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Jim Lawrence accessd at shaw.ca
Mon Oct 28 14:02:54 CDT 2013

Just an off side note: Oracle 10.x to 11.x used to have a limit of 6 levels and at the time MS SQL had only one level...I would suspect that MS SQL now has no such level limit. The one thing I noted was that more that one level deep could bring my client's servers to "its knees" so to speak, so used multiple temp files instead. MS SQL may have a resolution for that problem. 


----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Discussion concerning MS SQL Server" <dba-sqlserver at databaseadvisors.com>
Sent: Monday, October 28, 2013 9:43:33 AM
Subject: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008

Hi Arthur

I think I recall that article. But wasn't that about a limited/fixed number
of sublevels? True recursion should just continue one level down until no
more levels are found, no matter how many levels.


-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Sendt: 28. oktober 2013 16:34
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008


Yes and no. Somewhere on TechRepublic there's an article by me that
discusses recursive SQL within a stored procedure. If you can't find it, I
have a copy in Word format that I could send you off-list, but their search
is pretty good. Try "recursion in t-sql". If that doesn't work try searching
for my name and that ought to find it.

Anyway, you're right: The WITH <name> AS (SELECT...) construct is T-SQL not
pure SQL. In essence, what you're doing is creating a virtual temp table and
then joining that to the real table (or view).

And you're also wrong. In pure SQL it's quite common to join a table or view
to itself. One typical use of this technique is to find the second-last item
in a set (which you do by joining a set to itself but the WHERE clause uses
a "<" comparison rather than an "=". Combine that with an ORDER BY ... DESC
and you get the previous row. If that's a little fuzzy, I'll supply an
example, but I encourage you to ponder it first. LOL.


On Mon, Oct 28, 2013 at 11:06 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Arthur
> So the WITH .. AS isolates the inner part?
> Normally, in pure SQL, you can't let a query pull data from itself 
> without getting a circular reference error or the like.
> /gustav 

dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list