SQL Server about Cursors

A collection of ways of thinking about queries for SQL
For cursors: The way of thinking is line-oriented

Performance: Cursors eat more memory, reduce visible concurrency, lock resources, and more

When you have exhausted the while loop, temporal tables, table variables, self-built functions, or other ways still unable to implement some queries, consider using cursors

The life cycle of a cursor consists of 5 parts:

Cursors can be very simple or complex, depending on the parameters of the cursor

A cursor can be understood as a pointer defined on a dataset that can be controlled to traverse a dataset or just a specific row, so a cursor is defined on a dataset that starts with select

The definition of a cursor:

DECLARE cursor_name cursor [LOCAL |  GLOBAL]      | SCROLL]      | KEYSET | DYNAMIC | Fast_forward]      | Scroll_locks | optimistic]      [Type_warning]      For select_statement      [for UPDATE [of column_name [,... N]] [;]

Cursors are divided into: cursor types and cursor variables,
Cursor variables follow the definition of T-SQL variables, support two ways to assign values, assign values when defined, and assign values before they are defined
If you define a local cursor: add @ before the cursor
If a global cursor is defined, it is only supported when defined, and cannot precede the cursor name with the @
For example:

--* from AuthTokenas at----= * from AuthToken as at

Parameters of the cursor:
Local and global two select one

Local: means that the lifetime of the cursor is visible only in batches or functions or stored procedures.
Global: Indicates that the cursor is in the context of a particular connection and is valid globally

Global cursors: Still valid after batch processing,
Local cursors: Implicitly freed after batch completion, cannot be called in other batches

--* * from AuthToken as at

If not specified, the default is global

Forward_only and scroll two choose 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.
Scroll supports cursors moving in any direction, or anywhere, in a defined data set

For example:

--* from AuthTokenas in--* from AuthTokenas in--* from AuthToken as AtO PEN test_cursoropen test2_cursoropen test3_cursor-- only supports moving from the beginning of the dataset to the end direction of the fetch next from Test_cursorfetch next From Test3_cursor--SCROLL supports moving the fetch NEXT from Test2_cursorfetch to  test2_cursor in any direction

Static,keyset, dynamic and Fast_forward four, select one

These four parameters are the relationships between the data in the table and the data that the cursor reads out of the data set that the cursor is in.

Static: means that 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 will not affect the contents of the cursor

Dynamic: In contrast to static, when the contents of the underlying database table change, the contents of the cursor change, and the contents of the data are changed in the next fetch.

Keyset: This is the compromise between the two above: the only time the cursor is in the result set to determine the primary key for each row is stored in tempdb, and when any row in the result set is changed or deleted, @ @Fetch_status will not be able to probe the newly added data for -2,keyset

Fast_forward: is an optimized version of Forward_only, Forward_only performs a static plan,
And Fast_forward is based on the choice of dynamic planning or static planning,

Read_Only, Scroll_locks,optimistic, three-choice one
Read_Only: means that the declared cursor can only read data, and the cursor cannot do any update operations
Scroll_locks: Locks All data that is read into the cursor to prevent other programs from making changes to ensure the absolute success of the update

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, and if the underlying table data is not updated, the table data in the cursor can be updated

To open a cursor:

When the cursor is defined, it needs to be opened before it can be used
Open Test_cursor

Note: When a global cursor and a local cursor variable have the same name, the local variable cursor is opened by default

3 Using cursors:

The use of cursors is divided into two parts:
Part is the pointer to the action cursor within the dataset,
Part of the action is to manipulate some or all of the rows that the cursor points to

Supports 6 mobile options
To line one: first
Final line: Last
Next line: Next
Previous line: Prior
Jump directly to a line: Absolute (n)
Skip a few lines relative to the current (relative (n))

For cursors that do not specify the scroll option, only the next value is supported

For example:

deallocate test_cursor--defines a global cursor and supports moving declare test_cursor cursor SCROLL forselect c.nickname from dbo in any direction. Customer as C--after you have defined the cursor open test_cursordeclare @a NVARCHAR ( -)--using cursors: Take the first row of data to @afetch-Test_cursor into @aPRINT @a--Fetch The nth row of data at the current position (take absolute position) fetching ABSOLUTE3From test_cursor to @aPRINT @a--take the relative position fetch RELATIVE3From test_cursor to @aPRINT @a--FETCH next row of data from the current location test_cursor into @aPRINT @a--take the last data fetch from Test_cursor to @aPRINT @a--Fetch the previous row of the cursor's current position fetch PRIOR from test_cursor to @aPRINT @a--you need to close the cursor after the cursor is used close Test_cursor--If you no longer need a cursor, you can delete deallocate test_cursor

Cursors are often used in conjunction with the global variable @ @Fetch_status with a while loop to achieve the purpose of iterating over the data set where the cursor is located

For example:

DECLARE test_cursor Cursor SCROLL forselect c.id,c.nickname from Customer as COPEN test_cursordeclare @i intdeclare @n Ame NVARCHAR (0BEGIN    print @i    print @name    FETCH NEXT from Test_ Cursor into @i, @nameENDCLOSE test_cursordeallocate test_cursor

Note Points using cursors:

1 cursors do not need to use cursors as much as possible
2 Be sure to close and release after use
3 Try not to define cursors on large amounts of data
4 Try not to update data using cursors
5 Try not to use Insensitive,static,keyset to define cursors for these parameters
6 if possible, use the Fast_forward keyword to define the cursor
7 If the data is read only, it is best to use the Forward_only parameter when reading only the FETCH next option

SQL Server about Cursors