Advantages of Cursors

Question

Advantages of Cursors

suraj.app

SSC-Addicted

Points: 433
More actions
August 31, 2010 at 2:13 am

#387700

Hi,
What is advantage of cursors. What ever we can do with cursors can also be done using while loops, temp tables.
So, what is the advantage of using cursors vs the other looping techniques.
Regards,
Suraj

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

suraj.app (8/31/2010)
Hi,
What is advantage of cursors. What ever we can do with cursors can also be done using while loops, temp tables.
So, what is the advantage of using cursors vs the other looping techniques.
Regards,
Suraj

A while loop is no better, and frequently worse than a cursor.

I agree that since SQL 2005 was released, that 95-99% of all cursors can be eliminated. However, there are still SOME uses for them. For me, most of these fall into the maintenance/housekeeping category.

For example, say I wanted to add a new column to every table in my database. Obviously, I have to do a separate alter table statement for each table. I'd use a cursor for this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

suraj.app SSC-Addicted Points: 433 More actions · Answer 2

Hi,

Thanks for the reply.

But what about infinite looping error. I feel cursors cant have the infinite looping error.

Please advice.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Any looping code can have an "infinite looping error". After all, that is just bad programming.

The real solution is to remove all loop processing, and to convert it to set-based processing. It will perform a LOT better.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GSquared SSC Guru Points: 260824 More actions · Answer 4

For a number of things, a "local fast_forward" cursor can be much faster than other looping methods. I use these to generate scripts that can be run dynamically, like Wayne's example of scripting a table modification for a whole database (or even a whole server).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon