Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use / close / release / optimize SQL Server cursors. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Cursors are evil!

In relational databases, our thinking about queries is set-oriented. Cursors break this rule, and cursors make us think line by line. For C-like developers, this way of thinking will be more comfortable.

The normal set-oriented way of thinking is:

For cursors, however:

This is why cursors are evil, making developers lazy and lazy to think about implementing certain functions with set-oriented queries.

Similarly, in terms of performance, cursors eat more memory, reduce available concurrency, occupy broadband, lock resources, and, of course, more code.

From the way cursors read from the database, it is not difficult to see why cursors take up more resources, for example:

When you withdraw money from ATM, is it more efficient to withdraw 1000 at once or 10 times?

If cursors are so evil, why learn cursors?

I personally think that existence is reasonable. To sum up, the reasons for learning cursors can be summarized as follows:

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

two。 As an alternative, we use cursor implementation when we run out of while loops, subqueries, temporary tables, table variables, self-built functions, or other ways to implement some queries.

Life cycle and implementation of cursors in T-SQL

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

1. Define a cursor

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

A cursor can actually be understood as a pointer defined on a specific dataset, and we can control this pointer to traverse the dataset, or just point to a specific row, so cursors are defined on datasets that start with Select:

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] [;]

It looks like a headache, right? Let's take a closer look at how to define cursors:

Cursors are divided into cursor types and cursor variables. for cursor variables, follow the definition method of T-SQL variables (what, do not know the rules of T-SQL variable definition? Refer to my previous blog post). Cursor variables can be assigned in two ways: when defining and first defining and then assigning. Defining a cursor variable is the same as defining other local variables by adding "@" before the cursor. Note that if you define a global cursor, you can only directly assign values during definition, and you cannot precede the cursor name with "@". The two definition methods are as follows:

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

Choose between LOCAL and Global

LOCAL means that the lifetime of cursors is visible only in batches or functions or stored procedures, while GLOBAL means that cursors are valid globally as context for specific connections, for example:

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

FORWARD_ONLY and SCROLL choose one of the two

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, while SCROLL supports cursors to move in any direction or position in the defined dataset, as shown in the following figure:

STATIC KEYSET DYNAMIC and FAST_FORWARD choose one of the four

These four keywords are the relationship between the data in the table reflected by the dataset in which the cursor resides 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 will be created and stored in the tempdb database, and any changes to the data in the underlying table will not affect the contents of the cursor.

DYNAMIC is the opposite of STATIC. When the underlying database changes, the contents of the cursor are reflected, and in the next fetch, the data contents will change accordingly.

KEYSET can be understood as a compromise between STATIC and DYNAMIC. Store the only primary key that can determine each row in the result set of the cursor into tempdb. 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

FAST_FORWARD can be understood as the optimized version of FORWARD_ONLY. Forward _ ONLY executes static plans, while FAST_FORWARD chooses dynamic plans or static plans according to the situation. In most cases, the performance of FAST_FORWARD is slightly better than FORWARD_ONLY.

READ_ONLY SCROLL_LOCKS OPTIMISTIC choosing one or three READ_ONLY means that declared cursors can only read data, and cursors cannot do any update operations.

SCROLL_LOCKS is the other extreme, locking all data read into the cursor to prevent 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 you need to update data in the cursor, if the underlying table data is updated, the update of the data in the cursor is not successful. If the underlying table data is not updated, the table data in the cursor can be updated.

two。 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 global and local cursor variables have duplicate names, local variable cursors are opened by default.

3. Use cursors

The use of cursors is divided into two parts, one is to manipulate the direction of the cursor in the dataset, and the other is to operate some or all of the rows pointed to by the cursor.

Only six mobile options are supported, namely to the first line (FIRST), the last line (LAST), the next line (NEXT), the previous line (PRIOR), directly skip to a line (ABSOLUTE (n)), relative to the current line (RELATIVE (n)), for example:

For cursors that do not specify the SCROLL option, only NEXT values are supported.

When the first step is complete, pass the value of this line to the local variable through the INTO keyword:

For example, the following code:

Cursors are often used with global variables @ @ FETCH_STATUS and WHILE loops for the purpose of traversing the dataset in which the cursor resides, for example:

4. Close the cursor

After using the cursor, be sure to turn it off, just one 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

Some optimization suggestions for cursors

If you can not use cursors, try not to use cursors, be sure to close and release cursors after running out of cursors, try not to define cursors on a large amount of data, try not to use updated data on cursors, try not to use insensitive, static and keyset parameters to define cursors if you can, use the FAST_FORWARD keyword to define cursors if you only read data, when reading only use the FETCH NEXT option, then you'd better use the FORWARD_ONLY parameter.

About the use / close / release / optimization of SQL Server cursors is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.