Cursor

Cursor

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.

SQL-92 Syntax:-

This is SQL-92 Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

where

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

INSENSITIVE - specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.

SCROLL - specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported.

select_statement - the standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.

READ ONLY - specifies that cursor cannot be updated.

UPDATE [OF column_name [,...n]] - specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Transact-SQL Extended Syntax

This is Transact-SQL Extended Syntax:

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]]]

where

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

LOCAL - specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates.

GLOBAL - specifies that cursor is global to the connection. The GLOBAL cursor will be implicitly deallocated at disconnect.

FORWARD_ONLY - specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported.

STATIC - specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.

KEYSET - specifies that cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates nonkey values from being made through this cursor, but inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2.

DYNAMIC - specifies that cursor reflects all data changes made to the base tables as you scroll around the cursor. FETCH ABSOLUTE option is not supported with DYNAMIC cursor.

FAST_FORWARD - specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.

READ ONLY - specifies that cursor cannot be updated.

SCROLL_LOCKS - specifies that cursor will lock the rows as they are read into the cursor to ensure that positioned updates or deletes made through the cursor will be succeed.

OPTIMISTIC - specifies that cursor does not lock rows as they are read into the cursor. So, the positioned updates or deletes made through the cursor will not succeed if the row has been updated outside the cursor since this row was read into the cursor.

TYPE_WARNING - specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.

select_statement - the standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.

UPDATE [OF column_name [,...n]] - specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Opening a Cursor

Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

where

GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.

cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - The name of a cursor variable that references a cursor.

After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.

Fetching a Cursor

Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax:

FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar} ] FROM ] { { [GLOBAL] cursor_name } | @cursor_variable_name} [INTO @variable_name[,...n] ]

where

NEXT - The default cursor fetch option. FETCH NEXT returns the next row after the current row.

PRIOR - Returns the prior row before the current row.

FIRST - Returns the first row in the cursor.

LAST - Returns the last row in the cursor.

ABSOLUTE {n \| @nvar} - Returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set.

RELATIVE {n \| @nvar} - Returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row.

GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.

cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - The name of a cursor variable that references a cursor.

INTO @variable_name[,...n] - Allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.

Closing a Cursor

When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.

To close a cursor, you can use the following syntax:

CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

where

GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.

cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - The name of a cursor variable that references a cursor.

Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.

Deallocating a Cursor

When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.

To deallocate a cursor, you can use the following syntax:

DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}

where

GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.

cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - The name of a cursor variable that references a cursor.

Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.

Cursor Optimization Tips

• Try to avoid using SQL Server cursors whenever possible.

Using SQL Server cursors can result 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.

• Do not forget to close SQL Server cursor when its result set is not needed.

To close SQL Server cursor you can use the 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.

• Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.

To deallocate SQL Server cursor, you can use the DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

• Try to reduce the number of records to process in the cursor.

To reduce the cursor result set, use the WHERE clause in the cursor's select statement. It can increase cursor performance and reduce SQL Server overhead.

• Try to 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. This can increase cursor performance and reduce SQL Server overhead.

• 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.

• Try avoid using insensitive, static and keyset cursors, whenever possible.

These types of cursor produce the largest amount of overhead on SQL Server as 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 as they 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.

• 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. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified, the other cannot be specified.

SQL Server Compact Edition supports the following types of cursors:

• Base table
• Static
• Forward-only
• Forward-only/Read-only
• Keyset-driven

Base Table Cursors

Base table cursors are the lowest level of cursor available. These cursors work directly against the storage engine and are the fastest of all supported cursor types. Base table cursors can scroll forward or backward with minimal cost, and can be updated.

You can also open a cursor directly on an index. Indexes are supported to order the rows in a table, to enable seeking on particular values, and to restrict the rows based on a range of values within an index.

Base table cursors have dynamic membership. This means that two cursors opened over the same table can immediately see insertions, deletions, and changes to the data, assuming both are in the same transaction scope. Because you can update base table cursors, a client can use this kind of cursor to make changes to the underlying data.

Base table cursors cannot represent the result of a query. Results of queries, such as SELECT * FROM tablename, are not returned through a base table cursor. Instead, one of the supported query result cursors is used.

