SQLServerCentral / Discuss Content Posted by Srinivas Sampath / Article Discussions / Article Discussions by Author / Recursive Queries in SQL Server 2005 / Latest Posts

RE: Recursive Queries in SQL Server 2005

JX Harding — Mon, 31 Jan 2011 07:15:43 GMT

Here is the sample organizational chart.Chief Executive Officer Senior Director – Product Development Product Development Manager Project Lead Developers QA Lead Testing Team Documentation Lead Technical Writers would it be possible to say:1. i want to view the hierarchy of managers of technical writerswhich would then return :Chief Executive Officer Senior Director – Product Development Product Development Manager Documentation Lead (technical writers)?

RE: Recursive Queries in SQL Server 2005

swoveland21 — Thu, 30 Dec 2010 12:31:36 GMT

Hello, I "Google'd" across this as I was pondering a solution to this problem and think it solves it nicely but it is limited to only 100 recursions. I wonder what some other solutions to the problem might be?ProblemTSQL Challenge 46 - Remove leading occurrences of the first character in a string[url=http://beyondrelational.com/blogs/tc/archive/2010/12/27/tsql-challenge-46-Remove-leading-occurrences-of-the-first-character-in-a-string.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2010Dec28][/url]

RE: Recursive Queries in SQL Server 2005

Tepluken — Fri, 09 Oct 2009 11:03:02 GMT

Thank you Srinivas. A very lucid, intelligible presentation of CTEs. I Much appreciate your efforts

RE: Recursive Queries in SQL Server 2005

KBSK — Thu, 03 Sep 2009 08:38:35 GMT

[quote][b]Dave Ballantyne (9/3/2009)[/b][hr][quote][b]KB (9/3/2009)[/b][hr]Hi,In this article, I need to know what is the purpose of SortKey in CTE.In the hierarchy, will the Top level row is returned first??(In our project we need only the top row - so it is important that the row returned is correct)Please let me know.Thanks,KB[/quote]Please dont cross post[url]http://www.sqlservercentral.com/Forums/Topic782210-338-1.aspx[/url][/quote]Sorry for that...I didn't know whether this Discussion is active or not, so I posted here too..

RE: Recursive Queries in SQL Server 2005

Dave Ballantyne — Thu, 03 Sep 2009 08:29:17 GMT

[quote][b]KB (9/3/2009)[/b][hr]Hi,In this article, I need to know what is the purpose of SortKey in CTE.In the hierarchy, will the Top level row is returned first??(In our project we need only the top row - so it is important that the row returned is correct)Please let me know.Thanks,KB[/quote]Please dont cross post[url]http://www.sqlservercentral.com/Forums/Topic782210-338-1.aspx[/url]

RE: Recursive Queries in SQL Server 2005

KBSK — Thu, 03 Sep 2009 08:16:55 GMT

Hi,In this article, I need to know what is the purpose of SortKey in CTE.In the hierarchy, will the Top level row is returned first??(In our project we need only the top row - so it is important that the row returned is correct)Please let me know.Thanks,KB

RE: Recursive Queries in SQL Server 2005

darren_1065 — Sat, 14 Feb 2009 12:01:56 GMT

Very good article. However, I couldn't implement this without having an existing Level value. I kept working at this and came up with what appears to be a nice alternative. It's not a recursive procedure but gave me the results I wanted to I thought I'd share my work.My main table structure is a single table with related entities I'll call departments, where Departments are related to one another.DepartmentID int,ParentID int,Title varchar(25)What I needed was a procedure that I could pass an ID into and it would give me that ID and all related IDs down the tree. I did this using a temporary table. First inserting the Main record that was passed into the procedure then retrieving all child records using a join back to the temp table. This required using a loop which I did by using a WHILE statement and checking the @@ROWCOUNT var. This looks to work fine for retrieving segments of the table but won't work for getting the entire table, which is simple enough to do with a select statement. Here's the rest.CREATE PROCEDURE [dbo].[store_GetProductByDepartment]( @DepartmentID int)AS -- create temp table for top 2 levels of menuCREATE TABLE #TEMP_TABLE( [DepartmentID] [int] NOT NULL, [ParentID] [int] NULL, [Title] [nvarchar](256) NULL) -- get the first recordINSERT INTO #TEMP_TABLE (DepartmentID, ParentID, Title) SELECT d1.DepartmentID , d1.ParentID , d1.TitleFROM dbo.store_Department d1 WHERE d1.DepartmentID = @DepartmentID -- loop through the records and insert found departmentsWHILE @@ROWCOUNT > 0BEGIN INSERT INTO #TEMP_TABLE (DepartmentID, ParentID, Title) SELECT d1.DepartmentID , d1.ParentID , d1.Title FROM dbo.store_Department d1 INNER JOIN #TEMP_TABLE d2 ON d1.ParentID = d2.DepartmentID WHERE d1.DepartmentID NOT IN (SELECT DepartmentID FROM #TEMP_TABLE)END -- testing-- select * from #TEMP_TABLE SELECT * FROM store_ProductWHERE store_Product.DepartmentID in (SELECT DepartmentID FROM #TEMP_TABLE )I haven't tested this in all situations. If anyone can see problems from doing this or have any suggestions for improvement please post. Hope this helps.

RE: Recursive Queries in SQL Server 2005

hbpank — Fri, 06 Feb 2009 02:24:42 GMT

Thanks a lot Shrinivas for that. I just want to make Point is that to get results in the Oracle it quite a lot Easier... Just use START WITH clause specify who would be the Parent and Oracle takes care of all. I do agree that SS 2005 has better but Oracle is still the Best. Hary Pank

RE: Recursive Queries in SQL Server 2005

hbpank — Fri, 06 Feb 2009 02:12:47 GMT

Thanks a lot Shrinivas for that. I just want to make a Point is that to get same sort of output in the Oracle is quite easier... Just use START WITH clause specify who would be the Parent and Oracle takes care of all. I do agree that SS 2005 is better but Oracle is still the Best. Hary Pank

RE: Recursive Queries in SQL Server 2005

natasha.haslam — Fri, 15 Aug 2008 05:34:05 GMT

[quote][b]MikeAngelastro (4/18/2008)[/b][hr][quote][b]GSquared (4/18/2008)[/b][hr][quote][b]MikeAngelastro (3/13/2008)[/b][hr]I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?[/quote]A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.[/quote]GSquared, Thanks for your input.I did use a CTE initially. But situations arrived later where the resulting record set had too many rows. My tests indicated that the extra rows appear as soon as a branch appears more once in the table; that is, any branch can be a child in more than one product tree. The product-tree I am dealing with has this possibility and therefore I have to handle it. I searched the internet for a sample CTE that was specifically designed to handle this condition and found none. Because it turned out that the column values in the extra rows appeared to be identical to one of the original rows, I tried to use a “DISTINCT” qualifier but the CTE refused to run, even when I used the following approach:SELECT DISTINCT FROM CTEwhere “CTE” is the CTE’s record set result - extra rows and all.And even here, when rows have the same values as other rows, it does not necessarily mean they should be excluded from the result; this would happen if the same branch appears more than one in the same overall product tree. Given these results, how can the CTE be constructed in order to exclude the extra rows?Thanks,Mike[/quote]I am having a similar problem. I originally used a while loop to create the heirachical structure and if a child had been examined before I would restrict the itteration so that the geneology of that child was only shown once. I used the following code to stop the recursion. (@ChildLotIDs was the heirachical temporary table and consumedlotid was the and the ConsumedLotID was the field that allowed the self join.)C.ConsumedLotID NOT IN (SELECT EL.ConsumedLotID FROM @ChildLotIDs EL WHERE ClI.ParameterLotID = EL.ParameterLotID AND EL.TreeLevel <= @Level)However when using CTE I can't reference the table twice in the recursive member defintion.. I get the following error..Recursive member of a common table expression 'LotGeneology' has multiple recursive references.Any ideas how to get around this ?

RE: Recursive Queries in SQL Server 2005

natasha.haslam — Fri, 15 Aug 2008 05:00:17 GMT

[quote][b]Satish Jha (5/24/2006)[/b][hr]Thanks for this article. I have one question here -how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order[/quote]I have used recursion before using while loop and found a tree code very useful. The tree code allows you to sort the data as if it was displayed on a hierarchical tree view. I used another new CTE function to return incremental identifiers for a portion of data (ie, incremental identifiers for the child row of a certain parent) and staggered them in varchar of length 40 (allowing 20 levels with a max of 99 children). The formatting was a little tricky. I used the RIGHT and LEFT Functions to pad the numbers with 0sand I have assumed that you want the ordering of the members of each leave to be by the field DateJoined. For the anchor member definition I had some similar code for that field...right('0' + cast(ROW_NUMBER() OVER (Partition BY a.ReportingLevelID ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19)and for the recursive member definition I used...left(left(b.TreeCode, (b.TreeLevel+1)*2) + right('0' + cast(ROW_NUMBER() OVER (Partition BY b.TreeLevel ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19),40)

RE: Recursive Queries in SQL Server 2005

GSquared — Mon, 21 Apr 2008 13:25:00 GMT

[quote][b]john.racer (4/21/2008)[/b][hr]The basic business requirement is to roll sales up from employee to lead to manager to director. We have much movement in our sales organization. We want to historically calculate sale mad in history and roll up as it was at that point in time. Performing this with CTE gets tricky here as you know have people potentially in multiple places in the hierarchy for given efective dates. We use CTE for current state very successfully but have only been able to use it running one day at a time in history to roll up successfully. Ideas?[/quote]I'm assuming you have history data in your database that says, for example, "Joe Salesman was under Bob Manager between 1 Jan 2008 and 29 Feb 2008; Joe Salesman was under Sally Manager between 1 March and null". "Null" indicating here that the end date is unknown because the data is still current.If so, the recursive part of the CTE needs to include the time range. Then, when you join that to the sales table, you include the time range in the join.Am I understanding the situation correctly?

RE: Recursive Queries in SQL Server 2005

john.racer — Mon, 21 Apr 2008 11:00:59 GMT

The basic business requirement is to roll sales up from employee to lead to manager to director. We have much movement in our sales organization. We want to historically calculate sale mad in history and roll up as it was at that point in time. Performing this with CTE gets tricky here as you know have people potentially in multiple places in the hierarchy for given efective dates. We use CTE for current state very successfully but have only been able to use it running one day at a time in history to roll up successfully. Ideas?

RE: Recursive Queries in SQL Server 2005

GSquared — Mon, 21 Apr 2008 06:58:49 GMT

[quote][b]itsme_dev08 (4/21/2008)[/b][hr]Hi,I tried out the example to create a recursive query(Sample 1)...i get the following error when I run the script : Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'WITH'.I am using SQL server 2005. Have I missed out on something?Thanks,Dev[/quote]That usually means you need to add a semicolon, ";", before the "with".

RE: Recursive Queries in SQL Server 2005

Anipaul — Mon, 21 Apr 2008 06:17:29 GMT

[quote][b]shamshudheen (4/21/2008)[/b][hr]hii used the given example, but i got error sayingServer: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'WITH'.i am using sql server 2000, I don't know in sql server 2000 CTE is possible or not[/quote]It is not possible in SQL Server 2000. In SQL Server 2000, this is achieved by loops traversing all the levels under the node. SQL 2005 provides the new feature Common Table Expression (CTE), which can be used to solve this request.Read this: http://www.sqlservercentral.com/articles/T-SQL/2926/

RE: Recursive Queries in SQL Server 2005

shamshudheen — Mon, 21 Apr 2008 05:13:37 GMT

hii used the given example, but i got error sayingServer: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'WITH'.i am using sql server 2000, I don't know in sql server 2000 CTE is possible or not

RE: Recursive Queries in SQL Server 2005

itsme_dev08 — Mon, 21 Apr 2008 01:00:50 GMT

Hi,I tried out the example to create a recursive query(Sample 1)...i get the following error when I run the script : Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'WITH'.I am using SQL server 2005. Have I missed out on something?Thanks,Dev

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 14:50:33 GMT

[quote][b]john.racer (4/18/2008)[/b][hr]Any Ideas on how to incorporate into historical views?[/quote]Not sure what you mean. What are you looking at doing with the historical view?

RE: Recursive Queries in SQL Server 2005

MikeAngelastro-571287 — Fri, 18 Apr 2008 13:16:41 GMT

GSquared,Thanks! I'll give that a try in one of the databases I'm working with and let you know.Mike

RE: Recursive Queries in SQL Server 2005

john.racer — Fri, 18 Apr 2008 13:16:19 GMT

Any Ideas on how to incorporate into historical views?

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 13:04:38 GMT

Mike:If I'm understanding you correctly, this should duplicate your many-to-many table (functionally):[code]create table HierarchyComplex (ID int not null,ParentID int not null,constraint PK_HC primary key (id, parentid))goinsert into dbo.hierarchycomplex (id, parentid)select 1, 0 union allselect 2, 1 union allselect 3, 1 union allselect 2, 3 union allselect 4, 0 union allselect 5, 4go;with CTE (ID, ParentID) as (select ID, null from dbo.hierarchycomplex where id = 1 union all select h2.id, h2.parentid from dbo.hierarchycomplex h2 inner join cte on h2.parentid = cte.id)select *from cte[/code]You'll see that ID 2 (presumably an FK to another table), is a child of ID 1, and a child of ID 3. That should parallel your "same branch appears multiple time". Right?The above CTE pulls the expected data from this. I get ID 2 twice, once under ID 1, once under ID 3.If I add ID 6 as a child of ID 2, then 6 also shows up in the results twice, once per instance of ID 2.Does that make sense?

RE: Recursive Queries in SQL Server 2005

MikeAngelastro-571287 — Fri, 18 Apr 2008 10:39:06 GMT

[quote][b]GSquared (4/18/2008)[/b][hr][quote][b]MikeAngelastro (3/13/2008)[/b][hr]I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?[/quote]A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.[/quote]GSquared, Thanks for your input.I did use a CTE initially. But situations arrived later where the resulting record set had too many rows. My tests indicated that the extra rows appear as soon as a branch appears more once in the table; that is, any branch can be a child in more than one product tree. The product-tree I am dealing with has this possibility and therefore I have to handle it. I searched the internet for a sample CTE that was specifically designed to handle this condition and found none. Because it turned out that the column values in the extra rows appeared to be identical to one of the original rows, I tried to use a “DISTINCT” qualifier but the CTE refused to run, even when I used the following approach:SELECT DISTINCT FROM CTEwhere “CTE” is the CTE’s record set result - extra rows and all.And even here, when rows have the same values as other rows, it does not necessarily mean they should be excluded from the result; this would happen if the same branch appears more than one in the same overall product tree. Given these results, how can the CTE be constructed in order to exclude the extra rows?Thanks,Mike

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 07:08:05 GMT

Good article, by the way.

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 07:07:18 GMT

[quote][b]MikeAngelastro (3/13/2008)[/b][hr]I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?[/quote]A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 07:05:46 GMT

[quote][b]Rabia Mansour (3/18/2007)[/b][hr]Thanks for the article.My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.I've added : It is not reasnable in this example but it is reasnable in other.How it should be done to accomplish this need.[/quote]It's just a question of building the join in the part of the CTE after the Union All.For example:[code];with FTreeCTE (Generation, ID, Parent1ID, Parent2ID, Name) as (select 1, ID, Parent1ID, Parent2ID, Name from dbo.FamilyTree union all select Generation + 1, ft2.ID, ft2.Parent1ID, ft2.Parent2ID, ft2.Name from dbo.FamilyTree ft2 inner join FTreeCTE on ft2.Parent1ID = FTreeCTE.ID or ft2.Parent2ID = FTreeCTE.ID)select Generation, ID, Parent1ID, Parent2ID, Namefrom FTreeCTEorder by Generation[/code]

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 07:01:41 GMT

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 06:57:12 GMT

[quote][b]Drew Burlingame (3/17/2006)[/b][hr]Thanks for taking the time to post this article.I'm curious as to performance compared to other methods of getting heirarchical data like adjacency, nested, etc.?[/quote]The CTE method of unrolling a hierarchy uses the adjacency model. It's just another way to handle that, rather than cursors/while loops. I've tested cursors, while loops, and CTEs for complex adjacency hierarchies, and CTEs are faster in all of my tests (up to 7,000 nodes on the hierarchy, up to 50 levels). Cursors are the slowest. A semi-set while loop is in between.Nested sets are much faster to unroll. And they require one table scan per query, instead of a number of table scans equal to the number of nodes in the the hierarchy (cursor or CTE) or a number of scans equal to the number of levels (while loop). The problem with them is if the data changes much, they are much more difficult to handle. (Nested sets hierarchies select very, very fast, but update/insert/delete much more slowly; adjacency selects more slowly, but updates/inserts/deletes very efficiently.)

RE: Recursive Queries in SQL Server 2005

GSquared — Fri, 18 Apr 2008 06:51:39 GMT

[quote][b]PW (3/9/2005)[/b][hr]The sample VB.net code for comparison contains both syntax and logic errors.In VB.Net, an IF is ended by "End If", not End.Also, " Refactored code: Private Function Factorial(ByVal number As Integer) As Integer If number = 0 Then Return 1 Else Return (number * Factorial(number - 1)) End IfEnd Function [/quote]Shouldn't the exit condition for this be "if number = 1"? Since you're multiplying by "number -1", and "1 -1" = 0, it seems like you would end up multiplying by 0, and thus getting 0 as your result every time. (It's been a while since I played with VB, so I'm not certain on this.)

RE: Recursive Queries in SQL Server 2005

TreadHead — Fri, 18 Apr 2008 06:37:05 GMT

Here's the SQL factorial:[code]With Fact (Num, Factorial)As( Select 0 as Num, convert(bigint, 1) as Factorial Union All Select Num + 1, convert(bigint, Num + 1) * Factorial from Fact Where Num + 1 <= 20 --Recursion Buster; not really necessary since Fact(21) doesn't fit in a BigInt anyway)Select * from Fact[/code]

RE: Recursive Queries in SQL Server 2005

Anipaul — Fri, 18 Apr 2008 05:49:56 GMT

Nice and wonderful article with some good examples. :)

RE: Recursive Queries in SQL Server 2005

MikeAngelastro-571287 — Thu, 13 Mar 2008 12:03:02 GMT

I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.Am I wrong in assuming that a CTE cannot handle this situation?

RE: Recursive Queries in SQL Server 2005

WilliamsJ9-664388 — Thu, 13 Mar 2008 07:43:22 GMT

I would also like to know what performance gains if any you get when using CTE's.

RE: Recursive Queries in SQL Server 2005

MikeAngelastro-571287 — Thu, 06 Dec 2007 17:36:44 GMT

Nice explanation. Assuming that a query is correctly structured, what in the data will cause extra rows in the query result. Does the recursion assume that each child has only one parent? What if this is not the case? I have a table that has a "Father" field and "Child" field. Within the table a child can have more than one father. This is different from an org chart or an employee table where a child must have only one father. Is a recursive query possible when a child can have more than one father?Thanks.

RE: Recursive Queries in SQL Server 2005

Rajesh-388763 — Thu, 16 Aug 2007 08:14:00 GMT

Hi,

One of the best example of recursive funtions in SQL. Thanks for posting such a nice example.

Raj

RE: Recursive Queries in SQL Server 2005

Rabia Mansour — Sun, 18 Mar 2007 23:39:00 GMT

Thanks for the article.

My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.

I've added : INSERT INTO dbo.SampleOrg SELECT 14, 'Senior Director - Finance', 5

It is not reasnable in this example but it is reasnable in other.

How it should be done to accomplish this need.

RE: Recursive Queries in SQL Server 2005

Satish Jha — Wed, 24 May 2006 10:44:00 GMT

Thanks for this article. I have one question here - how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order

RE: Recursive Queries in SQL Server 2005

daryabeygi — Fri, 17 Mar 2006 07:07:00 GMT

the link to the previous article is wrong, it refers to the same article.http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=213&messageid=164149I didn't know what CTE's were, glad I found out!!This was the final straw, I'm upgrading from 2000.

RE: Recursive Queries in SQL Server 2005

Drew Burlingame-306747 — Fri, 17 Mar 2006 00:35:00 GMT

Thanks for taking the time to post this article.I'm curious as to performance compared to other methods of getting heirarchical data like adjacency, nested, etc.?

RE: Recursive Queries in SQL Server 2005

PW-201837 — Wed, 09 Mar 2005 11:03:00 GMT

The sample VB.net code for comparison contains both syntax and logic errors.

In VB.Net, an IF is ended by "End If", not End.

Also, "If result = number * Factorial(number - 1)" is attempted assignment and comparison and produces an incorrect result. If you run this code, you always get zero as a result.

Refactored code:

Private Function Factorial(ByVal number As Integer) As Integer If number = 0 Then Return 1 Else Return (number * Factorial(number - 1)) End IfEnd Function

RE: Recursive Queries in SQL Server 2005

Eralper — Thu, 03 Mar 2005 01:34:00 GMT

Thanks Srinivas,

Very good description of recursive common table expressions and great sample for getting hierarchical data with a defined value of drill down levels (sample 1).

I liked the sample 1. I believe it will be very useful for many of us.

Eralper

http://www.kodyaz.com