Cursor

Question

Post reply

Cursor

senthil_gobi04

Newbie

Points: 9
More actions
October 18, 2008 at 5:52 am

#194743

How to use update Query using cursor in SQL Server

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

If you must use a cursor Refer to books on line for 2 methods:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dataacc9/html/ff404e02-630f-474d-b5d4-06442b756991.htm

Remember a cursor is an agonizing row by agonizing row operation it would be more efficient to use a set based procedure.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

senthil_gobi04 (10/18/2008)
How to use update Query using cursor in SQL Server

Don't.

Post the requirements for your update (table definitions, sample data, desired results), and we can help you write an update that doesn't use a cursor.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

senthil_gobi04,

Just to add to the concensus forming here, about the only thing you really need to know about cursors is how to stay away from them. Even a nice Read Only Fast Forward cursor can be 10's to thousands of times slower than properly written set based code.

I agree with Gail... post your requirements using the methods found in the link below in my signature, and the chances are pretty darned good we can show you a way to do it without the slothfulness of a cursor.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I wish I didn't have to know how. While something like this is necessary in procedural code - it really has no place in SQL code in my mind. Beside the slowness factor - they also have a tendency to create large amounts of locking on tbles, which then block all sorts of other things etc....

Do like Ronnie said - Just Say No.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?