The following is an example of how to obtain a base table cursor by using ADO .NET:

//Base Table Cursor

cmd.CommandText = "tablename";

cmd.CommandType = CommandType.TableDirect;

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

The following is an example of how to obtain an index cursor by using ADO .NET:

cmd.CommandText = "tablename";

cmd.IndexName = "indexname";

cmd.CommandType = CommandType.TableDirect;

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

Static Cursors

A static cursor, referred to as a scrollable query cursor in earlier versions of SQL Server Compact Edition, creates and stores a complete copy of the result set. The exception to this is long-value data that is retrieved when a user explicitly requests it. This result set is filled only as needed. This is different from SQL Server, which populates the result set at cursor creation. Static cursors support scrolling backward and forward, but they do not support updates. Static cursors do not see external changes to the data that is insensitive. Query results are cached for the lifetime of the cursor. Although static cursors are more functional than forward-only cursors, static cursors are slower and use more memory. We recommend that you consider static cursors only if scrolling is required and a keyset cursor is not appropriate.

The following is an example of how to obtain a static cursor by using ADO.NET:

cmd.CommandText = "Select * from tablename";

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);

Forward-only Cursors

The forward-only cursor is the fastest cursor that you can update, but it does not support scrolling. It supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are 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 cannot be scrolled backward, changes made to rows in the database after the row was fetched are not visible by using the cursor.

Forward-only and forward-only/read-only cursors are the fastest query-based cursors. They should be used in scenarios in which speed and memory footprint are most important.

The following is an example of how to obtain a forward-only cursor by using ADO .NET:

cmd.CommandText = "Select * from tablename";

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);

Forward-only/Read-only Cursors

Forward-only/read-only cursors, referred to as forward-only cursors in earlier versions of SQL Server Compact Edition, are the fastest cursors, but cannot be updated.

The following is an example of how to obtain a forward-only/read-only cursor by using ADO.NET:

cmd.CommandText = "Select * from tablename";

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);

Note You cannot create read-only cursors on query that returns only read only columns because internally all SQL Server Compact Edition cursors are updatable. SQL Server Compact Edition cannot update read-only columns returned in the SqlCeResultSet. Therefore, it would fail with the error "Cannot generate an updatable cursor for the query because there is no updatable column."

Keyset-driven Cursors

The keyset-driven cursor in SQL Server Compact Edition is a scrollable cursor that you can update. A keyset-driven cursor is controlled by a set of physical identifiers known as the keyset. The keyset is based on all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset is built in a temporary table when the cursor is opened. With a keyset-driven cursor, membership is determined at the time that the query is executed.

Keyset-driven cursors in SQL Server Compact Edition differ slightly from those in SQL Server. In SQL Server, the keyset-driven cursor uses a set of unique identifiers as the keys in the keyset. In SQL Server Compact Edition, the keys are bookmarks for where values are logically stored in a table. They are not unique identifiers.

Although sensitive to a number of changes, a keyset-driven cursor is not as sensitive as other cursors. For example, an insert outside the cursor will not be seen, although inserts inside the cursor will be seen at the end. In this case, we recommend that you close and reopen the cursor, or use one of the forward-only cursors.

Because SQL Server Compact Edition uses bookmarks to define a keyset, all changes to data values for rows that are included in the keyset are visible by using the cursor. This is the case for both changes that are made within the cursors and changes that are made outside the cursor.

Any deletes in a keyset cursor, whether within or outside the cursor, will cause the row to be reported as deleted if an attempt is made to fetch it.

The following is an example of how to obtain a keyset-driven cursor by using ADO .NET:

cmd.CommandText = "Select * from tablename";

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

Working with Joins

If the query you use to open a keyset-driven cursor includes joined columns, these columns are not updateable. The user can insert new values into these columns, but updates are not supported.

If the keyset is used to populate a user-updateable control, like a DataGrid object, users might try to update the values in the control, and the update will fail. If you are developing an application that uses a DataGrid to display joined column data, ensure that you set the joined columns in the DataGrid to Read-only.

Static cursors
A static cursor always displays the result set as it was when the cursor was opened. Static cursors are always read-only.
Dynamic Cursors
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.
Forward-only Cursors
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor
Keyset-driven Cursors
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.