A SQL Server cursor is a database object that is used to process the data in a set row by row. In this article, we will review how to create a cursor and different attributes used while declaring a cursor.

Creating cursor

Below are the steps involved in creating a cursor.

• Declare – Declares the cursor with a name and the select statement which populates the result set
• Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
• Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
• Close – Closes the current result set of SQL Server cursor and can be reopened
• Deallocate – Removes cursor reference and releases all the resources associated with a cursor

Let us create a sample table, insert a few rows and perform cursor operations on the table with different attributes.

The syntax for creating a cursor with default attributes.

By default, the scope of the cursor is defined based on the Default cursor setting at the database level. To check or change the current setting, navigate to the database in SQL Server management studio, right click on the database and click on Properties. Click on Options. You can see the current setting is GLOBAL in this case. If you want to change the default scope of SQL Server cursor, then click on the drop down and change it to LOCAL and click on OK

You can also change this setting using T-SQL. Execute the below statement to change the default setting to LOCAL.

Execute the below statement to change the default setting to GLOBAL. Replace database name with your database name.

LOCAL

If the cursor is created with LOCAL attribute then the scope of the cursor is limited to that batch, stored procedure or trigger in which the cursor was created.

Let us try declaring a local cursor in one batch and open it in another batch.

And when you try to open the SQL Server cursor in another batch it throws errors as the scope of the cursor is limited to a batch where it was created. Please refer to the below image for the error.

All the statements referencing the cursor name should go in one batch if the cursor is declared with local attribute.

Please refer to the below image when the cursor is declared as local and all the referencing statements are executed in one batch.

GLOBAL

If the cursor is created with GLOBAL attribute, the scope is cursor is not limited to the batch where it was created, and the cursor name can be referenced by any batch in the same connection.

Let us declare SQL Server cursor with a global attribute in one batch and try to use the cursor reference in another batch.

Now we will open cursor and fetch the current record in another batch.

We must issue a DEALLOCATE command on the cursor to remove the cursor references or the cursor references will be removed only on closing the connection.

FORWARD_ONLY

When a cursor is specified with FORWARD_ONLY, it can be scrolled from first to the last row using fetch next. All other fetch options are not supported. All the data changes made by other users before fetching the row are visible.

Let us create a FORWARD_ONLY cursor and try to use other fetch options like ‘LAST’ and ‘PRIOR’. It throws an error as shown in the below image.

Only FETCH NEXT option is supported when the cursor is created with FORWARD_ONLY.

SCROLL

When a cursor is created with SCROLL attribute, all the fetch options are available. Below are different fetch options available.

• FIRST
• LAST
• PRIOR
• NEXT
• RELATIVE
• ABSOLUTE

Please refer to the below image for result set when a cursor is created with scroll and used LAST and PRIOR fetch options.

All the data changes made after opening the cursor are not visible. Create a cursor with a scroll and open it first.

Now open another session in SQL Server management studio and update the record where id=1

Now go back to a session where the SQL Server cursor was created on executing below fetch statement. It still shows the old value.

SCROLL and FAST_FORWARD attributes cannot be specified together in a cursor.

STATIC

Static cursor when opened creates a copy of the data returned by the select statement specified in declare statement and any further data changes are not visible. We cannot update or delete data in using the CURRENT OF as it is read-only.

Create a cursor with STATIC and open it first.

Now open another session in SQL Server management studio and update the record where id=1

Now go back to a session where the cursor was created on executing below fetch statement two times. It still shows the old value.

DYNAMIC

When a cursor is created with dynamic attribute all the changes made to rows inside cursor or outside cursor are visible when you fetch the new record. By default, if STATIC or FAST_FORWARD is not specified the cursor is created as dynamic.

To illustrate this, declare and opening the cursor.

In another session insert few more records or update them.

Now issue a fetch statement in the same session where you created a cursor.

We can see the data changes done outside of the cursor are reflected while fetching rows.

FAST_FORWARD

This enables READ_ONLY and FORWARD_ONLY on the cursor. This is the fastest SQL Server cursor and any changes to data are not visible once the cursor is opened. FAST_FORWARD cannot be specified along with SCROLL or FOR_UPDATE.

The syntax for creating a cursor with FAST_FORWARD

READ_ONLY

This attribute on the cursor will not allow updates and deletes within the cursor using the CURRENT OF. Any data changes that occurred after opening cursor is not visible.

SCROLL_LOCKS

When the cursor is created with SCROLL_LOCKS attribute the rows which are fetched into cursor are locked.

Execute the below statements to create a cursor with SCROLL_LOCKS.

Now open another session in SQL Server management studio and try to update the data in the table. It will be blocked by the above SQL Server cursor.

OPTIMISTIC

This attribute will not update or delete data inside the cursor by using the CURRENT OF when the data is modified by another user in a different session after fetch. It throws an error as shown in the below image.

It compares timestamp or checksum to see if the row was modified after fetch.

FOR UPDATE

This attribute specifies the updatable columns in the cursor. Only columns which are specified are updatable. If no column list was provided all columns of the table can be updated.

Sample cursor with no columns specified for an update. In this case, all columns in the table test can be updated inside the SQL Server cursor using the CURRENT OF.

Sample cursor with only one column specified in the update list. In this case, we cannot update the column “id”.

If we try to update the column which is not in the update of the list, then it throws an error as shown in below image.

To view properties of the cursor, use inline function sys.dm_exec_cursors. This returns the information of open cursors on all databases in the instance. This returns the information like on which session the cursor was opened, name of the cursor and properties. Please refer to below image for result set returned by the inline function.

I hope you have benefitted from this SQL Server cursor article. Feel free to provide feedback in the comments below.