SQL Server cursor usage/closure/release/optimization Summary

Cursors are evil!

In relational databases, we think about queries in a set. The cursor breaks this rule and changes the way we think about it into a row-by-row approach. For C-like developers, this approach is more comfortable.

The normal way of thinking for collections is:

For a cursor:

This is also why cursors are evil. They make developers become lazy and are too reluctant to use the set-oriented query method to implement certain functions.

Similarly, in terms of performance, the cursor will consume more memory, reduce available concurrency, occupy bandwidth, lock resources, and of course there will be more code ......

From the way the cursor reads data from the database, it is not difficult to see why the cursor occupies more resources. For example:

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

Since cursors are so "evil", why do we need to learn cursors?

I personally think that existence is reasonable. In summary, the reason for learning cursors is summarized as follows:

1. The existing system has some cursors, which must be used for query.

2. as an alternative method, when we exhaust the while LOOP, subqueries, temporary tables, table variables, self-built functions, or other methods, we can't implement some queries, we use cursors.

Life cycle and implementation of midstream mark in T-SQL

In a T-SQL, the lifecycle of a cursor consists of five parts

1. Define a cursor

In the T-SQL, defining a cursor can be very simple or relatively complex, depending on the cursor parameters, and the cursor parameter settings depend on your understanding of the principle of the cursor.

A cursor can be understood as a specific definition.DatasetWe can control this pointer traversal.DatasetOr just pointing to a specific row, so the cursor is defined on the dataset starting with Select:

The cursor definitions in the T-SQL are as follows in MSDN:

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 [[,... n] [;]

It seems a headache, right? Let's take a closer look at how to define a cursor:

The cursor is divided into the cursor type and the cursor variable, for the cursor variable, follow the T-SQL variable definition method (what, do not know the T-SQL variable definition rules? Refer to my previous blog posts ). you can assign values to a cursor variable in either of the following ways: assign values at definition and assign values after definition. Just like defining other local variables, add "@" before the cursor. Note, if you define a global cursor, you can only assign values when defining the cursor, and you cannot add "@" before the cursor name. The two definitions are as follows:

The following describes the parameters defined by the cursor:

Either LOCAL or GLOBAL

LOCAL means that the lifecycle of the cursor is only visible in batch processing or functions or stored procedures, while GLOBAL means that the cursor isConnectionIt is valid globally as the context, for example:

If the cursor scope is not specified, the default scope is GLOBAL.

Either FORWARD_ONLY or SCROLL

FORWARD_ONLY means that the cursor can only be read from the start of the dataset to the end of the dataset. fetch next is the only option, and SCROLL supports moving the cursor to any direction or any position in the defined dataset, for example:

Either static keyset dynamic or FAST_FORWARD

The four keywords indicate the relationship between the data in the table reflected by the cursor dataset and the data read by the cursor.

STATIC means that when a cursor is created, a copy of the dataset contained in the SELECT statement after the FOR statement will be saved to the tempdb database, any changes to the data in the underlying table will not affect the content of the cursor.

DYNAMIC is the opposite of STATIC. When the underlying database changes, the cursor content is also reflected. In the next fetch, the data content changes accordingly.

KEYSET can be understood as a compromise between STATIC and DYNAMIC. Store the primary key of each row in the result set of the cursor to tempdb. When any row in the result set changes or is deleted, @ FETCH_STATUS is-2, KEYSET cannot detect new data

FAST_FORWARD can be understood as the optimized FORWARD_ONLY version. FORWARD_ONLY executes static plans, while FAST_FORWARD selects dynamic or static plans based on the situation. In most cases, FAST_FORWARD performs better than FORWARD_ONLY.

READ_ONLY SCROLL_LOCKS OPTIMISTIC.
READ_ONLY means that the declared cursor can only read data, and the cursor cannot be updated.

SCROLL_LOCKS is another extreme. It locks all data that is read into the cursor to prevent other programs from making changes to ensure the update is absolutely successful.

OPTIMISTIC is a relatively good choice. OPTIMISTIC does not lock any data. When you need to update data in the cursor, if the underlying table data is updated, the data in the cursor is not updated successfully. If, if the underlying table data is not updated, the table data in the cursor can be updated.

2. Open the cursor

After the cursor is defined, it must be opened and used. There is only one simple line of code:

OPEN test_Cursor

Note: When the global cursor and the local cursor variable are duplicated, the local variable cursor is enabled by default.

3. Use a cursor

The use of a cursor is divided into two parts, one is the point of the Operation cursor in the dataset, the other is to operate part or all of the rows pointed to by the cursor

Only six moving options are supported, including FIRST, LAST, NEXT, and PRIOR ), directly jump to a certain line (ABSOLUTE (n), compared to the current few lines (RELATIVE (n), for example:

For a cursor that does not specify the SCROLL option, only the NEXT value is supported.

After the first step is completed, the value of this line is passed INTO the local variable through the INTO Keyword:

For example, the following code:

The cursor is often used together with the global variable @ FETCH_STATUS and WHILE loop to traverse the dataset where the cursor is located. For example:

4. Close the cursor

After using the cursor, remember to CLOSE it. You only need a line of code: CLOSE + cursor name.

CLOSE test_Cursor

5. Release cursor

When the cursor no longer needs to be used, release the cursor with only one line of code: DEALLOCATE + cursor name

DEALLOCATE test_Cursor

Optimization suggestions for cursors

If you do not need a cursor, try not to use it to close and release it after it is used up. Try not to define a cursor on a large amount of data. Try not to use an insensitive to update data on the cursor, static and keyset parameters define the cursor if possible. Try to use the FAST_FORWARD keyword to define the cursor. If only the data is read, only the fetch next option is used for reading, it is best to use the FORWARD_ONLY parameter.

Summary

This article describes the cursor from the basic concepts of the cursor to the lifecycle. The cursor is veryEvilThe use of cursors is usually two to three times slower than the use of the Set-oriented method. When the cursor is defined as a large data volume, this proportion will increase. If possible, try to use while, subquery, temporary tables, functions, table variables, and so on to replace the cursor. Remember, the cursor is always your last choice, not your first choice.