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:
- Declare Cursor
- Open Cursor
- Fetch rows
- Close Cursor
- 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:
- We need to add ‘_Backup’ to names of all existing tables in a database
- 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:
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:
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:
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:
- Declare a table variable
- Store tables names and ids in the table variable we declared
- Set the counter to 1 and get the total number of records from the table variable
- Use a ‘while’ loop as long as the counter is less than or equal to the total number of records
- 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:
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:
- Declare a temporary table
- Store tables names and ids in the temporary table we just declared
- Set the counter to 1 and get the total number of records from the temporary table
- Use a ‘while’ loop as long as the counter is less than or equal to the total number of records
- 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 ‘_Backup‘ from 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:
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:
- 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)
- Revert the names of the tables from this article back to their initial names using alternative methods (temporary tables and table variables)
- 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
