Use of the SQL Server midstream label

Use of the SQL Server midstream label

1. Cursors are row reads and consume more resources than SQL
2. Cursor Usage Scenarios:
A cursor is used in the existing system, and the query must be implemented by a cursor
Use cursors when you have exhausted the while, subquery temporary tables, table variables, custom functions, and other methods that still cannot be implemented
The life cycle of the 3.t-sql is made up of 5 parts.
Define cursor: Cursor definition follows T-SQL definition method, assignment there are two methods, define the value, and define the post-assignment, define the cursor as defined by the other local variables in front to add @, note if it is a global cursor, only support the definition of direct assignment, and cannot precede the cursor to add @
--Assign values directly when defining
Declare Test_cursor Cursor for
SELECT * FROM DBO.TB1

--Define the post-assignment value first
Declare @test_Cursor2 Cursor
Set @test_Cursor2 =cursor for
SELECT * FROM DBO.TB2

Local and global
Local means that the life cycle of a cursor is visible only in batches or functions or stored procedures, while global means that cursors are globally valid for a particular connection as a context
--Direct assignment after definition (global)
Declare test_cursor Cursor GLOBAL for
SELECT * FROM DBO.TB1
--(partial)
Declare Test_cursor2 Cursor LOCAL for
SELECT * FROM DBO.TB2
--use go to end the scope above
GO
Open Test_cursor
Open Test_cursor2

Error: Test_cursor2 does not exist

Note: If you do not specify a cursor scope, the default is global
Forward_only and scroll two choose one
Forward_only means that cursors can only be read from the beginning of the dataset toward the end of the dataset, with the unique option fetch NEXT, while the scroll supports cursors moving in any direction or position on the defined data set
--No parameter defaults to Forward_only
Declare Test_cursor Cursor for
SELECT * FROM DBO.TB1
--Add Forward_only
Declare Test_cursor2 Cursor forward_only for
SELECT * FROM DBO.TB1
--Add Scroll
Declare Test_cursor3 Cursor Scroll for
SELECT * FROM DBO.TB1

Open Test_cursor
Open Test_cursor2
Open Test_cursor3
Fetch last from Test_cursor
Fetch last from Test_cursor2
Fetch last from test_cursor3--read final line

Error: Forward-only cursor test_cursor cannot be used with last, forward-only cursor Test_cursor2 cannot be used with last
Static Keyset Dynamic and Fast_forward four select one
These four keywords are the relationships in which the data in the table that the cursor is in is reflected in the data and the tables are read out of the data.
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 do not affect the contents of the cursor
Dynamic: Is the exact opposite of static, and when the underlying database changes, the contents of the cursor stay in the show, and the data content changes in the next fetch.
Keyset: Can be understood as a compromise between static and dynamic. Shine The only result set that determines the primary key for each row is stored in tempdb, and when any row in the result is changed or deleted, the @ @FETCH_STATUS is -2,keyset unable to detect the newly added data
Fast_forwaed can be understood as an optimized version of Forward_only, Forward_only executes a plan, and Fast_forward chooses whether to use a dynamic or a static plan depending on the situation, in most cases fast_ Forward is slightly better than forward_only performance.
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: is another extreme that locks all data that is read into the cursor, preventing other programs from making changes to ensure the absolute success of the row
Optimistic: is a relatively good choice, optimistic does not lock any data, when the data needs to be updated in the cursor, if the underlying data is updated, the data update in the cursor is not successful, if the underlying table data is updated, the cursor south table data can be updated
--Open cursors: 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
-Use cursors: Cursors are 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 is straight to.
Only 6 move options are supported, to the first row, the last line, the next line (next), the previous line (PRIOR), jump directly to a row (ABSOLUTE (n)), and a few rows (RELATIVE (n)) relative to the current
--You must specify scroll otherwise only the next forward option is supported
DECLARE Test_cursor SCROLL for
SELECT * FROM DBO.TB1

OPEN Test_cursor
DECLARE @c nvarchar (10)
--Take off one line
Fetch next from Test_cursor to @c
Print @c
--Take the last line
Fetch last from Test_cursor to @c
Print @c
--Take the first line
Fetch first from Test_cursor to @c
Print @c
--Take a line.
Fetch prior from Test_cursor to @c
Print @c
--Take the third line
Fetch Absolute 3 from Test_cursor to @c
Print @c
--take relative to the current line (-2 compared to the current 2 lines upward, 2 for the current move downward 2 lines)
Fetch relative-1 from Test_cursor to @c
Print @c
For the specified scroll, use the next option only
Cursors are often used in conjunction with the global variable @ @FETCH_STATUS and the Whitle loop, never traversing the data set where the cursor resides
Declare test_cursor Cursor Scroll for
Select Id,name from Dbo.tb1
Open Test_cursor
Declare @id int
Declare @name nvarchar (10)

While @ @FETCH_STATUS =0
Begin
Print @id
Print @name
Fetch next from Test_cursor to @id, @name
End

Close Test_cursor
Deallocate test_cursor
-Close cursor: close test_cursor
-Release cursor: deallocate test_cursor

Note 1:
The complexity of the definition of a cursor is related to the parameter, and the cursor's parameter setting is how well the cursor is understood.
Cursors: Cursors are pointers that are defined on a particular set of data, and we control whether the pointer traverses the dataset or simply points to a specific row, so the cursor is defined on the dataset starting with select.


NOTE 2:
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

Use of the SQL Server midstream label