Cursors (SQL Server)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Operations in a relational database act on a complete set of rows. For example, the set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, can't always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.
Cursors extend result processing by:
Allowing positioning at specific rows of the result set.
Retrieving one row or block of rows from the current position in the result set.
Supporting data modifications to the rows at the current position in the result set.
Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.
Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.
Remarks
In some scenarios, if there's a primary key on a table, a WHILE loop can be used instead of a cursor, without incurring in the overhead of a cursor.
However, there are scenarios where cursors aren't only unavoidable, they're actually needed. When that is the case, if there's no requirement to update tables based on the cursor, then use firehose cursors, meaning fast-forward and read-only cursors.
Cursor implementations
SQL Server supports three cursor implementations.
| Cursor implementation | Description |
|---|---|
| Transact-SQL cursors | Transact-SQL cursors are based on the DECLARE CURSOR syntax and used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server. They might also be contained in batches, stored procedures, or triggers. |
| Application programming interface (API) server cursors | API cursors support the API cursor functions in OLE DB and ODBC. API server cursors are implemented on the server. Each time a client application calls an API cursor function, the SQL Server Native Client OLE DB provider or ODBC driver transmits the request to the server for action against the API server cursor. |
| Client cursors | Client cursors are implemented internally by the SQL Server Native Client ODBC driver and by the DLL that implements the ADO API. Client cursors are implemented by caching all the result set rows on the client. Each time a client application calls an API cursor function, the SQL Server Native Client ODBC driver or the ADO DLL performs the cursor operation on the result set rows cached on the client. |
Type of cursors
SQL Server supports four cursor types.
Cursors can use tempdb worktables. Just like aggregation or sort operations that spill, these incur I/O costs, and are a potential performance bottleneck. STATIC cursors use worktables from its inception. For more information, see the worktables section in the Query processing architecture guide.
Forward-only
A forward-only cursor is specified as FORWARD_ONLY and READ_ONLY and doesn't support scrolling. These are also called firehose cursors and support only fetching the rows serially from the start to the end of the cursor. The rows aren't retrieved from the database until they're fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.
Because the cursor can't be scrolled backward, most changes made to rows in the database after the row was fetched aren't visible through the cursor. In cases where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index, the modified value is visible through the cursor.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server doesn't. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors. Transact-SQL cursors support forward-only static, keyset-driven, and dynamic cursors. The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable. When a database API cursor attribute or property is set to forward-only, SQL Server implements this as a forward-only dynamic cursor.
Static
The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. Static cursors detect few or no changes, but consume relatively few resources while scrolling.
The cursor doesn't reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor doesn't display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. If rows making up the result set are updated by other users, the new data values aren't displayed in the static cursor. The static cursor displays rows deleted from the database after the cursor was opened. No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.
Note
SQL Server static cursors are always read-only.
Because the result set of a static cursor is stored in a worktable in tempdb, the size of the rows in the result set can't exceed the maximum row size for a SQL Server table.
For more information, see the worktables section in the Query processing architecture guide. For more information on max row size, see Maximum capacity specifications for SQL Server.
Transact-SQL uses the term insensitive for static cursors. Some database APIs identify them as snapshot cursors.
Keyset
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, or keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.
Dynamic
Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they're made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor aren't visible until they're committed, unless the cursor transaction isolation level is set to read uncommitted. For more information on isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Note
Dynamic cursor plans never use spatial indexes.
Request a cursor
SQL Server supports two methods for requesting a cursor:
-
Transact-SQL
The Transact-SQL language supports a syntax for using cursors modeled after the ISO cursor syntax.
Database application programming interface (API) cursor functions
SQL Server supports the cursor functionality of these database APIs:
ADO (Microsoft ActiveX Data Object)
OLE DB
ODBC (Open Database Connectivity)
An application should never mix these two methods of requesting a cursor. An application that uses the API to specify cursor behaviors shouldn't then execute a Transact-SQL DECLARE CURSOR statement to also request a Transact-SQL cursor. An application should only execute DECLARE CURSOR if it sets all the API cursor attributes back to their defaults.
If neither a Transact-SQL nor API cursor is requested, SQL Server defaults to returning a complete result set, known as a default result set, to the application.
Cursor process
Transact-SQL cursors and API cursors have different syntax, but the following general process is used with all SQL Server cursors:
Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
Execute the Transact-SQL statement to populate the cursor.
Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
Close the cursor.