SQL Server Cursor Examples
October 8th 2008 10:34
SQL Server Cursor Examples
Problem
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server 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 created them 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. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So 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
Example Cursor
Here is an example cursor from 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','tem pdb')
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
Cursor Components
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_name> 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 NEXT statements - Assign the specific values from the cursor to the variables
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 statement - Destroys the cursor
Additional 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
Scripting SQL Server Database Objects Using DMO (Distributed Management Objects)
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 2000
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
Cursor Analysis
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 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 serialized manner, cursors are a viable option.
Administrative tasks - Many administrative tasks 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.
Cursor Alternatives
Below outlines alternatives to cursor based logic which could meet the same needs:
Set based logic
SQL Server Integration Services (SSIS) or Data Transformation Services (SSIS)
WHILE loop
COALSCE
The Many Uses of Coalesce in SQL Server
sp_MSforeachdb
Run The Same SQL Command Against All SQL Server Databases
sp_MSforeachtable
Here is a tip with example code - Really Long Link
CASE expression
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
Problem
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server 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 created them 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. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So 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
Example Cursor
Here is an example cursor from 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
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tem pdb')
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
Cursor Components
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_name> 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 NEXT statements - Assign the specific values from the cursor to the variables
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 statement - Destroys the cursor
Additional 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
Scripting SQL Server Database Objects Using DMO (Distributed Management Objects)
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 2000
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
Cursor Analysis
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 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 serialized manner, cursors are a viable option.
Administrative tasks - Many administrative tasks 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.
Cursor Alternatives
Below outlines alternatives to cursor based logic which could meet the same needs:
Set based logic
SQL Server Integration Services (SSIS) or Data Transformation Services (SSIS)
WHILE loop
COALSCE
The Many Uses of Coalesce in SQL Server
sp_MSforeachdb
Run The Same SQL Command Against All SQL Server Databases
sp_MSforeachtable
Here is a tip with example code - Really Long Link
CASE expression
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
| 53 |
| Vote |
Add To: 
