Recursive Queries in SQL Server 2005

  • Srinivas Sampath

    SSChasing Mays

    Points: 606

    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

  • Eralper

    SSCarpal Tunnel

    Points: 4438

    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

     

  • PW-201837

    SSC-Insane

    Points: 20805

    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

  • Drew Burlingame-306747

    Grasshopper

    Points: 13

    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.?

  • daryabeygi

    Grasshopper

    Points: 14

    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.

  • Satish Jha

    SSC Rookie

    Points: 30

    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

  • Rabia Mansour

    Grasshopper

    Points: 19

    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.

  • Rajesh-388763

    SSC Veteran

    Points: 279

    Hi,

     

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

     

    Raj

  • MikeAngelastro-571287

    SSCommitted

    Points: 1576

    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.

  • WilliamsJ9-664388

    SSC Journeyman

    Points: 82

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

  • MikeAngelastro-571287

    SSCommitted

    Points: 1576

    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?

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice and wonderful article with some good examples. 🙂

  • TreadHead

    SSC Journeyman

    Points: 92

    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

  • GSquared

    SSC Guru

    Points: 260824

    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

  • GSquared

    SSC Guru

    Points: 260824

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

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