Use of SQL Server cursors

Ext.: http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

The cursor is evil!

In a relational database, our thinking about queries is set-oriented. The cursor breaks this rule, and the cursor makes us think in a progressive way. For developers of Class C, this way of thinking will be more comfortable.

The normal set-oriented way of thinking is:

For cursors, however:

This is also why cursors are evil, which makes developers lazy and lazy to implement certain functions with a collection-oriented query.

Similarly, in performance, cursors eat more memory, reduce available concurrency, consume bandwidth, lock resources, and of course, more code.

From the cursor to the database read way, not ugly travel standard why occupy more resources, for example:

When you withdraw money from an ATM, is it 1000 more efficient or 10 times 100?

since cursors are so "evil", why learn Cursors

I personally think that existence is both reasonable. In conclusion, learning the cursor causes me to summarize the following 2 points

1. There are some cursors in the existing system, and our query must be implemented by cursors

2. As an alternative, use cursors when we have exhausted the while loop, subqueries, temporal tables, table variables, self-built functions, or other ways to throw to implement certain queries.

the life cycle and implementation of T-SQL Midstream target

In T-SQL, the life cycle of a cursor is made up 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 know the cursor principle.

Cursors can actually be interpreted as a pointer to a specific set of data , and we can 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:

The cursor definitions in 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 [of column_name [,... N]] [;]

It looks like a headache, doesn't it? Let's take a closer look at how to define a cursor:

Cursors are divided into cursor types and cursor variables, which, for cursor variables, follow the definition of T-SQL variables (what are the rules defined by T-SQL variables?). Refer to my previous blog post). 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 is only supported when defined, and cannot precede the cursor name with "@". There are two ways to define this:

Let's look at the parameters defined by the cursor:

Local and global two select one

Local means that the lifetime of a cursor is only visible in a batch or function or stored procedure, while global means that the cursor is valid globally for a particular connection , such as:

If you do not specify a cursor scope, the default scope 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, while scroll supports cursors moving in any direction, or anywhere, in a defined dataset, such as:

STATIC KEYSET DYNAMIC and Fast_forward four select one

These four keywords are the relationship 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 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 reflected, and the contents of the data are changed in the next fetch.

Keyset can be understood as a compromise between static and dynamic. The sole of the result set where the cursor is located determines the primary key for each row to be 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 can be understood as an optimized version of 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, in most cases fast_ Forward is slightly better than forward_only performance.

Read_Only Scroll_locks optimistic three choice one
Read_Only means that a declared cursor can only read data, and the cursor cannot do any update operations

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

2. Open the cursor

When the cursor is defined, the cursor needs to be opened and used with a simple 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

3. Using Cursors

The use of cursors is divided into two parts, one pointing to the operation cursor in the dataset, and the other to manipulating some or all of the rows that the cursor points to.

Only 6 move options are supported, namely, to the first row, the last line, the next line (next), the previous line (PRIOR), and jump 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 this line is passed to the local variable by the INTO keyword:

For example, the following code:

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:

4. Close the cursor

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

CLOSE Test_cursor

5. 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

Summary

This paper discusses cursors from the basic concept of cursors to the life cycle. 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.

the cursor is evil!

Use of SQL Server cursors