Summary:
From this post you will learn how to use CURSOR statement in SQL.
What is Database 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.
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 life cycle
Explanation of Cursor Syntax in SQL Server
- 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 NEXT 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 statement - Destroys the cursor.
These are steps for using a cursor:
1. Declare a cursor.
DECLARE cursor_name CURSOR
FOR select_statement;
To declare a cursor, you specify its name after the DECLARE keyword with the CURSOR data type and provide a SELECT statement that defines the result set for the cursor.
2. Open and populate the cursor by executing the SELECT statement.
OPEN cursor_name;
3. Then, fetch a row from the cursor into one or more variables.
FETCH NEXT FROM cursor INTO variable_list;
SQL Server provides the @@FETCHSTATUS function that returns the status of the last cursor FETCH statement executed against the cursor; If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful. You can use the WHILE statement to fetch all rows from the cursor as shown in the following code:
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursor_name;
END;
4. Close the cursor.
CLOSE cursor_name;
5. Deallocate the cursor.
DEALLOCATE cursor_name;
Example:
We’ll use the prodution.products table from the sample database to show you how to use a cursor:
1. Declare two variables to hold product name and list price, and a cursor to hold the result of a query that retrieves product name and list price from the production.products table:
DECLARE
@product_name VARCHAR(MAX),
@list_price DECIMAL;
DECLARE cursor_product CURSOR
FOR SELECT
product_name,
list_price
FROM
production.products;
2. Open the cursor:
OPEN cursor_product;
3. Fetch each row from the cursor and print out the product name and list price:
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @product_name + CAST(@list_price AS varchar);
FETCH NEXT FROM cursor_product INTO
@product_name,
@list_price;
END;
4. Close the cursor:
CLOSE cursor_product;
5. Deallocate the cursor to release it.
DEALLOCATE cursor_product;
Output:
Limitations and Restrictions
You cannot use cursors or triggers on a table with a clustered columnstore index. This restriction does not apply to nonclustered columnstore indexes; you can use cursors and triggers on a table with a nonclustered columnstore index.
