Recursive Queries in SQL Server 2005
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
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
-
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 If
End Function
-
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.?
-
the link to the previous article is wrong, it refers to the same article.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=213&messageid=164149
I didn't know what CTE's were, glad I found out!!
This was the final straw, I'm upgrading from 2000.
-
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
-
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.
-
Hi,
One of the best example of recursive funtions in SQL. Thanks for posting such a nice example.
Raj
-
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.
-
I would also like to know what performance gains if any you get when using CTE's.
-
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?
-
Nice and wonderful article with some good examples. 🙂
-
Here's the SQL factorial:
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
-
PW (3/9/2005)
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 If
End Function
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.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Drew Burlingame (3/17/2006)
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.?
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.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply