A brief analysis of the differences between Fast_forword and srroll in SQL Server

This is a brief talk about the classification of cursors.

First look at the syntax of the usual cursor

 DECLARE  cursor_name  cursor 
 [Local: Local cursor, valid only for current session 
 | Global cursor, globally valid, can 
] [forward_only: Forward-only cursor, only use  Next  verb when reading cursors 
 | SCROLL: Scroll cursors,  First, last, PRIOR, NEXT, RELATIVE, ABSOLUTE  can all use 
] [STATIC: Define a cursor to create the data that will be used by the cursor Temporary copy. All requests to the cursor are answered from this temporary table in  tempdb ; 
 | KEYSET: Changes made to non-key values in the base table (changed by the cursor owner or submitted by another user) can be seen when the user scrolls the cursor. Insertions performed by other users are not visible (cannot be inserted through a Transact-SQL Server cursor). If you delete a row, the @ @FETCH_STATUS with a value of 2 is returned when you try to fetch the row. 
 | DYNAMIC: Defines a cursor that reflects all data changes made to the rows in the result set when the cursor is scrolled. The data values, order, and membership of the rows are changed on each fetch. Dynamic cursor does not support ABSOLUTE extraction option 
 | Fast_forward: Specifies Forward_only, READ_ONLY cursors with performance optimizations enabled.       If SCROLL or for_update is specified, you cannot also specify Fast_forward 
] [read_only: Read-only cursor, cannot make changes to the cursor contents, cannot use the WHERE CURRENT of statement 
 | Scroll_locks: Specifies that positioning updates or deletions through cursors will succeed. When a row is read into the cursor, SQL Server locks the rows to ensure that they can subsequently be modified. 
 | Optimistic: Specifies that if the row has been updated since it was read into the cursor, locating updates or location deletions through the cursor are unsuccessful. When a row is read into a cursor, SQL Server does not lock the row 

     ]      [type_warning]      select_statement      column_name,... n]]  : Making those columns can be updated, If not, all updates are available by default
     ]

Well, after the bag, and now the problem, I try to describe the characteristics of each type of illustrations ... _ (: З"∠) _

--------------------------------------------------------------I'm a split line------------------------------------------------------ -----------------------------------------------------------------------------------------
Structure of the test table

CREATE TABLE [dbo].[Employee]([ID] [int]  not NULL IDENTITY(1,1)Primary Key,[NAME] [nvarchar]( -)NULL,[Name2] [varchar]( -)NULL)  on [PRIMARY]GO

Forward_only  and SCROLL
   The two are still a good difference in the use of the process. One can only go forward, one may forward roll after the rollover or something.
Look first.

Forward_only

DECLARECr_cursorCURSORFast_forward--this cursor, is a read-only cursor Oh ~ for    SELECTID, NAME fromdbo. EmployeeWHEREId>=  -    DECLARE @ID INT ,    @Name NVARCHAR( -)    OPENCr_cursorFETCH NEXT  fromCr_cursor into @ID,@Name while @ @FETCH_STATUS = 0     BEGIN    --UPDATE dbo. Employee SET Name2 = @Name WHERE Current of Cr_cursor because it is a read-only cursor, it is not allowed to modify the contents of the cursor itself        UPDATEdbo. EmployeeSETName2= @Name        WHEREId= @ID        FETCH NEXT  fromCr_cursor into @ID,@Name    ENDCLOSECr_cursordeallocateCr_cursor

Then we look at the execution plan, which is no different from the normal instant query, so I guess that if the data changes during cursor reading, it can be obtained. Let me verify this.

DELETE from dbo. Employee WHERE ID = 25

2, and then in the read cursor to open one-step debugging, read the ID = 24 node

3, and then before I read the next cursor, I added a piece of data

SET Identity_insert  on INSERT  into dbo. Employee        (id,name)VALUES  , N' I was inserted into the small three ')  SETidentity_insertOFF

Then continue F10 forward ~ ~~25 out of the wild ~

It is proved that the forward_only is not even getting the data when the cursor scrolls down. So you can capture the newly inserted or deleted data.


This is roughly the paper.

SCROLL

DECLARECr_cursorCURSORSCROLL--The code is basically the same, just replaced by SCROLL for    SELECTID, NAME fromdbo. EmployeeWHEREId>=  -    DECLARE @ID INT ,    @Name NVARCHAR( -)    OPENCr_cursorFETCH NEXT  fromCr_cursor into @ID,@Name while @ @FETCH_STATUS = 0     BEGIN        UPDATEDbo. EmployeeSETName2= @Name WHERE  Current  ofCr_cursor--This sentence can now be executed.FETCH NEXT  fromCr_cursor into @ID,@Name    ENDCLOSECr_cursordeallocateCr_cursor

Let 's take a look at this execution plan.


You can see that there is a query procedure to insert data into the Cwt_primarykey temporary table. I guess that if there is an increase in data outside of the cursor reading, it is not available, what if the update and delete? Experiment.

DELETE from dbo. Employee WHERE ID = 25

2, and then in the read cursor to open one-step debugging, read the ID = 24 node

3, and then before I read the next cursor, I added a piece of data

SET identity_insert Employee ONINSERT into dbo. Employee        (id,name) VALUES  (25,n ' I was plugged in small three ') SET identity_insert Employee OFF 

Then F10 ... ID25 No Thick Line _ (: З"∠) _ went directly to 26

It is true that external data has been added and is not available. Here's a look at the modifications and deletions.

1, and then in the read cursor to open one-step debugging, read the ID = 24 node

2, the change ID is 25 data

UPDATE dbo. Employee SET NAME = ' I am the modified ID25 Oh ' WHERE ID = 25

3, and then press F10 continue to go, is can be obtained yo ~

Delete it?
Try again, in a single step of the process to erase the ID = 25 of the data, and then the direct loop is finished ~ Check the @ @Fetch_Status = 2 Failed to extract data, of course ... The data has been deleted. Incidentally, if you continue to take it, you can still take the next piece of data yo ~
...... The picture is not cut ...

And then continue to try the various ways and then add

A brief analysis of the differences between Fast_forword and srroll in SQL Server