SQL Server Cursor Example

By: Jeremy Kadlec   |   Updated: 2023-10-25   |   Comments (77)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > TSQL

Problem

In my T-SQL code, I always use set based operations. I have been told these types of SQL queries are what SQL Server relational database engine is designed to process and it should be quicker than serial processing. I know cursors exist, but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft included them in SQL Server for a reason so they must have a place where they can be used in an efficient manner.

Solution

In some circles, cursors are never used. In others, they are a last resort. And in other groups they are used regularly. In each of these camps, they have different reasons for their stand on cursor usage in the DBMS. Regardless, they probably have a place in particular circumstances and not in others. It boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor-based processing is appropriate or not. To get started let's do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

Let's first provide a SQL Server Cursor example then answer all of the pertinent questions in this SQL tutorial.

SQL Cursor Example

Here is an example SQL Server cursor from this tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
      BACKUP DATABASE @name TO DISK = @fileName 

      FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

What is a SQL Server Cursor

A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner. SQL Server cursors are used for Development, DBA and ETL processes. There are many options and types of cursors, such as:

• Read_Only
• Fast_Forward
• Forward_Only
• Scroll
• Static Cursors
• Dynamic
• Local
• Global
• Keyset
• Scroll_Locks
• Optimistic

How to Write a Cursor in SQL Server with Transact-SQL

Creating a SQL Server cursor with Transact-SQL is a consistent process that can process data on a set of rows. Once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let's walk through the steps:

1. Declare your variables (file names, database names, account numbers, etc.) that you need in the logic and initialize the variables. Specify the variable name and data type.
   • This logic would be updated based on your needs.
2. Declare cursor with a specific name (i.e. db_cursor in this tip) that you will use throughout the logic along with the business logic (SELECT SQL statement) to populate the records the cursor rows can be defined using user defined or temporary tables including filtering with a WHERE clause and ordering data with an ORDER BY clause. The cursor name can be anything meaningful. This is immediately followed by opening the cursor.
   • This logic would be updated based on your needs.
3. Fetch a record from cursor to begin the data processing.
   • NOTE - There are an equal of number of variables declared for the cursor, columns in the SELECT SQL statement and variables in the Fetch logic. In the example in this tip there is only one variable, one column selected and variable fetched, but if five pieces of data were needed for the cursor then five variables would need to be selected and fetched as well.
4. The data process is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
   • This logic would be updated based on your needs.
5. Fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
6. Once all of the data has been processed, then you close cursor.
7. As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

Cursor in SQL Server

Based on the code and explanations above, let's break down the SQL Server cursor example and notate which sections would need to be updated when using this code.

-- 1 - Declare Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

-- Initialize Variables
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- 2 - Declare Cursor
DECLARE db_cursor CURSOR FOR 
-- Populate the cursor with your logic
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

-- Open Cursor
OPEN db_cursor

-- 3 - Fetch the next record from the cursor
FETCH NEXT FROM db_cursor INTO @name  

-- Set the status for the cursor
WHILE @@FETCH_STATUS = 0  
 
BEGIN  
	-- 4 - Begin the custom business logic
	-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
   	SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
  	BACKUP DATABASE @name TO DISK = @fileName 

	-- 5 - Fetch the next record from the cursor
 	FETCH NEXT FROM db_cursor INTO @name 
END 

-- 6 - Close cursor
CLOSE db_cursor  

-- 7 - Deallocate cursor
DEALLOCATE db_cursor 

Explanation of Cursor Syntax in SQL Server

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH statements - Assign the specific values from the cursor to the variables to match the DECLARE CURSOR FOR and SELECT statement
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing, multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE cursor statement - Destroys the cursor

Why Use a Cursor in SQL Server

Although using an INSERT, UPDATE or DELETE statement to modify all of the applicable data in one transaction is generally the best way to work with data in SQL Server, a cursor may be needed for:

• Iterating over data one row at a time
• Completing a process in a serial manner such as SQL Server database backups
• Updating data across numerous tables for a specific account
• Correcting data with a predefined set of data as the input to the cursor

When to Use a SQL Server Cursor

