Tips for using SQL Server 2016 cursors
Reduce the number of rows to process in the cursor.
To reduce the cursor result set use the WHERE clause in the cursor’s select statement.
Do not forget to close SQL Server 2016 cursor when its result set is not needed.
To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command
releases the cursor result set and frees any cursor locks held on the rows on which the cursor
is positioned. Keep in mind, that in SQL Server 2016 only cursors that are opened within the
current transaction are closed at transaction commit time, other cursors that are opened
before the start of the transaction remain open and you need to close them manually.
Try to avoid using insensitive, static and keyset cursors, whenever possible.
These types of cursor produce the largest amount of overhead on SQL Server 2016, because
they cause a temporary table to be created in TEMPDB, which results in some performance
degradation.
Use FAST_FORWARD cursors, whenever possible.
The FAST_FORWARD cursors produce the least amount of overhead on SQL Server 2016,
because there are read-only cursors and can only be scrolled from the first to the last row.
Use FAST_FORWARD cursor if you do not need to update cursor result set and the
FETCH NEXT will be the only used fetch option.
Try to avoid using SQL Server 2016 cursors, whenever possible.
SQL Server cursors can results in some performance degradation in comparison with select
statements. Try to use correlated subquery or derived tables, if you need to perform
row-by-row operations.
Use READ ONLY cursors, whenever possible, instead of updatable cursors.
Because using cursors can reduce concurrency and lead to unnecessary locking, try to use
READ ONLY cursors, if you do not need to update cursor result set.
Reduce the number of columns to process in the cursor.
Include in the cursor’s select statement only necessary columns. It will reduce the cursor
result set. So, the cursor will use fewer resources. It can increase cursor performance and
reduce SQL Server 2016 overhead.
Use INSENSITIVE option when declare a cursor if you need that modifications made to
base tables are not reflected in the data returned by fetches made to this cursor.
When you declare an insensitive cursor, a temporary copy of the cursor’s data will be created
and stored in the tempdb database, and all requests to the cursor will be answered from this
temporary table. Otherwise, all committed deletes and updates made to the underlying tables
are reflected in subsequent fetches.
Avoid using the WHERE CURRENT OF syntax of the UPDATE command.
If you specify the FOR UPDATE clause in the cursor declaration, you can use the
WHERE CURRENT OF clause of the UPDATE command to update the last row
fetched, but this way provides slow performance in comparison with using a regular
UPDATE statement.
Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the
only used fetch option.
If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to
use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. In SQL Server 2016, both
FAST_FORWARD and FORWARD_ONLY keywords can be used in the same
DECLARE CURSOR statement.
Consider returning the entire rowset to the client instead of using a server-side cursor
if the number of rows is small.
In this case, returning the entire rowset to the client usually provides better performance.
Use dynamic cursor if you need a cursor that reflects all data changes made to the rows
in its result set as you scroll around the cursor.
In this case, the data values, order, and membership of the rows can be changed on each fetch.
Note. The ABSOLUTE fetch option cannot be used with dynamic cursors.
Consider using a user-defined function instead of cursor.
You can use a user-defined function (UDF) instead of cursor when you use cursor to perform a
calculation on values that come from each row in its rowset. Using UDF usually provides better
performance than using cursor and may reduce SQL Server 2016 overhead.
Use the UPDLOCK table hint in SQL Server 2016 scroll cursor to reduce the chance
of deadlocks for concurrent cursors.
In SQL Server 2016, scroll lock cursors use the Shared lock. So, SQL Server 2016 scroll cursors
allow better concurrency but opens up the chance of deadlocks for concurrent cursors.
Do not forget to deallocate SQL Server 2016 cursor when the data structures
comprising the cursor are not needed.
To deallocate SQL Server 2016 cursor, you can use DEALLOCATE {cursor_name} command. This
command removes a cursor reference and releases the data structures comprising the cursor.