SQL Server Cursor Example

Problem

A SQL Server cursor is a set of T-SQL logic that loops over a predetermined number of rows one at a time. The purpose of 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. Development, DBA, and ETL processes rely on SQL Server cursors. Can you provide a SQL cursor example? Can you give any guidance on when to use a SQL Server cursor?

Solution

Cursor in SQL Server

Let’s break down the SQL Server cursor example in this SQL tutorial and notate which sections 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 *
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 ('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 = 'C:\Backup\' + @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 SQL cursor example above, cursors include these components:

DECLARE Statements

Declare variables used in the code block such as file names, database names, account numbers, etc. Specify the variable name and data type.

Update the logic based on your needs

SET\SELECT Statements

Initialize the variables to a specific value.

DECLARE CURSOR Statement

Declare the cursor with a specific name (i.e., db_cursor in this SQL Tutorial) that you will use throughout the logic along with the business logic (SELECT SQL statement) to populate the records.

NOTE – There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be one or many variables and associated columns.
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
Update the logic based on your needs

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 back up a database to a specific path and file name, but this could be any DML or administrative logic.

Update the logic based on your needs

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 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 seen 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 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 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 and 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 only occur when the final data is processed. All 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 (read here), 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 SQL Server cursor options beyond the syntax in this tip.

SQL Server Cursor Alternatives

Learn more about SQL Server cursors and alternatives:

How to Avoid Cursors 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 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 wrote 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.

SQL Cursor Example Resources

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

SQL Server Cursor Final Thoughts and Opinion

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. 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?

Next Steps

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.

Resources

Community

Subscribe