Replacing SQL Cursors with Alternatives to Avoid Performance Issues

Total: 8 Average: 4.3

In this article, we’ll look at some alternatives to using SQL cursors which can help to avoid performance issues caused by using cursors.

Before discussing the alternatives, let’s review the general concept of SQL cursors.

Quick Overview of SQL Cursors

SQL cursors are primarily used where set-based operations are not applicable and you are required to access data and perform operations one row at a time rather than applying a single set-based operation to an entire object (such as a table or a set of tables).

Simple Definition

A SQL cursor provides access to data one row at a time there by giving you direct row-by-row control over the result set.

Microsoft Definition

According to the Microsoft documentation, Microsoft SQL Server statements produce a complete result set, but there are times when it is best to process it one row at a time – which can be done by opening a cursor on the result set.

The 5-Step Process of Using a Cursor

The process of using a SQL cursor can be generally described as follows:

  1. Declare Cursor
  2. Open Cursor
  3. Fetch rows
  4. Close Cursor
  5. Deallocate Cursor

Important Note

Please keep in mind that, according to Vaidehi Pandere, cursors are pointers that occupy your system memory – which would otherwise be reserved for other important processes. That’s why traversing a large result set by using cursors is usually not the best idea – unless there’s a legitimate reason for doing that.

For more detailed information on this, feel free to refer to my article How to use SQL Cursors for Special Purposes.

SQL Cursor Example

First, we will look at an example of how a SQL cursor can be used to rename database objects one by one.

In order to create a SQL cursor we need, let’s setup a sample database so that we can run our scripts against it.

Setup Sample Database (UniversityV3)

Run the following script to create and populate the UniversityV3 sample database with two tables:

Create a SQL Cursor to Rename Tables (_Backup)

Now consider meeting the following specification by using a cursor:

  1. We need to add ‘_Backup’ to names of all existing tables in a database
  2. Tables that already have ‘_Backup’ in their name should not be renamed

Let’s create a SQL cursor to rename all tables in the sample database 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:

Run the Renaming Script and View Results

Now, Press F5 in SSMS (SQL Server Management Studio) to run the script and see the results:

SQL Cursor renaming tables

Refreshing the tables’ names in the SSMS object explorer clearly shows that we have successfully changed them as specified.

Let’s run the script again by pressing F5 again and look at the results:

SQL Cursor Renamed Table Warning

Creating a SQL Cursor to Reset _Backup Naming

We also need to create a script which uses a SQL cursor to revert the names of the tables we have just changed back to the initial ones – we’ll do this by removing ‘_Backup’ from their names.

The script below will let us do just that :

Run the Reset Script and View Results

Running the script shows that table names have been successfully reset:

SQL Cursor Reset Table Names

These were the examples of some scenarios in which it is difficult to avoid using SQL cursors due to the nature of the requirement. However, it is still possible to find an alternative approach.

SQL Cursor Alternatives

There are two most common alternatives for SQL cursors, so let’s look at each one of them in detail.

Alternative 1: Table Variables

One of these alternatives are table variables.

Table variables, just like tables, can store multiple results – but with some limitation. According to the Microsoft documentation, a table variable is a special data type used to store a result set for processing at a later time.

However, keep in mind that table variables are best used with small data sets.

Table variables can be very efficient for small-scale queries since they work like local variables and are cleaned up automatically upon getting out of scope.

Table Variable Strategy:

We will use table variables instead of SQL cursors to rename all tables from a database by following these steps:

  1. Declare a table variable
  2. Store tables names and ids in the table variable we declared
  3. Set the counter to 1 and get the total number of records from the table variable
  4. Use a ‘while’ loop as long as the counter is less than or equal to the total number of records
  5. Inside the ‘while’ loop, we’ll rename tables one by one as long as they are not already renamed and increasing the counter for each table

Table Variable Code:

Run the following SQL script which creates and uses a table variable to rename tables:

Run the Script and View results

Now, let’s execute the script and check the results:

SQL Table Variable Rename Tables

Alternative 2: Temporary Tables

We can also use temporary tables instead of SQL cursors to iterate the result set one row at a time.

Temporary tables have been in use for a long time and provide an excellent way to replace cursors for large data sets.

Just like table variables, 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.

Temporary Table Strategy:

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

  1. Declare a temporary table
  2. Store tables names and ids in the temporary table we just declared
  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 ‘while’ loop, rename tables one by one as long as they are not already renamed and increase the counter for each table

Reset the tables

We need to reset the tables’ names to their initial form by deleting ‘_Backupfrom the end of their names, so please rerun the reset script we have already written and used above so that we can apply another method of renaming tables.

Temporary Table Code:

Run the following SQL script to create and use a temporary table to rename all tables in our database:

Run the Script and Check the Output

Now, let’s execute the script to view the results:

SQL Temporary Table Rename Tables

Things to Do

Now that you are familiar with alternatives to SQL cursors – such as using table variables and temporary tables – please try doing the following things to get comfortable with applying this knowledge in practice:

  1. Create and rename indices of all tables in a sample database – first via a Cursor, and then by using alternative methods (table variables and temporary tables)
  2. Revert the names of the tables from this article back to their initial names using alternative methods (temporary tables and table variables)
  3. You can also refer to first example(s) in my article How to use SQL Cursors for Special Purposes and try populating tables with lots of rows and measure the statistics and time for the queries to compare the basic cursor method with the alternatives

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
Haroon Ashraf