April 26, 2012 at 12:27 am
but only at 32 levels.
April 26, 2012 at 12:42 am
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"... 😉
April 26, 2012 at 1:23 am
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.
April 26, 2012 at 1:52 am
April 26, 2012 at 2:00 am
Ouch! That level of simularity is way beyond coinicidence... :angry::crying::sick:
I'll shoot Steve an email now and ask him to deal with this IP infringement as soon as possible.
April 26, 2012 at 2:06 am
April 26, 2012 at 2:34 am
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:
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
April 26, 2012 at 5:31 am
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!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
April 26, 2012 at 5:38 am
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[/url]).
Tom
April 26, 2012 at 6:07 am
Tom raises some very good issues. As always he elevates the level of the discussion.
Having followed the link suggested, curiosity got the best of me. I entered "recursion" in Standford's Encyclopedia of Philosophy search box and pressed ENTER. I got 50 hits, the first of which begins:
Recursive Functions
First published Thu Mar 24, 2005
The recursive functions, which form a class of computable functions, take their name from the process of “recurrence” or “recursion”. In its most general numerical form the process of recursion consists in defining the value of a function by using other values of the same function. In this entry, we provide an account of the class of recursive functions, with particular emphasis on six basic kinds of recursion: iteration, primitive recursion, primitive recursion with parameters, course-of-value recursion, and double recursion. We then examine some theorems relating to these types of recursion. ... (Standfords Encyclopedia of Philosophy)
Many of the other entries support Tom's position of the "pure" meaning of recursion.
However, there is just no avoiding a few simple facts:
1. The use of recursive/recursion in the sense used by OP is deeply embedded in common usage, even in those in CS inclined towards rigor of thought.
2. People know what you mean when you use the terms - most people do, anyway.
3. It really does convey the idea of recurrence very well.
(Edited to remove typo.)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
April 26, 2012 at 7:12 am
Interesting question and a few good follow on points. Thanks.
April 26, 2012 at 7:46 am
There are a number of features in T-SQL that are not ideally utilized within, but would be better if implemented in a CLR. We can add "recursion" to the list.
CLRs are not always available in all environments and implementing them has their own set of issues, which is why I appreciate having these types of feature available within T-SQL.
Sometimes the best solution isn't always the ideal one.
April 26, 2012 at 8:18 am
Allright people should know that we can use recurssion with nested procedure or nested function but i will not recommand this..
first the nesting level .. can not exceed 32
Using nested procedures can create as weel lock on the master tables it you create temp tables and this can have dramatic performances issues..
If you want to use recurssion try first to do it with CTE, here the exemple of factorial:
DECLARE @Out bigInt
DECLARE @n bigInt = 20
;WITH CTE (A, B) AS (
SELECT cast(1 as bigInt), cast(1 as bigInt)
UNION ALL
SELECT A+1, B*(A+1)
FROM CTE
)
SELECT TOP 1 @Out = B FROM CTE
WHERE A = @n
select @Out
But if you want performance for factorial,
for this type of mathematical operation, i recommend to use a tally tables or spt_values.. like you can find on google or like Hugo Kornelis post in previous comment
DECLARE @Out bigInt
DECLARE @n bigInt = 20
set @Out = 1
;with AllNumber(number)
as (
select cast(number as bigInt) from master..spt_values where type='p' and number between 1 and @n
)
select @Out = @Out * number from AllNumber
select @Out
But i you want recursion and performance CLR functions seems to be the best option..
April 26, 2012 at 8:39 am
The fact of having some feature available doesn't means that you have to do use of it. As happen with cursors, which is a feature that I have used pretty rarely, and always has been my last option to solve an issue.
On the other hand, it's so sad that somebody use the work of others to have some recognition without gives the respective credit to the author. But that isn't considered plagiarism, it's to take something that already exists and improve it. And happen all the time (Apple, Microsoft, Google, Facebook, etc...)
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply