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. Jim ----- 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. /gustav -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur Fuller Sendt: 28. oktober 2013 16:34 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] Recursive Queries in Microsoft SQL Server 2008 Gustav, 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. Arthur 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 http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com