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???
Answer by Matt Whitfield ·
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.
Answer by TimothyAWiseman ·
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.