SQL Server cursor usage

SQL Server cursors use cursors! 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 the set is: For the cursor: This is also

SQL Server cursors use cursors! 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 the set is: For the cursor: This is also

SQL Server cursor usage

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. In the U.S. space, it will make developers become lazy, so they are too reluctant to use the collection-oriented query method to implement some functions.

Similarly, in terms of performance, the cursor will consume more memory, reduce available concurrency, occupy bandwidth, lock resources, and Hong Kong virtual hosts. 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. Hong Kong server, 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 pointer defined on a specific dataset. We can control the pointer to traverse the dataset, or just to point to a specific row, therefore, the cursor is defined on the dataset starting with Select:

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

Select_statement]