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


SP


Author
Message
reema.m
reema.m
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 31
Comments posted to this topic are about the item SP
ashwani24
ashwani24
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1001 Visits: 122
but only at 32 levels.
Ola L Martins-329921
Ola L Martins-329921
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 188
The "Don't know" alternative should give you points, right? I mean, if you don't know, then the correct answer is actually "Don't know"... ;-)
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15612 Visits: 12326
I love the "don't know" option, and I agree with the poster above that those who rightfully tick that box should get a point. ;-)

In T-SQL, recursion is not as useful as it is in more traditional programming languages. Not only because of the 32-level restriction, but also because recursive algorithms are, by nature, iterative - and SQL Server is tuned to optimize set-based operations.
For the factorial example, the code below is not only more succinct, it also woud perform a lot better - if SQL Server actually had an aggregate PRODUCT() function (which, unfortunately, is not the case):
SELECT PRODUCT(n)
FROM dbo.Numbers
WHERE n BETWEEN 1 AND @in;



However, because there is no PRODUCT function, we have to work around that - using the mathematical fact that ex * ey = ex+y. (Note that the formula below works only for numbers above zero; there are also formulas to calculate aggregate product for sets that may include negative numbers or zero - google or bing if you need them, or ask me).
SELECT EXP(SUM(LOG(n)))
FROM dbo.Numbers
WHERE n BETWEEN 1 AND @in;


I'd argue that, with a comment thrown in to explain the EXP(SUM(LOG())) bit, this is still easier to maintain than the recursive procedure - and it'll definitely be faster!

* Note that the above is not meant to suggest that recursion has no place in SQL Server, I only wanted to point out that when possible, a set-based solution should be the first choice. There will always be situations where recursion is indeed the best solution.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
paul s-306273
paul s-306273
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3102 Visits: 1147
This article was posted in 2005 on TechRepublic:

[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15612 Visits: 12326
paul s-306273 (4/26/2012)
This article was posted in 2005 on TechRepublic:

[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]


Ouch! That level of simularity is way beyond coinicidence... AngryCryingSick
I'll shoot Steve an email now and ask him to deal with this IP infringement as soon as possible.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
paul s-306273
paul s-306273
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3102 Visits: 1147
Hugo Kornelis (4/26/2012)
paul s-306273 (4/26/2012)
This article was posted in 2005 on TechRepublic:

[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]


Ouch! That level of simularity is way beyond coinicidence... AngryCryingSick
I'll shoot Steve an email now and ask him to deal with this IP infringement as soon as possible.


Hugo - thanks for that.
kaspencer
kaspencer
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 849
If the writer of the commentary on the answer to this question were one of my students, and if he were not named Arthur Fuller, I would submit his name to a student ethics committee on the grounds of plagiarism.

As already mentioned, please read:
[url=http://www.techrepublic.com/article/using-recursion-in-stored-procedures/5700193][/url]

No obfuscation whatsoever was attempted when this piece of text was stolen. Apologies are demanded, and the writer should have all previously gained points confiscated.

Kenneth Spencer

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3413 Visits: 2256
Ola L Martins-329921 (4/26/2012)
The "Don't know" alternative should give you points, right? I mean, if you don't know, then the correct answer is actually "Don't know"... ;-)


Sometimes true wisdom is just realizing what you DON'T know. (And humility follows closely behind.)

I have never used recursive SPs. I can't think of situation where I might, given the environment in which I am. However, I absolutely love recursive CTE's, even if I could only use them for BOM (bill of materials) explosions. We had a previous implementation that used CURSORS to do them!

I have also used recursive functions. They are handy for string parsing. I imagine that recursive CTE's would do a bit better performance wise, but I find it easier to write readable/maintainable code when done as a function.

Thanks to OP for question!

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Tom Thomson
Tom Thomson
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21153 Visits: 12443
On top of the plagiarism issue (as yet unproven -"reema" may be a pseudonym of Arthur Fuller, although that seems unlikely) and the use of technical terms (recursive, recursion) of computer science and mathematics in a sense that they don't have in those arts/sciences (despite SQL Server certainly being something to do with computation) the explanation is pretty terrible.

Since SQL Server doesn't do multi-statement optimisation, it certainly doesn't do optimisation of tail-recursion; so quite clearly T-SQL procs that call themselves to implement tail-recursion should never be written since they will be gloriously inefficient - so this implementation of factorial is a fine example of something that should never be done. In addition, T-SQL doesn't actually support recursion except in a restricted form, with a restriction of 32 on nesting level ( and although this does no harm to the factorial function, which of course blows up at 21 even if it uses bigint for its result, that restriction should certainly have been mentioned.

So in general a T-SQL procedure calling itself should be used only for functions which are not easily converted to tail-recursive form (and hence to iterative form) and where it is assured that the necessary nesting level does not excede the T-SQL limit or the procedure contains tests to detect when the nesting level is getting to high and can do something useful/intelligent to avoid this (instead of just failing with the vanilla error message provided by the system). A discussion of "recursive" stored procedures which doesn't point this out is a bit like rat poison in a jam-jar without a warning label.


Note: in mathematics, in computer science, in formal logic, and in computation theory "recursive" means "able to be computed by using a Turing machine" or, equivalently, "able to be computed by using lambda calculus"; so every possible stored procedure is recursive, whether it calls itself or not. Use of the term to mean "calls itself" is fine in other contexts, but really irritating in the context of computing, as in this question and explanation. Generally it is believed that "recursive" is the same as "in principle a human being could compute it if supplied with enough paper and pencils and detailed instructions by following the instructions exactly and mechanically with no excercise of intelligence or intuition" (that is "Church's Thesis" or "Turing's Thesis" or "The Church-Turing Thesis", perhaps the most important idea in the theory of computation and certainly the most horribly misinterpreted - a decent explanation can be found here).

Tom

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