Thursday, July 28, 2011

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.
Cursor retrieves the records from database row by row, and it is mainly use for to reduce the network traffic.

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:

  • Declare cursor
  • Open cursor
  • Fetch row from the cursor
  • Process fetched row
  • Close cursor
  • Deallocate cursor

A cursor is a database object that helps in accessing and manipulating data in a given result set.The main advantage of cursors is that you can process data row-by-row.

A result set is defined as a collection of rows obtained from a SELECT statement that meet the criteria specified in te WHERE clause.

Cursors,therefore,serve as a mechanism for applications to operate on a single row or a set of rows.Cursors enable the processing of rows in the given result set in the following ways:

1) Allow specific rows to be retrieved from the result set.
2) Allow the current row in the result set to be modified.
3) Help navigate from the current row in the result set to a different row.
4) Allow data modified by other users to be visible in the result set.

Structure of Cursors:

The following tasks need to be performed while using a cursor in SQL Server:

1) The cursor needs to be defined and its attributes need to be set.
2) The cursor needs to be opened.
3) The required rows need to be fetched from the cursor. Fetch refers to the process of retrieving a row from the result set.
4) The data in the current row of the cursor can be modified, if required.
5) The cursor needs to be closed.
6) The cursor should be deallocated. This is a good practice as recourses used by the cursor are released.

Types of Cursor:-
Static cursor always displays the result set as it was when the cursor was opened. Static cursors are always read-only.
This is the one to use for generating reports or finding data. Additions, changes, or deletions
by other users are not visible

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor.
Additions and deletions by others are visible. All movement is supported. But some providers
don't support this cursor type.

Forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor.
This is the default. It is identical to the Static except that you can only scroll forward. The
Fastest cursor this side of the Pecos Mountains.

Keyset-driven Cursors - The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened.
This is similar to a Dynamic cursor except you can't see records others add. If another user deletes a record, it is inaccessible from your recordset.


If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.
Here are some alternatives to using a cursor:
· Use WHILE LOOPS
· Use temp tables
· Use derived tables
· Use correlated sub-queries
· Use the CASE statement
· Perform multiple queries
  • Once cursor is opened we have to close the cursor
  • After the usage cursor should be deallocated from the memory.
  • As a DBA , I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a  network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.


@@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.


A Very  Simple Example of Cursor:-

I have a table “Emp” with following records:-




Following is the cursor which print each record:-
CREATE PROCEDURE Usp_cursor_test
AS
  BEGIN
      DECLARE @Name VARCHAR(50)
      DECLARE @Address VARCHAR(50)
      DECLARE @Gender VARCHAR(50)
      DECLARE cur_print CURSOR FOR
        SELECT name, address,gender FROM   emp
        OPEN cur_print
        FETCH NEXT FROM cur_print INTO @Name, @Address, @Gender
        WHILE @@FETCH_STATUS = 0
         BEGIN
 PRINT 'Name is:- ' + @Name + ' | Address is:- ' + @Address             + ' | Gender is:- ' + @Gender
           
PRINT '______________________________________________'
           FETCH NEXT FROM cur_print INTO @Name, @Address, @Gender
        END
      CLOSE cur_print
      DEALLOCATE cur_print
  END





Output is as follow for cursor:-



Cursor advantages:
  • Using cursor to getting multiple values.
  • One cursor have multiple select statements
  • Cursor parameters
i.e cursor cursor_name (parameter datatype) is select * from table_name where(condition)

  • Where Current of Clause: this is use full when the primary key is not present.
  • Client-side memory: For large results, holding the entire result set on the client can lead to demanding memory requirements on client side system.
  • Response time : Cursors can provide the first few rows before the whole result set is assembled. If you do not use cursors, the entire result set must be delivered before any rows are displayed by your application.
  • Concurrency control :It's a general problem with current applications, If you make updates to your data and do not using cursors in your application, you must send separate SQL statements to the database server to apply the changes. This raises the possibility of concurrency problems if the result set has changed since it was queried by the client. In turn, this raises the possibility of lost updates.But Cursors act as pointers to the underlying data, and so impose proper concurrency constraints on any changes you make.


Cursor disadvantages:

  • It is returned by only one row at a time.
  • It gives less performance
  • Each time we fetch a row from the cursor where as a normal cursor select statement query makes only one trip.
  • We know cursor doing round trip it will make network line busy and also make time consuming methods. First of all select queries generate output and after that cursor goes one by one so round trip happen.
  • Another disadvantage of cursor is there are too costly because they require lot of resources and temporary storage so network is quite busy.

0 comments: