T-SQL Cursors

Cursors are line-oriented, which makes developers lazy and lazy to implement certain functions with a collection-oriented query.
In performance, cursors can be delayed by more memory, reduce available concurrency, consume bandwidth, lock resources, and, of course, more code.
Use a metaphor to illustrate why cursors consume more resources. When you withdraw from an ATM, is it more efficient to take 1000 or 10 times 100?

since cursors have so many drawbacks, why learn cursors?

1. Existing systems have some cursors, and our queries must be implemented by cursors.
2. An alternate way to use cursors when using while, subqueries, temporal tables, table variables, self-built functions, or other methods to implement certain queries is still possible.

Definition syntax for cursors:

DECLARECursor_nameCURSOR [LOCAL | GLOBAL]      [Forward_only | SCROLL]      [STATIC | KEYSET | DYNAMIC | Fast_forward]      [Read_Only | Scroll_locks | Optimistic]      [type_warning]       forselect_statement[For UPDATE [of column_name [,... n] ] ][;]

the life cycle and implementation of T-SQL Midstream target

1. Define a cursor
In T-SQL, defining a cursor can be either very simple or relatively complex, depending primarily on the parameters of the cursor. The parameter setting of the cursor depends on how well you know the cursor principle.
A cursor can actually be interpreted as a pointer defined on a particular set of data, and we can control whether the pointer traverses the dataset or simply points to a particular row, so the euro is defined on the dataset starting with select.

　　

Cursors are divided into cursor types and cursor variables, which, for cursor variables, follow the definition of T-SQL variables. Cursor variables support two ways of assigning values, defining the assignment and defining a post-assigned value, defining a cursor variable like defining other local variables, adding "@" before the cursor, noting that if a global cursor is defined, it can only be assigned directly when defined, and cannot precede the cursor name with "@", both of which are defined as follows:

--Direct copy after definition DECLARE CURSOR  for SELECT *  from  Person --Define and copy first DECLARE @TEST_Cursor2 CURSOR SET @test_Cursor2 = CURSOR  for SELECT *  from Person　　　　　　　　　　

Parameter explanation:

　　1, local and global two select one

--Global cursors, across globalDECLARETest_cursorCURSORGLOBAL for        SELECT *  from Person--local cursors, across localDECLARETest_cursor2CURSORLOCAL forSELECT *  from Person--use go to end the scope aboveGOOPENTest_cursorOPENTest_cursor2--This line code error, reported that the cursor does not exist, so you can understand that the local cursor does not cross batches, after the end of the batch, will be implicitly released, cannot be called in other batches

If you do not specify a cursor scope, the default scope is global.

2, Forward_only and SCROLL two select one

Forward_only means that the cursor can only be read from the beginning of the dataset to the end of the dataset, and FETCH Next is the only option, while the scroll supports the cursor moving in any direction, or anywhere, in the defined data set.

--no parameters, default to Forward_onlyDECLARETest_cursorCURSOR  forSELECT *  from Person--Add forward_onlyDECLARETest_cursor2CURSORForward_only forSELECT *  from Person--Add scrollDECLARETest_cursor3CURSORSCROLL forSELECT *  from PersonOPENTest_cursorOPENTest_cursor2OPENTest_cursor3FETCHLast fromTest_cursor--Error FETCH: The last fetch type cannot be used with a forward-only cursor. FETCHLast fromTest_cursor2--Error FETCH: The last fetch type cannot be used with a forward-only cursor. FETCHLast fromTest_cursor3--Correct execution

　　3, STATIC KEYSET DYNAMIC and Fast_forward four select one

These four keywords are the relationship between table data and cursor read-out data reflected by the data set where the cursor resides

• STATIC: When a cursor is established, a copy of the dataset contained in the SELECT statement that follows for is created in the tempdb database, and any changes to the data in the underlying table do not affect the cursor content.
• DYNAMIC: The exact opposite of the static option, when the underlying database changes, the contents of the cursor are also reflected, and in the next fecth, the data content will be more loved.
• KEYSET: Can be understood as a compromise between static and dynamic, the only time the cursor is in the result set to determine the primary key of each row is stored in tempdb, when any row in the result set is changed or deleted, @ @FETCH_STATUS will be-2, Keyset cannot detect the newly added data.
• Fast_forward: An optimized version that can be understood as forward_only. Forward_only performs a static plan, and Fast_forward chooses whether to use a dynamic or a static plan depending on the situation, and in most cases fast_forward is slightly better than forward_only performance.