The analysis below is intended to serve as insight into various scenarios where cursor-based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic (INSERT, UPDATE or DELETE on applicable rows) makes the most sense for short transactions. Our team has run into a third-party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes. A cursor-based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in a serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks such as database backups or Database Consistency Checks need to be executed in a serial manner, which fits nicely into cursor-based logic. But other system-based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set-based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor-based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database, the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor. However, with a set-based approach that may not be the case until an entire set of data is completed. As such, troubleshooting the row with the problem may be more difficult.

SQL Server Cursor Types

Simon Liew has written a detailed technical tip on five Different Ways to Write a Cursor in SQL Server which includes the following:

• Most Common SQL Server Cursor Syntax
• SQL Server Cursor as a Variable
• SQL Server Cursor as Output of a Stored Procedure
• SQL Server Cursor Current Of Example
• SQL Server Cursor Alternative with a WHILE Loop

This tip provides sample code that can be used to expand your SQL Server cursor options beyond the syntax in this tip.

SQL Server Cursor Alternatives

Learn more about SQL Server Cursors and alternatives:

• Run The Same SQL Command Against All SQL Server Databases
• Iterate through SQL Server database objects without cursors
• Making a more reliable and flexible sp_MSforeachdb
• SQL Server CROSS APPLY and OUTER APPLY
• SQL Server Join Example

How to Avoid Cursor in SQL Server

Below outlines SQL Server T-SQL coding options to avoid SQL Server Cursors:

• Set based logic
  • INSERT or SELECT INTO or INSERT... SELECT to add records to a table as a single transaction
  • UPDATE to modify one or many rows in a single transaction
  • DELETE or TRUNCATE to remove records from a table
  • MERGE branching logic to INSERT, UPDATE or DELETE data based on criteria
• Consider SQL Server Integration Services (SSIS) to loop through data primarily for data extraction, transformation and loading processes between databases
• WHILE command to loop to over records in a sequential manner
  • Optimize Large SQL Server Insert, Update and Delete Processes by Using Batches
• COALSCE command to process NON-NULL values
  • The Many Uses of Coalesce in SQL Server
• sp_MSforeachdb SQL Server system stored procedure to loop over each database on an instance
  • Run The Same SQL Command Against All SQL Server Databases
• sp_MSforeachtable SQL Server system stored procedure to loop over each table in a database
  • Iterate through SQL Server database objects without cursors
• CASE expression which can include some branching logic to process data with a SELECT statement
  • Using the CASE expression instead of dynamic SQL in SQL Server
• Repeat a batch with the GO command
  • Executing a T-SQL batch multiple times using GO

Difference Between While Loop and Cursor in SQL Server

Daniel Farina has written an interesting article (SQL Server Loop through Table Rows without Cursor) comparing the SQL Server While Loop and Cursors. He covers the following:

• Using a While Loop Instead of Cursors in SQL Server
• Pros and Cons of Using Cursors to Iterate Through Table Rows in SQL Server
• Pros and Cons of Using a While Loop to Iterate Through Table Rows in SQL Server
• Example of a Basic Cursor to Loop through Table Rows in SQL Server
• Example of a Basic While Loop to Cycle through Table Rows in SQL Server

The code samples in this tip are valuable to illustrate the differences between cursors in SQL Server and the While Loop.

Additional SQL Server Cursor Examples

In the example above, backups are issued via a cursor. Check out these other tips that leverage cursor-based logic:

• Managing SQL Server Database Fragmentation
• SQL Server script to rebuild all indexes for all tables and all databases
• SQL Server Index Analysis Script for All Indexes on All Tables
• Standardize your SQL Server data with this text lookup and replace function
• Automating Transaction Log Backups for All SQL Server Databases
• Searching and finding a string value in all columns in a SQL Server table
• Script All Server Level Objects to Recreate SQL Server
• Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server
• Capacity Planning for SQL Server 2000 Database Storage
• Automate Restoration of Log Shipping Databases for Failover in SQL Server
• Determining space used for each table in a SQL Server database
• Auditing Windows Groups from SQL Server
• SQL Server Find and Replace Values in All Tables and All Text Columns
• Easing the SQL Server Database Capacity Planning Burden
• Index Metadata and Statistics Update Date for SQL Server

Next Steps

• When you are faced with a data processing decision, determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task. Using a cursor could be a reasonable alternative or not. You be the judge.
• If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one-time process or nightly processing, this could do the trick.
• If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
• As you evaluate a cursor-based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.

View all my tips