What is cursor in Sql Server and its type?

In this article, I will explain what is meant by cursor and types of cursor with example codes and then components which are used in cursor with syntax and Example with Explanation.How the cursor access the data inside the sqlserver.

What is cursor in SQL Server?

Contents
Definition
Types of cursor
Components of Cursor
Syntax for Cursor
Example for Cursor

Definition:

A Cursor is a set row together with a pointer to identify current row.Cursor is a
database object and manipulate data in row-by-row basics.

Types of Cursor:

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

Base table:

Base table cursors are the lowest level of cursor .Base table cursors can scroll
forward or backward with minimal cost, and can be updated.Directly open the Cursor in Index.

Example:By using ADO.Net

cmd.CommandText = "tablename"; 
cmd.CommandType = CommandType.TableDirect; 
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

Static:

Static cursor is a scrollable query cursor .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.

Example:By using ADO.Net

cmd.CommandText = "Select * from tablename"; 
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);

Forward-only:

The forward-only cursor is the fastest cursor that you can update, but it does not support scrolling.
It supports only fetching the rows.Forward-only and forward-only/read-only cursors are the fastest query-based cursors.

Example:By using ADO.Net

cmd.CommandText = "Select * from tablename"; 
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);

Forward-only/Read-only:

Forward-only/Read-only is same as Forward-only . Its faster then Forward-only but can't be updated.

Example:By using ADO.Net

cmd.CommandText = "Select * from tablename"; 
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);

Keyset-driven:

The keyset-driven cursor is a scrollable cursor and then you can update.
A keyset-driven cursor is controlled by a set of physical identifiers .A keyset-driven cursor is not as sensitive as other cursors.

Example:By using ADO.Net

cmd.CommandText = "Select * from tablename"; 
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

Components for Cursor in Sql Server:

1)DECLARE statements :
Declare variables used in the code block .

DECLARE @AuthorID char(11)
DECLARE c1 CURSOR

2)SET\SELECT statements:
Initialize the variables to a specific value .

SELECT au_id
FROM authors

3)OPEN statement :
Open the cursor to begin data processing .
OPEN c1

4)FETCH NEXT statements:
Assign the specific values from the cursor to the variables .

FETCH NEXT FROM c1
INTO @AuthorID

5)WHILE statement :
Condition to begin and continue data processing .
WHILE @@FETCH_STATUS = 0

6)Begin and End statemets:
Start and end of the code block .

BEGIN
PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorID
END

7)Data processing :
In this example Print @AuthorID

PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorID

8)CLOSE statement:
Releases the current data , but permits the cursor to be re-opened .
CLOSE c1

9)DEALLOCATE statement :
Destroys the cursor.
DEALLOCATE c1

Cursor Syntax


DECLARE @AuthorID char(11)
DECLARE c1 CURSOR 
FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @AuthorID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorID
END
CLOSE c1
DEALLOCATE c1

Example for Cursor:


DECLARE @Loan_details varchar(20)
DECLARE @getLoan_details CURSOR
SET @getLoan_details = CURSOR 
FOR
SELECT Lnno
FROM test4
OPEN @getLoan_details
FETCH NEXT
FROM @getLoan_details INTO @Loan_details
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Loan_details
FETCH NEXT
FROM @getLoan_details INTO @Loan_details
END
CLOSE @getLoan_details
DEALLOCATE @getLoan_details

In this example cursor fetch the loanno from loan_details by row -by-row

Note:
Cursors are the SLOWEST way to access data inside SQL Server.
And Cursor is thirty times slower than set based alternatives.

Comments

Guest Author: honey rana27 May 2012

thanks ,,its great..

Author: Vinod Kumar Sahu12 Feb 2013 Member Level: Silver Points : 8

What is a cursor?
Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.

Let us look this example.
In this example we have 2 columns from a table and passing through the cursor and printing them.

CREATE PROCEDURE Usp_cursor_test
AS
BEGIN
–Declaring the variables needed for cursor to store data
DECLARE @Name VARCHAR(50)
DECLARE @EmptypeID INT
–Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table
DECLARE cur_print CURSOR FOR
SELECT name,
emptypeid
FROM employee.employeedetails
–After declaring we have to open the cursor
OPEN cur_print
–retreives the First row from cursor and storing it into the variables.
FETCH NEXT FROM cur_print INTO @Name, @EmptypeID

– @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against
– any cursor currently opened by the connection.
– @@FETCH_STATUS = 0 means The FETCH statement was successful.
– @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set.
– @@FETCH_STATUS = -2 The row fetched is missing.
WHILE @@FETCH_STATUS = 0
BEGIN
–Operations need to be done,Here just printing the variables
PRINT @Name
PRINT @EmptypeID
–retreives the NExt row from cursor and storing it into the variables.
FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
END
–Closing the cursor
CLOSE cur_print
– removes the cursor reference and relase cursor from memory
– very Important
DEALLOCATE cur_print
END

Note:
Once cursor is opened we have to close the cursor
After the usage cursor should be deallocated from the memory.

Author: srirama03 Apr 2013 Member Level: Gold Points : 4

Hi saranya,

I read your article.As you mentioned about cursors.But at the end of the article 'Cursors are the SLOWEST way to access data inside SQL-Server.And Cursor is thirty times slower than set based alternatives'.is wrong interpretation/Analysis.Cursors are slow when you are using other than 'Fast-forward' option.If you have a scenario to fetch row by row data and move fast-forward direction.Cursors are more efficient than while loop.Because the cursors are pointing out to next record.And it will not allocate any memory to move backward or scrollable.

Author: srirama03 Apr 2013 Member Level: Gold Points : 4

Author: maruthanayakam21 Feb 2014 Member Level: Silver Points : 5

Cursors (SQL Server Compact)

Application developers usually use the relation (set)-based nature of SQL to manage data. However, sometimes application developers need the extra control of row-by-row operations. Cursors fulfill that need. In Microsoft SQL Server Compact 4.0, there are a number of different types of cursors that you can use to control row-by-row operations.
Most cursors can be used in conjunction with the query processor. There are a number of different cursor behaviors, such as scrollability, updatability, and sensitivity that also affect the choice of cursor. For more information, see "Cursor Behaviors" in
SQL Server Books Online.

Advantages of Using Cursors with the Query Processor

Some advantages of using cursors in conjunction with the query processor are:

Efficiency. The query optimizer automatically selects the appropriate query plan, so the developer does not need to design a complex algorithm to access the required data.
Adaptability. As data changes or indexes are added or dropped, the query optimizer automatically adapts its behavior by using alternative plans.
Fewer errors. Instead of the developer handling data and algorithms in the application, the SQL Server Compact 4.0 Database Engine natively supports the required operations.

Requesting a Cursor

In SQL Server Compact 4.0, it is possible to request a cursor only by using API functions.

Types of Cursors

Microsoft SQL Server Compact 4.0 cursors are similar to cursors used in SQL Server. The differences are documented in this section. For a complete explanation about database cursors, see SQL Server Books Online.
SQL Server Compact 4.0 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 4.0, 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 4.0, 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 4.0 cursors are updatable. SQL Server Compact 4.0 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 4.0 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 4.0 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 4.0, 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 4.0 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.

Regards,
Marudhu...

Submit Article

Return to Article Index

What is cursor in Sql Server and its type?