SQL Server Cursor and its types

In this post we will learn about what is cursor and what are its different types. The actual practical example, we will see in the next post.

Concept of Cursor

A cursor is a database object which is used to read or edit data one row at a time from a data set where data set can be based on one or more tables of the database. Cursor provides procedural capability to SQL developers to edit or read the desired rows of the data set. The cursor is thus a technical construct to maneuver the record of a result set.

In short, the concept of cursor is all about reading or editing one row of tabular data in a database at a time.

When SELECT query is used to fetch a set of records, we may require the procedure to process one or more records. The cursor allows us to read or update data of different columns for a given row. Cursor can move up and down in the record set and data of the active row can be read or changed by the help of the cursor.

The most important point to remember about the cursor is this that the cursor points to the active row of the result set.

Syntax of Cursor

Cursor is a database object. It is declared as an object variable using DECLARE statement. The type of cursor is specified using a keyword after the name of the cursor. Specifying the cursor type is optional. The FOR statement is used to specify the SELECT query which is further followed by the next FOR statement to specify whether the cursor is for update or read only.

Look at the following ISO Syntax for cursor.

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

    FOR select_statement

    [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

[;]

T-SQL Extended Syntax for cursor is as follows.

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

    [ FORWARD_ONLY | SCROLL ]

    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

    [ TYPE_WARNING ]

    FOR select_statement

    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

In the above syntax, the value inside square brackets implies the optional features and pipe symbol stands for mutually exclusive case. For example, we can use either LOCAL or GLOBAL but not both.

Life Cycle of Cursor

Cursor is a database object variable. We know that an object variable is declared and initialized with a value. Once the role of the variable is over, it is de-initialized to reclaim the memory. A cursor life cycle is as follows:

1. Declaring the cursor
2. Opening the cursor
3. Fetching the cursor
4. Closing the cursor
5. De-allocating the cursor

Why Is Cursor Required

Already we have discussed that cursor deals with one row at a time. When we use SELECT query, the dataset returned may not be well enough to manipulate specific rows of result set. To edit or read only specific row of the result set, we use the cursor. The WHILE loop is used to iterate the result set. The FETCH NEXT FROM <cursor name> expression is used to fetch next row into the cursor variable.

Types of Cursor

There are three types of cursors in SQL Server which are as follows:

1. Static cursor
2. Dynamic cursor
3. Keyset cursor

Static Cursor

Throughout the life of static cursor, the static cursor shows the same data of the data source when it was opened, even though change may have taken place in the source data after opening the cursor. It means that If we make change to the source data after the opening the cursor, the changes will not be reflected in the cursor.

The static cursor just fetches a copy of the data source and works on the cached/copy of the data source. If the static cursor is edited, the change will not be reflected into the original data source unless the static cursor is closed. The change will be visible when the static cursor is closed post edit work.

Since the static cursor caches the original data throughout its life, more memory is required or consumed compared to other types of cursors.

Dynamic Cursor

The dynamic cursor shows the real time data of the record set when it is opened. It means that If we make change to the source data after the opening the cursor, the changes will be reflected in the cursor. The dynamic cursor shows the on-the-fly record. Make the change in the source table, and the dynamic cursor will fetch the updated data into it.

Keyset Cursor

A keyset is a set of keys, which are based on the unique IDs of the rows of the result set. It means that the rows retrieved after select query will decide the keyset. If there is no ID available to uniquely identity each row of the result set, the keyset cursor will work as static cursor. So, keyset cursor is analogous to static cursor in this situation.

When we open keyset-driven cursor then it creates a list of unique values in the TEMPDB database. These values are called keyset. Every keyset uniquely identifies each row in the result set.

Keyset is created after the opening the cursor and it remains fixed until we close the cursor. When the cursor is opened, the order of the rows and the membership is fixed. And the set of keys that identifies the rows will be fetched into a table under TEMPDB database. The KEYSET Cursor in SQL Server can only move from the first row to last, and last to first. By default, keyset cursor is scrollable.