SQL Server cursor usage/shutdown/release/optimization Summary _mssql

The cursor is evil!

In relational databases, our thinking about queries is set-oriented. The cursor breaks this rule, and the cursor makes the way we think is done on a line-by-row basis. For developers of Class C, this is a more comfortable way to think.

The normal way of thinking about collections is:

And for cursors:

This is why cursors are evil, and it makes developers lazy and lazy to want to implement certain features in a collection-oriented query.

Similarly, in performance, the cursor eats more memory, reduces available concurrency, consumes broadband, locks resources, and, of course, has more code ...

From the way the cursor is read to the database, it is not unsightly why the travel label takes up more resources, metaphorically speaking:

When you withdraw money from an ATM, is it more efficient to take 1000 at a time, or is it 10 times 100?

Why learn cursors if the cursor is so "evil"?

I personally think that existence is not only reasonable. To sum up, the learning of the cursor reasons I conclude the following 2 points

1. Existing systems have some cursors, and our queries must be implemented through cursors

2. As an alternative, when we end up with a while loop, subqueries, temporary tables, table variables, custom functions, or other methods that cannot be implemented in some queries, use a cursor implementation.

The lifecycle and implementation of T-SQL Middle-scale

In T-SQL, the life cycle of a cursor consists of 5 parts

1. Define a cursor

In T-SQL, defining a cursor can be either very simple or relatively complex, depending on the parameters of the cursor. The parameter setting of the cursor depends on how well you understand the principle of the cursor.

A cursor can actually be interpreted as a pointer defined over a particular dataset , and we can control the pointer traversing the dataset, or simply pointing to a specific row, so the cursor is defined on a dataset that starts with select:

The cursors in T-SQL are defined in MSDN as follows:

DECLARE cursor_name Cursor [local | GLOBAL] [forward_only | SCROLL] [STATIC | Keyset | DYNAMIC | Fast_forward] [Read_Only | Scroll_locks | Optimistic] [type_warning] for select_statement [for UPDATE [of column_name [,... N]] [;]

Looks like a lot of headaches, right? Here's how to define a cursor:

Cursors are divided into cursor types and cursor variables, and for cursor variables, follow the definition of T-SQL variables (what, do not know the rules defined by T-SQL variables?) Refer to my previous blog post). Cursor variables support two ways of assigning values, assign values and define them first, define cursor variables like other local variables, precede the cursor with "@", and note that if you define a global cursor, only the definition is directly assigned, and you cannot precede the cursor name with "@". There are two ways to define this:

Here we look at the parameters defined by the cursor:

Local and global two select a

Local means that the lifetime of a cursor is visible only in batches or functions or stored procedures, and global means that the cursor is in the context of a particular connection and is globally valid, for example:

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

Forward_only and SCROLL two elect a

Forward_only means that cursors can only be read from the beginning of the dataset to the end of the dataset, FETCH next is the only option, and scroll supports cursors moving in any direction or anywhere in the defined dataset, as shown in the following illustration:

STATIC keyset DYNAMIC and Fast_forward four Select a

These four keywords are the relationships between the data in the table that the cursor is in and the data that the cursor is reading out of

Static means that when a cursor is created, a copy of the dataset contained in the for subsequent SELECT statement is stored 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 are also reflected, and in the next fetch the data content changes

Keyset can be understood as a compromise between static and dynamic. The only primary key that determines the result set of the cursor is in tempdb for each row, and when any row in the result set is changed or deleted, @ @FETCH_STATUS will not be able to detect the newly added data for -2,keyset

Fast_forward can be understood as an optimized version of Forward_only. Forward_only executes a static plan, while Fast_forward chooses whether to use a dynamic or static plan depending on the situation, in most cases fast_ Forward is slightly better than forward_only performance.

Read_Only Scroll_locks optimistic three selected one
Read_Only means that declared cursors can only read data, and cursors cannot do any update operations

Scroll_locks is another extreme that locks all data that is read into the cursor and prevents other programs from making changes to ensure that the update is absolutely successful

Optimistic is a relatively good choice, optimistic do not lock any data, when you need to update the data in the cursor, if the underlying table data update, the cursor within the data update is unsuccessful, if the underlying table data is not updated, the cursor within the table data can be updated

2. Open cursor

When the cursor is defined, the cursor needs to be opened and used with only a single line of code:

OPEN Test_cursor

Note that a local variable cursor is opened by default when the name of the global and local cursor variables is duplicate

3. Using Cursors

The use of a cursor is divided into two parts, one in which the action cursor is pointing in the dataset, and the other is to manipulate some or all of the rows that the cursor points to

Only 6 kinds of mobile options are supported, to the first row, the last line (next), the next line (PRIOR), jumps directly to a row (absolute (n)), relative to the current hop (relative (n)), for example:

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

When the first step is complete, the value of the row is passed into the local variable via the INTO keyword:

For example, the following code:

Cursors are often used in conjunction with the global variable @ @FETCH_STATUS and the while loop to achieve the purpose of traversing the dataset in which the cursor resides, for example:

4. Close cursor

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

Close Test_cursor

5. Releasing cursors

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

Deallocate test_cursor

Some tuning suggestions for cursors

If you can not use a cursor, try to avoid using the cursor after the use of the need to close and release as far as possible do not define cursors on a large number of data as far as possible do not use the cursor to update the data as far as possible not using insensitive, static and keyset these parameters define cursors if you can, Forward keyword definition cursors if you only read the data and use only the FETCH next option when you read it, it's best to use the forward_only parameter

Summarize

This article talks about cursors from the basic concept of cursors to the lifecycle. Cursors are a very evil kind of existence, using cursors is often 2-3 times slower than using a set-oriented approach, which increases when the cursor is defined in large amounts 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 first.