Advertisements
Home > SQL Server Questions > How exactly STATIC CURSOR works internally – MSDN TSQL forum

How exactly STATIC CURSOR works internally – MSDN TSQL forum


–> Question:

In MSDN help mentions static cursor as follows:

STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say’s that modifications is not allowed in the static cursor.

I have a questions regarding that:

declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll

while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1

close ll
deallocate ll

In “AG” table, “SALARY” was 100 for all the entries.

When I run the Cursor, it showed the salary value as “100” correctly.

After the cursor was closed, I run the query select * from AG.

But the result had updated to salary 200 as given in the cursor.

Help file says modifications is not allowed in the static cursor. But I am able to update the data using static cursor.
 

–> My Answer:

MSDN meant that:

“After you create a STATIC Cursor if the table is updated by any other process that update will not be reflected in the Static Cursor rows.”

1. Let’s say your SPID=100 and you read the table with Salary=100.

2. You created a STATIC Cursor and read the above table with Salary=100.

3. Another SPID=105 updated that Salary=200

4. But when you will read the cursor it will still show you Salary=100.

But yes you can update the same table form the Cursor itself as you are doing above. Check this MSDN link for clear info: https://msdn.microsoft.com/en-us/library/windows/desktop/ms678392%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396
 

Ref Link.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: