How to use SQL Cursors for Special Purposes

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:

CREATE TABLE [dbo].[Student] (
    [StudentId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (30)  NULL,
    [Course]    VARCHAR (30)  NULL,
    [Marks]     INT           NULL,
    [ExamDate]  DATETIME2 (7) NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)
);

-- (5) Populate Student table
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (1, N'Asif', N'Database Management System', 80, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (2, N'Peter', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (3, N'Sam', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (4, N'Adil', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (5, N'Naveed', N'Database Management System', 90, N'2016-01-01 00:00:00')
SET IDENTITY_INSERT [dbo].[Student] OFF

Now, select all the rows from the  Student table:

-- View Student table data
SELECT [StudentId], [Name], [Course], [Marks], [ExamDate] FROM dbo.Student

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:

DECLARE Cursor <Cursor_Name> for <SQL statement>

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:

Open <Cursor_Name>

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:

Fetch Next from <Cursor_Name>

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:

Close <Cursor_Name>

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:

DEALLOCATE <Cursor_Name>

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:

-- Declare Student cursor example 1
USE UniversityV3
GO

DECLARE Student_Cursor CURSOR FOR SELECT
  StudentId
 ,[Name]
FROM dbo.Student;
OPEN Student_Cursor
FETCH NEXT FROM Student_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Student_Cursor
END
CLOSE Student_Cursor
DEALLOCATE Student_Cursor

The output is as follows:

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.

-- Declare Student cursor with variables example 2
USE UniversityV3
GO

DECLARE @StudentId INT
       ,@StudentName VARCHAR(40) -- Declare variables to hold row data held by cursor
DECLARE Student_Cursor CURSOR FOR SELECT 
  StudentId
 ,[Name]
FROM dbo.Student;
OPEN Student_Cursor
FETCH NEXT FROM Student_Cursor INTO @StudentId, @StudentName -- Fetch first row and store it into variables
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONCAT(@StudentId,'--', @StudentName) -- Show variables data
FETCH NEXT FROM Student_Cursor -- Get next row data into cursor and store it into variables
INTO @StudentId, @StudentName
END
CLOSE Student_Cursor -- Close cursor locks on the rows
DEALLOCATE Student_Cursor -- Release cursor reference

The result of the above SQL code is as follows:

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

-- Viewing student id and name without SQL cursor
SELECT StudentId,Name FROM dbo.Student
order by StudentId

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:

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

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:

-- Create Course table
CREATE TABLE [dbo].[Course] (
    [CourseId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (30)  NOT NULL,
    [Detail]   VARCHAR (200) NULL,
    CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC)
);

-- Populate Course table
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (1, N'DevOps for Databases', N'This is about DevOps for Databases')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (2, N'Power BI Fundamentals', N'This is about Power BI Fundamentals')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (3, N'T-SQL Programming', N'About T-SQL Programming')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (4, N'Tabular Data Modeling', N'This is about Tabular Data Modeling')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (5, N'Analysis Services Fundamentals', N'This is about Analysis Services Fundamentals')
SET IDENTITY_INSERT [dbo].[Course] OFF

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:

-- Declare Student cursor to rename all the tables as old
USE UniversityV3
GO

DECLARE @TableName VARCHAR(50) -- Existing table name
       ,@NewTableName VARCHAR(50) -- New table name

DECLARE Student_Cursor CURSOR FOR SELECT T.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T;
OPEN Student_Cursor
FETCH NEXT FROM Student_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NewTableName=@TableName+'_OLD' -- Add _OLD to exsiting name of the table
EXEC sp_rename @TableName,@NewTableName -- Rename table as OLD table
FETCH NEXT FROM Student_Cursor -- Get next row data into cursor and store it into variables
INTO @TableName
END
CLOSE Student_Cursor -- Close cursor locks on the rows
DEALLOCATE Student_Cursor -- Release cursor reference

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.