Operations in a relational database act on a complete set of rows. 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, cannot 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:
Microsoft® SQL Server™ supports two methods for requesting a cursor:
The Transact-SQL language supports a syntax for using cursors modeled after the SQL-92 cursor syntax.
SQL Server supports the cursor functionality of these database APIs:
An application should never mix these two methods of requesting a cursor. An application that has used the API to specify cursor behaviors should not then execute a Transact-SQL DECLARE CURSOR statement to also request a Transact-SQL cursor. An application should only execute DECLARE CURSOR if it has set all the API cursor attributes back to their defaults.
If neither a Transact-SQL nor API cursor has been requested, SQL Server defaults to returning a complete result set, known as a default result set, to the application.
Transact-SQL cursors and API cursors have different syntax, but the following general process is used with all SQL Server cursors: