Cursor In SQL

What is Cursor in SQL?

A cursor is a database object in SQL Server that lets us retrieve and manipulate data one row at a time. A cursor is nothing more than a row's pointing device. It must be followed by a SELECT query at all times. It's usually a set of SQL logic that loops through a set number of rows one by one. When we have a huge database of employee records and want to calculate each employee's wage after taxes and leaves have been deducted, we can use the cursor.
A cursor is a type of workstation or temporary memory. When a user performs DML (Data Manipulation Language) actions on a table, the database server allocates it.

A cursor's purpose is to update data row by row, change it, or perform calculations that would be hard to perform if all records were fetched at once. It can also be used to do administrative tasks like SQL Server database backups in a sequential order. Cursors are commonly used in development, database management, and ETL procedures. This article explains SQL Server cursors in detail, including their life cycle, why and when they're used, how to use them, their limitations, and how to replace them.

Life Cycle of a cursor

A cursor's life cycle can be broken down into five separate stages:

The first step is to declare the cursor using the following SQL statement:

DECLARE cursor_name CURSOR  
FOR select_statement;  

After using the Define keyword, we may declare a cursor by using the data type CURSOR and its name. The SELECT statement, which defines the cursor's output, will be written next.

In the second phase, the cursor is opened to store data from the result set. We can achieve such by using the SQL statement below:

OPEN cursor_name;

It's the third step, in which rows are fetched one by one or in batches to perform data manipulation operations on the cursor's currently active row, such as insert, update, and delete. Using the SQL statement below, we can accomplish this:

FETCH NEXT FROME cursor INTO variable_list;

Using SQL Server's @@FETCHSTATUS function, we can also get the status of the most recent FETCH command cursor that was executed on the cursor. The FETCH statement was successful when the @@FETCHSTATUS returned zero. Use the WHILE statement to get all records from the cursor.

As a fourth step, we should close the cursor after we've finished working with it. Using the SQL statement below, we can accomplish this:

CLOSE cursor_name;

In this fifth and last stage, the cursor definition will be deleted, and all system resources associated with the cursor will be released. We may achieve this by using the SQL statement below:

DEALLOCATE cursor_name;

Uses of SQL Server Cursor

Relational database management solutions, such as SQL Server, excel at processing data in result sets, which are collections of rows, as we all know. The product descriptions, for example, are maintained in the product table table. If we want to change a product's price, we may use the 'UPDATE' query, which will update all entries that match the 'WHERE' condition:

UPDATE product_table SET unit_price = 300 WHERE product_id = 190;  

Rather of processing the entire result set at once, the application may be forced to handle the rows in a singleton fashion. In SQL Server, cursors can be utilised to accomplish this. Before using the cursor, keep in mind that cursors are notoriously slow, thus it should only be utilised when all other choices have been exhausted.

The cursor uses the same technique as loops like FOREACH, FOR, WHILE, DO WHILE to iterate one object at a time in all computer languages. As a result, it may be chosen since it follows the same logic as programming languages' looping processes.

Types of Cursors in SQL Server

The following are the various types of cursors available in SQL Server:

🚀 Forward-Only Cursors
🚀 Static Cursors
🚀 Keyset Cursors
🚀 Dynamic Cursors

Let's take a quick look at them.

The result set of a static cursor is always the same as when it was first opened. Static cursor results are always read-only because they are saved in tempdb. Forward and backward movement is possible with the static cursor. It is slower and consumes more memory than other cursors. As a result, we can only use it when we need to scroll and other cursors aren't working.

This cursor reveals records that were removed after the database was visited. A static cursor does not represent INSERT, UPDATE, or DELETE operations (unless the cursor is closed and reopened).

The polar opposite of static cursors, dynamic cursors allow us to update, delete, and insert data while the cursor is open. It can be scrolled by default. It can detect all changes to the result set's rows, order, and values, whether they happen inside or outside the cursor. Until the updates have been committed, we won't be able to see them outside of the cursor.

It is the most common and fastest cursor type. It's called a forward-only cursor because it only moves forward through the result set. With this cursor, scrolling is not possible. Only rows from the start of the result set to the end can be retrieved. It allows us to add, edit, and delete information. The effect of the user's insert, update, and delete operations that affect rows in the result set may be seen as the rows are fetched from the cursor. When a row is fetched, we can't see the changes made to it through the pointer.

There are three different types of Forward-Only cursors:
🚀 Forward_Only Static
🚀 Forward_Only Keyset
🚀 Fast_Forward

This cursor is in between a static and a dynamic cursor in terms of its capacity to detect changes. It can't always detect changes in the membership and order of the result set, just as a static cursor. It can detect changes in the result set's rows values as if it were a dynamic cursor. Only from the first to the last row, and then from the last to the first, can it go. The order and membership are fixed when this cursor is opened.

It operates by using the same set of unique identifiers as the keys in the keyset. The keyset is determined by all rows that qualified the SELECT statement when the cursor was first launched. It can also detect changes to the data source, as well as update and delete actions. By default, it can be scrolled.

Limitations of SQL Server Cursor

Because cursors have restrictions, they should only be used when all other choices have been exhausted. The following are the limitations:

🚀 Cursor consumes network resources because each record it retrieves necessitates a network roundtrip.
🚀A cursor is a memory-resident group of pointers, which means it takes up space on our computer that may be utilised by other processes.
🚀 It locks a piece of the database or the entire table when processing data. The cursor's performance and speed are slower since it refreshes table records one row at a time.
🚀Cursors are faster than while loops, but they come at a higher cost.
🚀The number of rows and columns brought into the cursor also affects cursor speed. It's the time it takes you to open your cursor and execute a fetch command.

How can we avoid Cursor?

The basic purpose of cursors is to traverse the table row by row. The most basic method for avoiding cursors is as follows:

🚀 Using a while loop in SQL
A while loop that allows a result set to be inserted into a temporary table is the simplest way to avoid using a cursor.

🚀 Functions that are defined by the user
The final row set is occasionally calculated using cursors. We can accomplish this by employing a user-defined function that meets the requirements.

🚀 Making Use of Joins
In the case of big data, Join processes only those columns that meet the specified criteria, minimising the number of lines of code and offering faster performance than cursors. Cursor consumes network resources because each record it retrieves necessitates a network roundtrip.

With this article at Logicmojo, you must have the complete idea of Cursor in SQL .