x

Where excatly we can use cursor in T-SQL programming??

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???

more ▼

asked Mar 11, 2010 at 05:18 PM in Default

venkat reddy Ravu gravatar image

venkat reddy Ravu
59 2 3 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Mar 11, 2010 at 06:06 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

Agreed. There are rare occasions where a cursor or loop is a good solution (they do exist). Focus on set-based solutions.
Mar 11, 2010 at 06:15 PM CirqueDeSQLeil
Short and to the point.
Mar 11, 2010 at 07:57 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 11, 2010 at 07:54 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 20 23 32

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.
May 09, 2010 at 12:37 AM Jeff Moden
I knew some would disagree, and I knew you would be one of them, I just wasn't sure if you would comment on this thread ;-) I see your viewpoint, and in general I agree. Inside SQL, set based code is generally better. But to quote Donald Knuth, "Premature optimization is the root of all evil." There are times (*rare!*) when it makes sense to use a set based solution either as the solution or as a stepping stone to a more optimized solution.
May 09, 2010 at 04:38 AM TimothyAWiseman
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.
Jun 30, 2010 at 03:50 PM Jeff Moden

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.
Jul 29, 2011 at 09:49 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x59
x6

asked: Mar 11, 2010 at 05:18 PM

Seen: 3360 times

Last Updated: Mar 11, 2010 at 06:12 PM