Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

What is cursor in SQL Server?

Author: Richard Wand

I will give a good explanation of SQL Server cursors and syntax of cursor in this article.

 

 
Cursors are not used widely but they exist in SQL Server and you might need to use Cursors somewhere so read this article and learn about cursor and its syntax in SQL Server. Normally database statements are processed on a complete set of rows in a database table but some applications cannot work efficiently with complete set of rows so in that situation they need to use cursers to work with individual rows of set of rows.   
 
SQL Server is good when you need to access sets of data but Cursors are used when you need to access one row at a time. Cursors are lot slower than SQL Server set based alternatives like stored procedure. Some people like to use cursors regularly but some are against of using cursors. Both of these have their own arguments and reasons to use or not to use cursors but there may be some situations and circumstances where cursors are required to use.
 
Cursers are good to process individual rows returned from database queries and they enable rows to be processed sequentially in result sets. Typical SQL commands operate on all the rows of a set at one time but cursors are used to manipulate data on row by row bases in set of data. Cursor is something like pointer which points to one row in a set of rows and it can only reference one row at a time. Cursors can be used to perform multiple operations on row by row basis. You can retrieve a set of rows or a single row from the current position in a result set. You can also modify rows at the current position and you can use cursors in stored procedures and triggers as well. In SQL stored procedures, cursor defines a set of data rows and performs complex logic on row by row basis.
 
Basic T-SQL syntax for SQL Server cursor is like below:
 
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]]]
 
Cursor_name:
 
It is the T-SQL server side name of the cursor. You can define any name but cursor name can contain characters from 1 to 128.
 
LOCAL:
 
This keyword specifies the scope of curser as local to the current stored procedure, trigger or batch in which cursor is defined. When batch stored procedure of trigger terminated, local scope will be de-allocated implicitly.
 
GLOBAL:
 
The GLOBAL keyword specifies that the scope of the cursor is global to the connection and it is available in any stored procedure, trigger or batch in that connection. Cursor will be de-allocated implicitly when connection will be disconnected.
 
FORWARD_ONLY:
 
It specified that the cursor can fetch data in forward direction only that means form first row to last row only. FETCH NEXT is the only option available to fetch.
 
STATIC:
 
Cursor that is specified with STATIC keyword will make a temporary copy of the data to use. This temporary copy is made in tempdb and all requests are answered from this copy.
 
KEYSET:
 
This keyword specifies that the order of rows and membership will be fixed in the cursor for an open cursor.
 
DYNAMIC:
 
It specifies that all changes made to base table will be reflected by cursor. Membership and order of the rows can change on each fetch.
 
FAST_FORWARD:
 
This keyword specifies a performance optimization enabled FORWARD_ONLY and READ_ONLY cursor. The relationship between FAST_FORWARD and FORWARD_ONLY was mutually exclusive before SQL Server 2005. If one is specified for a cursor, other can be specified for the same cursor. But now from SQL Server 2005, both can be specified for the same cursor.
 
READ_ONLY:
 
READ_ONLY specifies that the updates can be made through this cursor and the cursor can be referenced in an UPDATE or DELETE statement. 
 
SCROLL_LOCKS:
 
It insures that the rows are available for later modification by locking them and it guarantees that the updates or deletes are succeeded.
 
Cursor Example
 
DECLARE @firstName nvarchar(10), @lastName nvarchar(20)
 
DECLARE cursor1 CURSOR READ_ONLY
 
FOR
 
SELECT FirstName, LastName FROM Employees
 
OPEN cursor1
 
FETCH NEXT FROM cursor1
INTO @firstName, @lastName
 
WHILE @@FETCH_STATUS = 0
BEGIN
      PRINT @firstName + ' ' + @lastName
      FETCH NEXT FROM cursor1
      INTO @firstName, @lastName
END
 
CLOSE cursor1
DEALLOCATE cursor1
 
Above cursor syntax is written to select and print first name and last name form Employees table of NORTHWND sample database. First I am declaring variables at the top and then cursor declaration statement. You need to write DECLARE keyword then cursor name to declare a cursor. You can also specify keyword like READ_ONLY, FORWARD_ONLY, LOCAL and STATIC.   SELECT statement selects mentioned fields of table with an OPEN cursor statement. FETCH statement returns a row from result set. WHILE loop and @@FETCH_STATUS variable will check if there are more rows. CLOSE statement releases the current result set and will free any locks from the rows. CLOSE statement must be for a OPEN cursor statement. DEALOCATE statement removes a cursor reference.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< What is User Defined Function and how to create User Defined Function in SQL Server?

Disclaimer - Privacy
© 2002-2017 DevASP.net