　　4, Read_Only Scroll_locks optimistic three choose one

• Read_Only: means that the declared cursor can only read data, and the cursor cannot do any update operations
• Scroll_locks: Another extreme, locking all data that is read into the cursor, preventing other programs from making changes to ensure the absolute success of the update.
• Optimistic: A relatively good choice, optimistic does not lock any data, when the data needs to be updated in the cursor, if the underlying table data is updated, the data update in the cursor is unsuccessful, if the underlying table data is not updated, the table data in the cursor can be updated.

Open Cursor

After the cursor is defined, the cursor needs to be opened for use, and the cursor can be opened in a single line of code:

OPEN Test_cursor

Note that when a global cursor and a local cursor variable have the same name, the local variable cursor is opened by default.

using Cursors

The use of cursors is divided into two parts, one for manipulating cursors in the dataset and the other for manipulating some or all of the rows that the cursor points to.
Only support 6 to move Xuan Hang, respectively, the first row, the last line, the next line (next), the previous row (PRIOR), jump directly to a row (ABSOLUTE (n)), with respect to the current number of hops (RELATIVE (n)).

For example:

DECLARETest_cursorCURSORSCROLL for 　　 SELECTName from PersonOPENTest_cursorDECLARE @c NVARCHAR(Ten)--Remove one line 　　 FETCH NEXT  fromTest_cursor into @c 　　 PRINT @c 　　 --Take the last line 　　 FETCHLast fromTest_cursor into @c 　　 PRINT @c--　　take the first line 　　 FETCHFirst fromTest_cursor into @c 　　 PRINT @c 　　 --Take a line 　　 FETCHPRIOR fromTest_cursor into @c 　　 PRINT @c 　　 --Take the third line 　　 FETCHABSOLUTE3  fromTest_cursor into @c 　　 PRINT @c 　　 --Take the previous line relative to the current 　　 FETCHRELATIVE-1  fromTest_cursor into @c 　　 PRINT @c

　　For cursors that do not specify the scroll option (no forward-only cursors are specified), only next values are supported.

cursors are often used in conjunction with the global variable @ @FETCH_STATUS with a while loop to achieve the purpose of traversing the data set where the cursor resides.

For example:

DECLARETest_cursorCURSORSCROLL for 　　 SELECTId,name from PersonOPENTest_cursorDECLARE @id intDECLARE @name NVARCHAR(Ten) while @ @FETCH_STATUS = 0 　　 BEGIN 　　 PRINT @id 　　 PRINT @nem 　　 FETCH NEXT  fromTest_cursor into @id,@name 　　 END CLOSE test_cursor　　deallocateTest_cursor

Close Cursors

After the cursor is used, be sure to remember to close, just one line of code: close+ cursor Name

CLOSE Test_cursor

Releasing Cursors

When a cursor is no longer needed to be used, the cursor is freed, requiring only one line of code: deallocate+ cursor Name

deallocate Test_cursor

Some optimization suggestions for cursors

• If you can avoid cursors, try not to use cursors
• Be sure to close and release after you run out of use
• Try not to define cursors on large amounts of data
• Try not to update the data using cursors
• Try not to use insensitive, static, and keyset to define cursors
• If possible, use the Fast_forward keyword to define the cursor
• If only the data is read, it is best to use the Forward_only parameter when reading only with the FETCH next option

To the life cycle to talk about cursors. Cursors are a very evil existence, and using cursors is often 2-3 times slower than using a set-oriented approach, which increases when the cursor is defined on a large amount of data. If possible, use while, subqueries, temporary tables, functions, table variables, and so on to replace the cursor, remembering that the cursor is always your last choice, not the preferred one.

T-SQL Cursors