What Is Cursor in SQL Server And Why Is It Used?

Cursors in SQL Server

So, what is a TSQL cursor? SQL Cursor is a database object that is used to manipulate data in a set, in row-by-row processing (or one row at the time). Instead of the T-SQL commands which operate on ALL the rows in the result set.

Key takeaways

1. SQL cursor overview:
   Cursors in SQL Server allow row-by-row data manipulation, contrasting with T-SQL’s full result set operations. While useful for specific tasks, they can very negatively impact performance.
2. Cursor types and challenges:
   SQL Server offers various cursor types: STATIC, FAST_FORWARD, DYNAMIC, and KEYSET. Their misuse, primarily due to a misunderstanding of set operations, can lead to performance issues and table locking.
3. Cursors vs. alternatives:
   Temporary tables are a notable alternative to cursors. They store result sets and, with set-based operations, can offer better performance. It’s essential to favor set operations over row-by-row approaches in SQL for optimized results.

When to use cursors?

There are some conditions when we want to get a record from one table and need to insert it into another with performing some condition or logic. A cursor is a similar approach to any looping mechanism found in any other programming.

What are the biggest problems with cursors?

Problems when it comes to performance.

When cursor is OK to use?

They are great for one-time type of work. Example: pull multiple rows, do multiple actions on them. Maybe even execute PowerShell or xp_cmdshell. As a DBA, I use this quite a bit. Take one row, do a bunch of actions, and move to another row. This is when I don’t care what performance looks like. I just need to execute multiple commands per each row.

When NOT to use cursors?

Don’t put the cursor into anything that will be used very frequently. Especially, stored proc that will need to run fast. Or very frequently. I would say that it will not be as fast as using other methods.

How to create a SQL Server cursor?

The process of using a SQL cursor (starting with MS SQL 2008) is:

1. Declaring cursor by defining the SQL statement.
2. Opening the cursor for storing data retrieved from the result set.
3. Rows can be fetched from the cursor one by one or in a block to do data manipulation.
4. The cursor should be closed explicitly after data manipulation.
5. Cursors should be deallocated to delete cursor definitions and release all the system resources.

Cursor syntax

What are the different cursor types?

Microsoft SQL Server supports the following types of cursors.

1. STATIC CURSOR
   Populates the result set during cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward.
2. FAST_FORWARD (default type of cursor)
   It is identical to the static except that you can only scroll forward.
3. DYNAMIC
   Additions and deletions are visible to others in the data source while the cursor is open. Unlike static cursors, all the changes made in the Dynamic cursor will reflect the Original data. You can use this cursor to perform INSERT, DELETE, and UPDATE operations.
4. KEYSET
   This is like a dynamic cursor except we can’t see records others add. If another user deletes a record, it is inaccessible from our record set.

Cursor examples

The example below renames all tables in the sample database named as Spreadsheet, by adding ‘_Backup’ to each table’s name while also ensuring that tables containing ‘_Backup’ in their name will not be renamed again by running the following code:

DECLARE @TableName VARCHAR(50) -- Old table name
	,@NewTableName VARCHAR(50) -- New table name

SELECT ROW_NUMBER() OVER (
		ORDER BY T.TABLE_NAME
		)
	,T.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE TABLE_CATALOG = ‘SpreadSheet’ -- Get all tables name from database SpreadSheet

OPEN Student_Cursor

FETCH NEXT
FROM Student_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
	IF RIGHT(@TableName, 6) <> ’Backup’ -- If Backup table does not exist, then rename the table
	BEGIN
		SET @NewTableName = @TableName + ’_Backup’ -- Add ‘_Backup’ to current name

		EXEC sp_rename @TableName
			,@NewTableName -- Rename the table
	END
	ELSE
		PRINT ‘Backup TABLE name already

	exists: ‘ + @TABLENAME

	FETCH NEXT
	FROM Student_Cursor -- Get next row data into cursor and store it in variables
	INTO @TableName
END

CLOSE Student_Cursor -- Close cursor locks on the rows

DEALLOCATE Student_Cursor -- Release cursor reference

Template of how to declare a cursor in SQL Server

DECLARE @product_id INT;
DECLARE @product_name VARCHAR(50);

DECLARE product_cursor CURSOR
FOR
SELECT product_id
	,product_name
FROM products;

OPEN product_cursor;

FETCH NEXT
FROM product_cursor
INTO @product_id
	,@product_name;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Do something with the row data
	PRINT 'Product ID: ' + CAST(@product_id AS VARCHAR(10));
	PRINT 'Product Name: ' + @product_name;

	-- Fetch the next row
	FETCH NEXT
	FROM product_cursor
	INTO @product_id
		,@product_name;
END;

CLOSE product_cursor;

DEALLOCATE product_cursor;

In this example, the cursor product_cursor is declared to fetch the product_id and product_name columns from the products table. The OPEN statement opens the cursor, and the FETCH NEXT statement retrieves the first row from the result set and stores the values in the variables @product_id and @product_name.

The WHILE loop iterates through the rows in the result set, processing each row in turn. The @@FETCH_STATUS function returns the status of the last fetch operation, so the loop continues as long as there are more rows to fetch. The FETCH NEXT statement retrieves the next row from the result set and stores the values in the variables again.

Finally, the CLOSE statement closes the cursor, and the DEALLOCATE statement frees the memory used by the cursor. It is important to always close and deallocate cursors when you are done using them, to avoid memory leaks and other issues.

How to fetch data using a cursor in SQL Server

