What is a cursor in SQL Server and why do you need it?

A SQL cursor is a database object which is used to manipulate data in a set, do row-by-row processing, instead of the T-SQL commands that operate on all the rows in the result set at one time.

There are some conditions when we want to get a record from one table and need to insert it into another with performing some condition or logic. A cursor is a similar approach to any looping mechanism found in any other programming.

The idea behind cursors sounds perfect and writing one seems good, but one can runs into big problems when it comes to performance.

How to Create a SQL Server Cursor?

The process of using a SQL Cursor (starting with MS SQL 2008) can be described as follows:

1. Declaring Cursor by defining the SQL statement.
2. Opening the Cursor for storing data retrieved from the result set.
3. Rows can be fetched from the cursor one by one or in a block to do data manipulation.
4. The cursor should be closed explicitly after data manipulation.
5. Cursors should be deallocated to delete cursor definition and release all the system resources.

Cursor Syntax

What are the different types of a cursor in SQL Server?

Microsoft SQL Server supports the following types of cursors.

1. STATIC CURSOR
   Populates the result set during cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward.
2. FAST_FORWARD (default type of cursor)
   It is identical to the static except that you can only scroll forward.
3. DYNAMIC
   Additions and deletions are visible for others in the data source while the cursor is open. Unlike static cursors, all the changes made in the Dynamic cursor will reflect the Original data. You can use this cursor to perform INSERT, DELETE, and UPDATE operations.
4. KEYSET
   This is like a dynamic cursor except we can’t see records others add. If another user deletes a record, it is inaccessible from our recordset.

Example

The example below renames all tables in the sample database named as Spreadsheet, by adding ‘_Backup’ to each table’s name while also ensuring that tables containing ‘_Backup’ in their name will not be renamed again by running the following code:

What are the cursors’ limitations?

Cursors are used frequently as the wrong tool for the wrong task. They’re used for quick programming when a developer does not have a full understanding of set operations.

The problem with the cursor is that, in most cases, we can use JOINS, even WHILE clauses, SSIS packages, or other alternative methods to get the same result quicker, with less impact on performance output and even writing fewer lines of syntax.

The principal factor problem affecting cursor speed is the number of rows and columns brought into it. While a cursor is being populated, the table being iterated is locked. Row operations can take a long time to execute depending upon the type of task being performed on each row. While the cursor is open, a table cannot be accessed or updated by the other users.

Cursors could be used in some applications for serialized, but generally, they should be avoided because they bring a negative impact on performance, especially when operating on a large set of data.

Alternatives to SQL Server Cursors

There are many alternatives for SQL cursors, so let’s look at the temporary table strategy in detail. Temporary tables have been in use for a long time and provide an excellent way to replace cursors for large data sets. Temporary tables can hold the result set so that we can perform the necessary operations by processing it with an iterating algorithm such as a ‘while’ loop.

We will use a temporary table to rename all tables in the sample by following these steps:

1. Declare a temporary table;
2. Store tables names and ids;
3. Set the counter to 1 and get the total number of records from the temporary table;
4. Use a ‘while’ loop as long as the counter is less than or equal to the total number of records;
5. Within the loop, rename tables one by one if they are not already renamed and increase the counter for each table;

Conclusions

It`s a good practice in SQL to think at making operations on sets of data, rather than think programmatically, using iterations or loops, because this kind of approach is not recommended nor intended for this use.

We must think about set-based operations rather than one row at a time to get the data we need. Use loops like FOR or FOREACH from programming languages and associate that logic with SQL operations, is an obstacle for getting the right solution to our needs.