SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive Queries in SQL Server 2005


Recursive Queries in SQL Server 2005

Author
Message
Srinivas Sampath
Srinivas Sampath
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 1
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
Eralper
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 466

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



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
PW-201837
PW-201837
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9295 Visits: 1228

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
Drew Burlingame-306747
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 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
daryabeygi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 5
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
Satish Jha
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 16
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
Rabia Mansour
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 80

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
Rajesh-388763
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 10

Hi,

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

Raj


MikeAngelastro-571287
MikeAngelastro-571287
SSC Eights!
SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)

Group: General Forum Members
Points: 858 Visits: 177
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
WilliamsJ9-664388
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 53
I would also like to know what performance gains if any you get when using CTE's.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search