Wednesday 29 May 2013

Cursor in sql server

Cursor in sql.

     Hi,
          In this article we will discuss about a cursor in sql server,what is cursor ,use and disadvantages
of a cursor,types of cursor etc.

Click here For example of Cursor in sql server
What is Cursor object in sql server ?

                  A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This  temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

 Difference between Cursor and sql statement ? or when we use a cursor?

Generally our sql statement or sql query process a records in bench and a Cursor is worked with data or records on row by row basis. so if we need to do something on on row by row basis then you can  use Corsor.
E.g::     
  Some time we need to Update only one column of the all record in table,that time
we can use cursor.
There are two types of cursors in PL/SQL:

1) Implicit cursors ::

               We can use a Implicit cursor when DML statement like Insert,Update ,Delete are executed, a Implicit Cursor also used with select statement that return just one row is executed.

1) Explicit cursors ::

                        We can use a Explicit cursors with SELECT statement that returns more than one row.
Even though the cursor stores multiple records, only one record can be processed at a time,which is
called as current row. When you fetch a row the current row position moves to next row.

Difference between Implicit cursors and Explicit cursors ?

              Both implicit and explicit cursors have the same functionality, but they differ in the way they
are accessed.A Implicit cursor is hold only single record ,but in case of Explicit cursor it hold a multiple
records but it process a only one record at a time

disadvantages of cursor?
                 A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs. You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.
Subtypes of Cursors

1.Static Cursors
         A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable. You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source.
2.Dynamic Cursors
      A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.
3.Forward Only Cursors
                  A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling.You can  update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source. There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the datato the cursor lifetime. It is not sensitive to any changes to the data source. A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source
.
4.Keyset Driven Cursors::  

   A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset.
The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened.A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations.
By default keyset driven cursors are scrollable.
SUMMERY ::

        In this article I try to explain the what is Cursor , types of Cursor in SQL Server,and what is drowbacks or disadvantage of cursor. I hope after reading this article you will be able to understand different types of cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

1 comment:

  1. Nice......may i get simplest examples of above mentioned cursor's....which will explain with more simply... :) 'hrihaan77777@gmail.com'

    ReplyDelete

if you have any doubt any suggestions do comment