Written by 16:57 Database administration, Performance Tuning, Troubleshooting Issues

Replacing SQL Cursors with Alternatives to Avoid Performance Issues

In this article, we’ll look at some alternatives to using SQL cursors which can help 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, thereby 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:

-- (1) Create UniversityV3 sample database

CREATE DATABASE UniversityV3;

GO

USE UniversityV3

-- (2) Create Course table

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Course') 

DROP TABLE dbo.Course 

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)

);

-- (3) Create Student table

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Student') 

DROP TABLE dbo.Student 

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)

);

-- (4) 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



-- (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

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:

-- Declaring the Student cursor to rename all tables by adding ‘_backup’ to their names and also making sure that all tables that are already named correctly will be skipped:

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

IF RIGHT(@TableName,6)<>'Backup' -- If Backup table does not exist then rename the table

BEGIN

SET @NewTableName=@TableName+'_Backup' -- Add _Backup to the table’s current name

EXEC sp_rename @TableName,@NewTableName -- Rename table as OLD table

END

ELSE

PRINT 'Backup table name already exists: '+@TABLENAME

FETCH NEXT FROM Student_Cursor -- Get next row data into cursor and store it in variables

INTO @TableName

END

CLOSE Student_Cursor -- Close cursor locks on the rows

DEALLOCATE Student_Cursor -- Release cursor reference

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 :

-- Declare the Student cursor to reset tables names _backup to their original forms by removing ‘_backup’

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

IF RIGHT(@TableName,6)='Backup' -- If Backup table name exists then reset (rename) it

BEGIN

SET @NewTableName=SUBSTRING(@TableName,1,LEN(@TableName)-7) -- Remove _Backup from the table name

EXEC sp_rename @TableName,@NewTableName -- Rename table 

END

ELSE

PRINT 'Backup table name already reset: '+@TABLENAME

FETCH NEXT FROM Student_Cursor – Get the data of the next row into cursor and store it in variables

INTO @TableName

END

CLOSE Student_Cursor -- Close cursor locks on the rows

DEALLOCATE Student_Cursor -- Release cursor reference

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:

-- Declare Student Table Variable to rename all tables by adding ‘_backup’ t their name and also making sure that already renamed tables are skipped

USE UniversityV3

GO

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

DECLARE @StudentTableVar TABLE -- Declaring a table variable to store tables names
(
TableId INT,

TableName VARCHAR(40))

INSERT INTO @StudentTableVar -- insert tables names into the table variable 

SELECT ROW_NUMBER() OVER(ORDER BY T.TABLE_NAME),T.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T

DECLARE @TotalRows INT=(SELECT COUNT(*) FROM @StudentTableVar),@i INT=1 -- Get total rows and set counter to 1

WHILE @i<=@TotalRows -- begin as long as i (counter) is less than or equal to the total number of records

BEGIN -- ‘While’ loop begins here

SELECT @TableName=TableName from @StudentTableVar WHERE TableId=@i

IF RIGHT(@TableName,6)<>'Backup' -- If a Backup table does not exist, then rename the table

BEGIN

SET @NewTableName=@TableName+'_Backup' -- Add _Backup to the table’s current name

EXEC sp_rename @TableName,@NewTableName -- Rename the table as OLD table

END

ELSE

PRINT 'Backup table name already exists: '+@TABLENAME

SET @i=@i+1

END -- 'While' loop ends here

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:

-- Declare the Student Temporary Table to rename all tables by adding ‘_backup’ to their names while also making sure that already renamed tables are skipped

USE UniversityV3

GO

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

CREATE TABLE #Student -- Declaring a temporary table

(
TableId INT,
TableName VARCHAR(40)
)

INSERT INTO #Student -- insert tables names into the temporary table

SELECT ROW_NUMBER() OVER(ORDER BY T.TABLE_NAME),T.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T

DECLARE @TotalRows INT=(SELECT COUNT(*) FROM #Student),@i INT=1 -- Get the total amount of rows and set the counter to 1

WHILE @i<=@TotalRows -- begin as long as i (counter) is less than or equal to the total number of records

BEGIN -- ‘While’ loop begins here

SELECT @TableName=TableName from #Student WHERE TableId=@i

IF RIGHT(@TableName,6)<>'Backup' -- If a Backup table does not exist, then rename the table

BEGIN

SET @NewTableName=@TableName+'_Backup' -- Add ‘_Backup’ to the table’s current name

EXEC sp_rename @TableName,@NewTableName -- Rename the table as OLD table

END

ELSE

PRINT 'Backup table name already exists: '+@TABLENAME

SET @i=@i+1

END -- While loop ends here

DROP TABLE #Student

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
Tags: , Last modified: September 22, 2021
Close