Hi,Im a junior level SQl developer/Admin.Can anybody explain where exactly we can use cursors in T-SQL programming and what are the benifits when we compare cursor with simple DML statements.Can anybody help plz???
asked
Where excatly we can use cursor in T-SQL programming??
There are rarely any benefits to cursors. They will almost always perform extremely poorly compared to a well designed DML statement.
So, don't spend too much time focusing on them. Focus on getting the job done with DML instead.
Agreed. There are rare occasions where a cursor or loop is a good solution (they do exist). Focus on set-based solutions.
As Matt said, there is very rarely a reason to use cursors in T-SQL. Oracle is a different animal that handles cursors better, but even there I would use them only with great care. Even in Oracle, cursors will very often be slower than the purely DML based solution, and often harder to read and maintain.
I do frequently see use for some purely procedural code inside of SQL though. For instance, if it needs to import a set of files, I may use a while loop to get to each file.
Also, at the risk of some disagreeing with me vehemently, let me point out that in most organizations in the modern era programmer time is more expensive than processor time. If you can come up with a decent procedural solution with cursors or a loop that runs in an acceptable time frame, and think that figuring out a way to do it with set based solution would take 5x times as long, you may want to go with the procedural solution.
I know some will object that the procedural code will not scale, and they would be right. But sometimes you genuinely know that the data set it runs over will not grow dramatically ever, or that by the time it grows enough for it to matter a major rewrite of the whole system will have occurred anyway, and sometimes you are just plain rushing against a deadline.
So, to summarize, you should generally avoid cursors and indeed all loops inside of T-SQL as a rule, but you may need them if the script works with entities outside of the database itself and you may occasionally find it more pragmatic to use them in some very rare case.
>>>Also, at the risk of some disagreeing with me vehemently, let me point out that in most organizations in the modern era programmer time is more expensive than processor time. Yeah... Sorry, Timothy. I do have to disagree with that. It takes less time to write set based code than it does cursor code. If you're going to have to pay for developers anyway, you might as well get the ones that know what they're doing. I'd never go with the procedural solution because the rework when it breaks in scalability will cost way too much. Do it right the first time every time.
If Donald Knuth said that, then I guess I also vehemently disagree with him. ;-) To coin my own phrase, "Premature use of non-optimized code is the root of all performance problems and future rework." Why would anyone ever intentionally do things the wrong way? Before you answer that, remember that post release rework costs more than 8 times the original cost of writing the code.
Well, at over a year later it might be a little late to answer this, but I came back over it today. No sane person is going to intentionally do things the wrong way, but there are lots of reasons you may happily trade an inefficiency in on area to gain an effeciency in another. Very frequently I write code that is only ever going to be run once to generate one result. While I certainly try to write that effeciently the first time, I do not spend a lot of time optimizing it. The same holds true for once yearly reports. Also, frequently I am under strict deadlines. I may happily choose a less effecient implementation that I can write quickly rather than meticulously optimizing. And finally, I will happily use less effecient code taht is more readable in many cases. Now, with all that said I have not used a single cursor for many years and I do try to keep effeciency as a thought even on my first draft of any code. But I also very rarely go back and rewrite my python code in C even though I know just writing it in C will gain a substantial performance gain. I trade the raw performance of C for the readability and speed of coding in Python frequently.
