Subscribe to Subscribers
Talk to Webmaster Tony John


Resources » SQL Server

What is cursor in Sql Server and its type?


Posted Date:     Category: SQL Server    
Author: Member Level: Silver    Points: 75

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.





Did you like this resource? Share it with your friends and show your love!


Responses to "What is cursor in Sql Server and its type?"
Guest Author: honey rana     27 May 2012
thanks ,,its great..


Author: Vinod Kumar Sahu    12 Feb 2013Member 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: srirama    03 Apr 2013Member 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: srirama    03 Apr 2013Member 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.
read the below article
http://www.dotnetspider.com/resources/44228-Cursors-its-usage.aspx


Feedbacks      

Post Comment:


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
    •
    Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: Collation in SQL Server
    Previous Resource: Evaluate And Execute IF Statement in Formula Editor
    Return to Resources
    Post New Resource
    Category: SQL Server


    Post resources and earn money!     		 
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    Cursor in Sqlserver  .  Database object in SqlServer  .  Cursor with Example  .  



    Follow us on Twitter: https://twitter.com/dotnetspider

    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2012 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.