Difference between Client cursor and server cursor

Question

Post reply

Difference between Client cursor and server cursor

karthik M

One Orange Chip

Points: 29139
More actions
October 31, 2007 at 12:59 am

#75805

Hi Experts,;)
I want to know the difference between client cursor and server cursor.Where exactly the server side cursor is used ? where exactly the client cursor is used ?
Can anyone give me a clear idea about both the cursors ?
Regards:cool:
Karthik
karthik

Viewing 7 posts - 1 through 6 (of 6 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

November 5, 2007 at 5:46 am

#748192

Experts ! Kindly clarify my doubts

karthik

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

The difference between the two is pretty straightforward:

Server side cursor - data remains on the server until requested by the client application; results are kept on the server and only returned to the client as necessary.

Client side cursose - all data is sent to the client usually resulting in higher network traffic, latency, etc.

Now as far as which one to use, the answer is a firm "it depends".

Joe

karthik M One Orange Chip Points: 29139 More actions · Answer 3

so do we need to use the same structure for both the cursors ?

Stucture :

Declare cursor

Open cursor

fetch

close cursor

Deallocate cursor

am i correct ?

karthik

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 4

karthikeyan (11/6/2007)
so do we need to use the same structure for both the cursors ?
Stucture :
Declare cursor
Open cursor
fetch
close cursor
Deallocate cursor
am i correct ?

The example/code above is code for a classic server side T-SQL cursor - a client side cursor isn't created by T-SQL other than to select data for inclusion into the resultset that is returned to the client.

For example, in c# the entire sqlclient library creates and manipulates client side cursors...

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Ok Joe, I agreed your point.

My question is is there any techincal difference between client cursor and server cursor ?

If yes , how to write server side cursor ? do we have acquire any special programming knowledge to write a server side cursor ?

karthik

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 6

Karthik -

Your sample code is a good start, basically what you're trying to do is avoid bringing a whole lot of data down to the client for further processing if you can manage to keep the work at the server by using a cursor, stored procedure, etc.

For example a bad use of a client cursor would be to pull down all of the customer in your database and then iteratively update each of them (say change a date or status) and then send those changes back to the database via a series of update statements (a for next loop would be a good example). The same processing could be done with a server side cursor (e.g. declare cursor for) which would be a server side cursor. Even better would be to figure out how to perform the same update without using a cursor at all (e.g. update customers set status = 'X' where...).

Any time you bring data down to your client application there is additional network overhead as the data is streamed from the database server to the client and then again when you send updates back to the database server...