A cursor is a database object that allows users to traverse through the result set of a SELECT statement one row at a time. Cursors are often used when working with complex queries that return large amounts of data, as they provide a way to process the data row by row.

To fetch data using a cursor in SQL Server, you can follow these basic steps:

Step 1 – Declare a cursor:
To declare a cursor, you need to specify its name and the SELECT statement that defines the result set for the cursor. For example, you can declare a cursor named myCursor as follows:

DECLARE myCursor CURSOR
FOR
SELECT column1
	,column2
	,column3
FROM myTable
WHERE column4 = 'value';

Step 2 – Open the cursor:
Once you have declared the cursor, you need to open it using the OPEN statement. This will initialize the cursor and make the first row of the result set available for processing. For example:

OPEN myCursor;

Step 3 – Fetch data from the cursor:
To fetch data from the cursor, you can use the FETCH statement. This will retrieve the next row of the result set and make it available for processing. For example:

FETCH NEXT
FROM myCursor
INTO @column1
	,@column2
	,@column3;

Here, “@column1”, “@column2”, and “@column3” are variables that you have declared to store the values of the columns retrieved by the FETCH statement.

Step 4 – Process the data:
Once you have fetched the data from the cursor, you can process it as needed. This may involve performing calculations, updating values, or inserting data into another table.

Step 5 – Repeat steps 3-4 until all rows have been processed:
To process all rows in the result set, you need to repeat steps 3-4 until the FETCH statement returns a value of NULL, indicating that there are no more rows to fetch. For example:

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Process data here 
	FETCH NEXT
	FROM myCursor
	INTO @column1
		,@column2
		,@column3;
END

Here, the WHILE loop will continue to execute as long as the FETCH statement returns a value of 0, indicating that there are still rows to be processed.

Step 6 – Close the cursor:
Once you have finished processing the data, you should close the cursor using the CLOSE statement. This will release any resources that were allocated for the cursor. For example:

CLOSE myCursor;

Step 7 – Deallocate the cursor:
Finally, you should deallocate the cursor using the DEALLOCATE statement. This will remove the cursor from memory. For example:

DEALLOCATE myCursor;

By following these steps, you can use a cursor to fetch data from a result set in SQL Server and process it row by row.

However, I should note that cursors can be resource-intensive and should be used judiciously. In some cases, it may be more efficient to use other techniques such as temporary tables or set-based operations.

Cursor limitations

Cursors are used frequently as the wrong tool for the wrong task. They’re used for quick programming when a developer does not have a full understanding of set operations.

The problem with the cursor is that, in most cases, we can use JOINS, even WHILE clauses, SSIS packages, or other alternative methods to get the same result quicker, with less impact on performance output and even writing fewer lines of syntax.

The principal factor problem affecting cursor speed is the number of rows and columns brought into it. While a cursor is being populated, the table being iterated is locked. Row operations can take a long time to execute depending upon the type of task being performed on each row. While the cursor is open, a table cannot be accessed or updated by the other users.

Cursors could be used in some applications for serialized, but generally, they should be avoided because they bring a negative impact on performance, especially when operating on a large set of data.

SQL Server cursor alternatives

There are many alternatives for SQL cursors, so let’s look at the temporary table strategy in detail. Temporary tables have been in use for a long time and provide an excellent way to replace cursors for large data sets. Temporary tables can hold the result set so that we can perform the necessary operations by processing it with an iterating algorithm such as a ‘while’ loop.

We will use a temporary table to rename all tables in the sample by following these steps:

1. Declare a temporary table
2. Store table names and IDs
3. Set the counter to 1 and get the total number of records from the temporary table
4. Use a ‘while’ loop as long as the counter is less than or equal to the total number of records
5. Within the loop, rename tables one by one if they are not already renamed and increase the counter for each table

DECLARE @TableName VARCHAR(50) -- Old table name
	       
	,@NewTableName VARCHAR(50) -- New table name
	   
	,@TotalRows INT -- Total amount of rows
	   
	,@i INT– LOOP counter

CREATE TABLE #TargetTables -- Declaring a temporary table
	(
	  TableId INT
	,TableName VARCHAR(40)
	)

INSERT INTO #TargetTables -- insert into the temporary table
SELECT ROW_NUMBER() OVER (
		ORDER BY T.TABLE_NAME
		)
	,T.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE TABLE_CATALOG = ‘SpreadSheet’ -- Get all tables name from database SpreadSheet

SET @TotalRows = (
		SELECT COUNT(*)
		FROM #TargetTables
		) -- Get the total amount of rows and
SET @i = 1 -- Set the counter to 1

WHILE @i <= @TotalRows -- begin as long as i (counter) is less than or equal to the total number of records
BEGIN -- Loop begins here
	SELECT @TableName = TableName
	FROM #TargetTables
	WHERE TableId = @i

	IF RIGHT(@TableName, 6) <> ’Backup’ -- If a Backup table does not exist then rename the table
	BEGIN
		SET @NewTableName = @TableName + ’_Backup’ -- Add ‘_Backup’ to current name

		EXEC sp_rename @TableName
			,@NewTableName -- Rename the table
	END
	ELSE
		PRINT ‘The TABLE name already

	exists: ‘ + @TABLENAME

	SET @i = @i + 1
END -- While loop ends here

DROP TABLE #TargetTables

Conclusions

It`s a good practice in T-SQL to think about making operations on sets of data, rather than thinking programmatically, and processing row by row in the loop. Cursors are a great tool and not always evil, but when performance matters, just find another way to accomplish the same thing.