How to use SQL Cursors for Special Purposes

How to use SQL Cursors for Special Purposes
5 (100%) 2 votes

This article describes SQL cursors and how to use them for some special purposes. It highlights the importance of SQL cursors along with their downsides.

It is not always the case that you use SQL cursor in database programming, but their conceptual understanding and learning how to use them helps a lot to understand how to perform exceptional tasks in T-SQL programming.

SQL Cursors Overview

Let us go through some basics of SQL cursors if you are not familiar with them.

Simple Definition

A SQL cursor provides access to data one row at a time thereby giving you more (row-by-row) control over the result set.

Microsoft Definition

According to Microsoft documentation, Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time.

T-SQL and Result Set

Since both a simple and Microsoft definitions of the SQL cursor mention a result set, let us try to understand what exactly is the result set in the context of database programming. Let us quickly create and populate the Students table in a sample database UniversityV3 as follows:

Now, select all the rows from the  Student table:

Result set

This is the result set which is returned as a result of selecting all the records from the Student table.

T-SQL and Set Theory

T-SQL is purely based on the following two mathematical concepts:

  1. Set Theory
  2. Predicate Logic

The set theory, as the name indicates, is a branch of mathematics about sets which can also be called collections of definite distinct objects.

In short, in the set theory, we think of things or objects as a whole in the same way we think of an individual item.

For example, the student is a set of all the definite distinct students, so, we take a student as a whole which is enough to get details of all the students in that set (table).

Please refer to my article The Art of Aggregating Data in SQL from Simple to Sliding Aggregations for further details.

Cursors and Row-Based Operations

T-SQL is primarily designed to perform set based operations such as selecting all the records from a table or delete all the rows from a table.

In short, T-SQL is specially designed to work with tables in a set based fashion, which means we think of a table as a whole, and any operation such as select, update or delete is applied as a whole to the table or certain rows which satisfy the criteria.

However, there are cases when tables need to be accessed row by row rather than as one single result set, and this is when cursors come into action.

According to Vaidehi Pandere, sometimes application logic needs to work with one row at a time rather than all the rows at once which is the same as looping (use of loops to iterate) through the entire result set.

Basics of SQL Cursors with Examples

Let us now discuss more about SQL cursors.

First of all, let us learn or review (those who are already familiar with using cursors in T-SQL) how to use cursor in T-SQL.

Using the SQL cursor is a five-step process expressed as follows:

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

Step 1: Declare Cursor

The first step is to declare the SQL cursor so that it can be used afterward.

SQL cursor can be declared as follows:

Step 2: Open Cursor

The next step after the declaration is to open the cursor which means populating the cursor with the result set which is expressed as follows:

Step 3: Fetch Rows

Once the cursor is declared and opened, the next step is to start retrieving rows from SQL cursor one by one so fetch rows get the next available row from the SQL cursor:

Step 4: Close Cursor

Once the rows are fetched one by one and manipulated as per requirement, the next step is to close the SQL cursor.

Closing SQL cursor performs three tasks:

  1. Releases the result set currently held by the cursor
  2. Frees any cursor locks on the rows by the cursor
  3. Closes the open cursor

The simple syntax for closing the cursor is as follows:

Step 5: Deallocate Cursor

The final step in this regard is to deallocate the cursor which removes the cursor reference.

The syntax is as follows:

SQL Cursor Compatibility

According to Microsoft documentation, SQL cursors are compatible with the following versions:

  1. SQL Server 2008 and upward versions
  2. Azure SQL Database

SQL Cursor Example 1:

Now that we are familiar with the steps involved to implement SQL cursor, let us look at a simple example of using SQL cursor:

The output is as follows:

SQL cursor simple example

SQL Cursor Example 2:

In this example, we are going to use two variables to store the data held by the cursor as it moves from row to row so that we can display the result set one row at a time by displaying the variables values.

The result of the above SQL code is as follows:

Using SQL cursor with variables

One would argue that we can achieve the same output by using simple SQL script as follows:

Student Id and Name without using SQL cursor

In fact, there are quite a few tasks which do require SQL cursors to be used despite the fact that it is discouraged to use SQL cursors due to their direct impact on memory.

Important Note

Please bear in mind that according to Vaidehi Pandere, cursors are a memory resident set of pointers so they occupy your system memory which would otherwise be used by other important processes; that’s why traversing a large result set through cursors is never a good idea unless there is a legitimate reason for that.

Using SQL Cursors for Special Purposes

We will go through some special purposes for which SQL cursors can be used.

Database Server Memory Testing

Since SQL cursors have a high impact on system memory, they are good candidates to replicate scenarios where excessive memory usage by different stored procedures or ad-hoc SQL scripts needs to be investigated.

One simple way  to understand this is to click the client statistics button on the toolbar (or press Shift+Alt+S) in SSMS (SQL Server Management Studio) and run a simple query without cursor:

Client statistics

Now run the query with the cursor using variables (SQL Cursor Example 2):

SQL script with SQL cursor

Now please note down the differences:

Number of SELECT statements without cursor: 1

Number of SELECT statements with cursor: 7

Number of server roundtrips without cursor: 1

Number of server roundtrips with cursor: 2

Client processing time without cursor: 1

Client processing time with cursor: 8

Total execution time without cursor: 1

Total execution time with cursor: 38

Wait time on server replies without cursor: 0

Wait time on server replies with cursor: 30

In short, running the query without the cursor which returns just 5 rows is running the same query 6-7 times with the cursor.

Now you can imagine how easy it is to replicate memory impact using cursors, however, this is not always the best thing to do.

Bulk Database Objects Manipulation Tasks

There is another area where SQL cursors can be handy and it is when we have to perform a bulk operation on databases or database objects.

In order to understand this, first, we need to create the Course table and populate it in the UniversityV3 database as follows:

Now suppose we want to rename all the existing tables in the UniversityV3 database as OLD tables.

This requires cursor iteration over all the tables one by one so that they can be renamed.

The following code does the job:

Renaming tables using SQL Cursor

Congratulations, you have successfully renamed all the existing tables using the SQL cursor.

Things to Do

Now that you are familiar with the use of the SQL cursor please try the following things:

  1. Please try to create and rename indexes of all the tables of a sample database through the cursor.
  2. Please try to revert the renamed tables in this article back to original names using the cursor.
  3. Please try populating tables with a lot of rows and measure the statistics and time for the queries with and without the cursor.

 

Haroon Ashraf

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