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.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.

You should avoid the use of the cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

These are steps for using a cursor:

Creating a SQL Server cursor is a consistent process.  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.
    • 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 statement) to populate the records the cursor will need. 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 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.
-- 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 the 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 the cursor
CLOSE db_cursor  

-- 7 - Deallocate the cursor
DEALLOCATE db_cursor 

Types of Cursors
There are different types of cursors in sql server as listed below. 
1. Static Cursors
A static cursor populates the result set at the time of cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.
No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened). By default static cursors are scrollable. SQL Server static cursors are always read-only.

2. Dynamic Cursors
A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
3. Forward Only Cursors
A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.
There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.
A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.
A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.

4. Keyset Driven Cursors
A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of the cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

Cursor Scope
 
Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.
  1. GLOBAL - specifies that the cursor name is global to the connection.
  2. LOCAL - specifies that the cursor name is local to the Stored Procedure, trigger, or query that holds the cursor.
Data Fetch Option in Cursors
 
Microsoft SQL Server supports the following two fetch options for data:
  1. FORWARD_ONLY - Specifies that the cursor can only be scrolled from the first to the last row.
  2. SCROLL - It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE).

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