Cursors are evil.

Source: Internet
Author: User

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 [ OF column_name [ ,...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 a value 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.
  • Close and release after use
  • Try not to define a cursor on a large amount of data
  • Try not to use a cursor to update data
  • Do not use insensitive, static, or keyset parameters to define the cursor.
  • If possible, use the fast_forward keyword to define the cursor.
  • If you only use the fetch next option to read data, it is best to use the forward_only parameter.



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.

Cursors are evil!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.