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.
A SQL cursor provides access to data one row at a time thereby giving you more (row-by-row) control over the result set.
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:
- Set Theory
- 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:
- Declare Cursor
- Open Cursor
- Fetch rows
- Close Cursor
- 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:
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:
- Releases the result set currently held by the cursor
- Frees any cursor locks on the rows by the cursor
- 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:
- SQL Server 2008 and upward versions
- 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.
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:
- Please try to create and rename indexes of all the tables of a sample database through the cursor.
- Please try to revert the renamed tables in this article back to original names using the cursor.
- Please try populating tables with a lot of rows and measure the statistics and time for the queries with and without the cursor.
- Writing Simple SQL Query from Reference Table - March 2, 2021
- How to Write Simple SQL Queries from a Blank Database - February 25, 2021
- SQL Server DELETE – Removing One or More Rows from a Table with Slightly Advanced Scenarios - February 9, 2021