SQL Server Cursor Library

  • Article

   

There are two types of SQL server cursors. One type, discussed here, is based on the ANSI syntax for cursors and is intended for triggers and stored procedures. The second type of SQL Server cursor is provided by the DB-Library, the SQL Server ODBC driver, and the SQL Server OLE DB provider.

For More Information   For more information on the types of cursors available in the DB-Library, see DB-Library Cursors in this chapter.

SQL Server can provide a forward-scrolling, read-only cursor. This is sometimes called the "firehose" cursor because it is very efficient at retrieving data and does so by keeping the connection open.

A SQL Server cursor is defined by specifying one or more cursor characteristics in the DECLARE statement. The cursor characteristics available in the SQL cursor library are listed in the following table.

Cursor type Constant
Static cursor. Defines a cursor that makes a copy of the data. Changes to base tables are not detected. INSENSITIVE
Forward and backward scrolling. Changes made to the base tables are available in subsequent fetches. SCROLL
Read only. Prevents updates from occurring on any row in this result set. FOR READ-ONLY
Updatable. Defines updatable columns. All columns are updatable unless the cursor has also been defined as READ-ONLY. FOR UPDATE

SQL Server does not use a server cursor for any of the following situations:

  • Read-only cursor   If your application just reads and does not update, all the data is sent to the client. No cursor is needed.
  • Forward-only cursor   If your application uses forward direction only, all the data is sent to the client. No cursor is needed.
  • Simple fetches   If your application requires the entire result set, all the data is sent to the client. No cursor is needed.

To handle concurrency issues, SQL Server automatically chooses a suitable lock type depending on what type of Transact-SQL operation your application is performing. SQL Server can place locks at the page, table, extent, and database level. You can additionally assign the HOLDLOCK attribute in the SELECT statement to force SQL Server to hold its locks until the transaction is complete.

The types of possible locks are:

  • Shared   Any SELECT statement that reads and does not write places a shared lock on the object. With a shared lock, other applications can still read and even place their own (perhaps stronger) locks on the same object.
  • Exclusive   An exclusive lock prevents any other process from modifying the object. This is useful when your application cannot allow concurrent changes.
  • Update   The update lock occurs when table data is changed. The update lock applies to the associated page.
  • Intent shared   This is an internal SQL Server housekeeping lock advising other processes that a shared lock on a page is about to occur.
  • Intent exclusive   This is an internal SQL Server housekeeping lock advising other processes that an exclusive lock on a page is about to occur.
  • Blocking   While this is a type of locking situation, it is not actually an intentional type of locking. Rather, a blocking lock occurs if two processes are in contention for the same object and one of them has locked it. A more severe variation on the blocking lock is when one process has locked some or all of the first object prior to requesting the second object. Meanwhile, a second process has locked the second object prior to requesting the first object. This is known as a deadlock. Deadlocks occur primarily due to bad table and procedure design.
  • Insert_page lock   As a process is inserting table rows, it can use the insert_page lock as a busy signal. Each page can have many insert_page locks.
  • Link_page lock   This is an internal SQL Server housekeeping lock that merely extends the current page lock to new pages.

For More Information   For more information on the SQL Server cursor library, search online for "Server Cursors" in MSDN Library Visual Studio 6.0 and "DECLARE Statement" in SQL Server Books Online. For more information on using locks to handle multiuser concurrency situations, see Managing Concurrency with Cursor Locks.