SQL Server - why Cursor?

Asked By koteswara rao arvapalli
20-Sep-11 01:08 AM
where and when can we use cursor?
and

why cursor?

when and where these cursors are used?
  Suchit shah replied to koteswara rao arvapalli
20-Sep-11 01:13 AM

In computer science and technology, a http://en.wikipedia.org/wiki/Database cursor is a control structure that enables http://en.wiktionary.org/wiki/traverse over the http://en.wikipedia.org/wiki/Database_record in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal make cursors akin to the programming language concept of http://en.wikipedia.org/wiki/Iterator.

Cursors are used by database programmers to process individual rows returned by http://en.wikipedia.org/wiki/Database_system queries. Cursors enable manipulation of whole http://en.wikipedia.org/wiki/Result_set at once—a capability that most http://en.wikipedia.org/wiki/Procedural_programming languages lack. In this scenario, a cursor enables the rows in a result-set to be processed sequentially.



Disadvantages of cursors

The following information may vary depending on the specific database system.

Fetching a row from the cursor may result in a http://en.wikipedia.org/wiki/Round-trip_delay_time each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.

Cursors allocate resources on the server, for instance http://en.wikipedia.org/wiki/Lock_(database), packages, processes, temporary storage, etc. For example, http://en.wikipedia.org/wiki/Microsoft_SQL_Server implements cursors by creating a temporary table and populating it with the query's result-set. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures



Many users perceive Explicit Cursors as a difficult means of retrieving
data from the database. In reality, only a few basic concepts are needed
to implement this flexible method, thus, maximizing efficiency and
developer control.

A cursor is a named SQL statement. From the PL/SQL User's Guide and
Reference Manual (Page 2-16):

When a query returns multiple rows, you can explicitly define a cursor to:

* process beyond the first row returned by the query
* keep track of which row is currently being processed.

Cursors allow the designer precise control of the multiple values returned
by the cursor's SELECT statement.

Note that explicit cursors should be used in specific cases only. For most
applications, the default database block is sufficient and easier to use
than explicit cursors. Please read the last section of this document for
situations where explicit cursors may be appropriate.

POPULATING MULTI-RECORD BLOCKS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Explicit cursors can be used to query records into a block, similar to
a normal EXECUTE_QUERY within a database block. The basic difference
between these two methods is that each record queried into the block
is tied, by rowid, to a row in the database. An explicit cursor
retrieves the data and places it into a field as if a user had input
the data by hand. A more in depth discussion of the differences is
included in the last section of this bulletin.

Below is a simple example of populating a multi-record block using
explicit cursors. To recreate this you will need to use the standard
database tables that are supplied with SQL*Forms: create a non-base table
block F_EMP, with 3 fields F_EMPNO, F_ENAME and F_DEPTNO that have the same
datatypes and field lengths as the standard EMP fields EMPNO, ENAME and
DEPTNO in the database.

KEY-HELP

declare cursor C1 is select EMPNO,ENAME,DEPTNO
from EMP
where DEPTNO>10;
begin
go_block('F_EMP'); /* This isn't necessary if you are already in
the block where the values are being entered */
open C1;
loop
fetch C1 into :F_EMP.F_EMPNO, :F_EMP.F_ENAME, :F_EMP.F_DEPTNO;
exit when C1%notfound;
next_record;
end loop;
close C1;
first_record;
end;

Note the looping structure. The FETCH, grabs the next (or first) row from
the table satisfying the cursor declaration, INTO the specified fields. If
it is not the last record, then the cursor is moved to the next record and
the next FETCH INTO is performed. After the cursor is closed, the cursor
is put on the first record of the block, the same functionality as a
regular database block query.

Available cursor options are

%NOTFOUND : TRUE if fetch failed because no more rows were available.
%FOUND : Logical opposite of %NOTFOUND.
%ROWCOUNT : Returns the number of rows fetched from the active set so far.
%ISOPEN : TRUE if explicit cursor is open.

Please see the PL/SQL Manual (page 3-35) for further details.


LOOKUP FIELDS
~~~~~~~~~~~~~
In relational database environments, lookup fields are often performed
to display data referenced from other tables. To recreate the example
below, create a non-database lookup field called LOOKUP within a block
based on the EMP table. LOOKUP will contain the department name (DNAME)
from table DEPT based upon the value of the department number (:DEPTNO)
field in the EMP block. The DEPTNO field is the join field between EMP
and DEPT. The user may wish to make the LOOKUP field non-enterable.


/*=========================================================================*/
/* Get_Name_Value */
/* This procedure attempts to query the department name into the LOOKUP */
/* field from DEPT based on the field value in EMP.DEPTNO. If the value */
/* is not found, then RETURN_CODE := 0, ELSE RETURN_CODE := 1; */
/* This procedure could obviously be made more generic by allowing the */
/* the field value to be populated to be passed in as a parameter. */
/* ----------------------------------------------------------------------- */

procedure get_name_value (return_code IN OUT boolean) is
cursor C1 is select DNAME
from DEPT
where DEPTNO=:EMP.DEPTNO;
begin
open C1;
fetch C1 into :EMP.LOOKUP;
if c1%notfound then /* If value not found then FAIL */
return_code := FALSE;
else
return_code := TRUE; /* If value found then SUCCESS */
end if;
close C1;
end;

/*=========================================================================*/


POST-QUERY on the EMP block.
/*========================================================================*/
/* Post-Query */
/* Will lookup the department name DNAME from the table DEPT based on */
/* the DEPTNO field in the EMP block for each record queried in. */
/* The RETURN_CODE is not used in this procedure but is needed to */
/* compile the procedure. */
/* ---------------------------------------------------------------------- */

declare
RETURN_CODE boolean;
begin
GET_NAME_VALUE(RETURN_CODE);
end;

/*========================================================================*/

ON-VALIDATE-FIELD on field EMP.DEPTNO

/*========================================================================*/
/* On-Validate-Field */
/* Will attempt to populate LOOKUP with department name from DNAME based */
/* on DEPTNO field in the EMP block. The return code is checked if the */
/* procedure GET_VALUE could not query in a related record. */
/* ---------------------------------------------------------------------- */

declare
RETURN_CODE boolean;
begin
GET_NAME_VALUE(RETURN_CODE);
if RETURN_CODE = FALSE then
message('Invalid Department Number, Please Reenter');
raise form_trigger_failure;
elsif RETURN_CODE = TRUE then
message('Department Number is Valid');
end if;
end;
  James H replied to koteswara rao arvapalli
20-Sep-11 01:13 AM
Cursor is a named private SQL area also called as context area from where information can be accessed. Cursors  are  required  to  process rows individually for queries returning multiple rows. there are three types of cursors
1. Static Cursor
   * Implicit Cursor
   * Explicit Cursor
2. Dynamic Cursor
3. Reference Cursor
Use of Cursor:
Cursor is a handler or pointer to Oracle Context Area.Context area is a memory space used for processing of sql statements.
  Suchit shah replied to koteswara rao arvapalli
20-Sep-11 01:14 AM

Most DBA's will tell you that cursors are bad, or worse. That stems from developers in the early days of SQL using cursors to accomplish tasks the same way they did in the programming language of their choice - looping. My standard line is that you should try to solve the problem in a set based way first and reserve cursors for these situations:

  • Multiple database maintenance type tasks, where you need to run through many (or at least more than one) databases
  • You just cannot figure out a set based solution. That may sound simple and/or lame, but we get paid to solve problems. If you (and your DBA) can't figure out a set based solution in 30 minutes, use the cursor. That solves the problem and often you gain a deeper understanding of the problem that may lead you back to a set based solution.
  • You want to leverage logic you've already encapsulated in a stored procedure.

I hope you'll agree with the first point, imagine you'll vilify me for the second point, and maybe scratch your head about the third. The goal today is to get you to rethink your views on cursors and if enough interest, I may follow up with a deeper discussion. We'll focus on the third point as a starting place.

First, some background. I believe in using stored procedures for all data access with the exception of things that intrinsically require dynamic SQL like searches. I also believe that putting some business logic in a stored procedure is worth doing if it reduces round trips to the client machine. The limiting factor to stored procedures is that it's hard to pass in the equivalent of an array so that you can use that encapsulated logic for one record (request) or for many.

One example from my background required parsing a string containing order information (for books, shirts, etc) and splitting it into parent/child tables. Orders were received via an HTTP post, each post containing all the information relevant to the order. The post was inserted as a single string into a table so that it completed quickly, then a job ran frequently to process the new records. It was an ugly bit of parsing and I fell back on point #2 above, using a cursor to handle the parsing because there were some special cases I needed to handle. So, in pseudo code, I had something like this:

create proc usp_ParseOrder @WebOrderID
as
begin trans
some ugly cursor stuff here
commit trans

Possibly I could have solved it set based, but for the sake of discussion let's say that it performed well enough and had the advantage of being easy to understand if maintenance was needed that no further effort was warranted. For the purposes of this discussion it's how I solved the problem inside the stored procedure but rather that it was complicated to express.

So the job that processed new records looked something like this (again, pseudo code):

open cursor
for each record in cursor
    exec usp_ParseOrder @WebOrderID
next
close cursor

That's the flaw of building procedures designed to handle a single request (think of users adding/editing records, etc). They are fast and clean, but if you want to reuse the logic you either call the procedure repeatedly, refactor the procedure to handle 1 to unlimited records, or you duplicate the logic for batch processing.

The flaws are offset by a some nice wins:

  • Duration of each transaction should be short compared to a large batch
  • Growth of the log file has less chance of getting out of hand. Logging 100 small transactions provides the chance for the log to roll over where doing one big transaction may require the log to grow
  • That complex logic is in one place and is pretty easy to work with
  • If something goes wrong you roll back one very small transaction

Those potentials wins should also be considerations when you're coding. It's easy to think batch when you're processing 100 records or even a 1000, but what about 10k? 100k? Ever roll back a million record transaction?

I think looping to call a stored procedure multiple times to leverage logic is a valid and useful technique. Be practical and pragmatic about it's application and you'll do well. I look forward to the discussion!

  dipa ahuja replied to koteswara rao arvapalli
20-Sep-11 01:27 AM
What is cursor?

The cursor is one of the important elements in the sql server. As you know it will do the row by row operation. It will affect the performance of the sql server. 

Because it will use lot of IO to do the operations. The web application performance not only depends on the application. We have to consider the database also. Because when we want to display the records from the database then it must be effectively do the backend processing. 

When to use Cursor?

The cursors will be used when we want to perform the looping of the table result set. In the sql server we don't have any inbuilt functions to do the looping of the Table results like Array in the any programming language. If you want to iterate the table based on the key then we cannot say it will be in the sequence order.
  aneesa replied to koteswara rao arvapalli
20-Sep-11 01:29 AM

The Problem

The scenario involve an Inventory table, which holds information regarding intial beginning inventory per product, along with subsequent inventory transactions. To make things a bit more simple, I’ll assume that first record for a given date will contain the beginning inventory, each record after the initial record will indicate Inventory being moved.

The required report is a listing of the Products by Date and the amount of Inventory remaining at the end of the day. Management wants to know when ineventory is getting low for specific products before the entire supply has been depleted. The code snippet below will create my Inventory table and load some sample data into it.

IF OBJECT_ID('Inventory') IS NOT NULL
DROP TABLE Inventory;
GO
CREATE TABLE [dbo].Inventory
(
InventoryID [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](150) NULL,
[InventoryDate] [datetime] NULL,
[InventoryCount] INT NULL
)
GO
INSERT INTO Inventory
(Product, InventoryDate, InventoryCount)
SELECT 'Computer', DATEADD(d, -5, GETDATE()), 5000
UNION ALL
SELECT 'Computer', DATEADD(d, -4, GETDATE()), 4000
UNION ALL
SELECT 'Computer', DATEADD(d, -3, GETDATE()), 3000
UNION ALL
SELECT 'Computer', DATEADD(d, -2, GETDATE()), 2000
UNION ALL
SELECT 'Computer', DATEADD(d, -1, GETDATE()), 1000
INSERT INTO Inventory
(Product, InventoryDate, InventoryCount)
SELECT 'BigScreen', DATEADD(d, -5, GETDATE()), 5000
UNION ALL
SELECT 'BigScreen', DATEADD(d, -4, GETDATE()), 2000
UNION ALL
SELECT 'BigScreen', DATEADD(d, -3, GETDATE()), 1900
UNION ALL
SELECT 'BigScreen', DATEADD(d, -2, GETDATE()), 1800
UNION ALL
SELECT 'BigScreen', DATEADD(d, -1, GETDATE()), 1000
INSERT INTO Inventory
(Product, InventoryDate, InventoryCount)
SELECT 'PoolTable', DATEADD(d, -5, GETDATE()), 5000
UNION ALL
SELECT 'PoolTable', DATEADD(d, -4, GETDATE()), 4500
UNION ALL
SELECT 'PoolTable', DATEADD(d, -3, GETDATE()), 3900
UNION ALL
SELECT 'PoolTable', DATEADD(d, -2, GETDATE()), 3800
UNION ALL
SELECT 'PoolTable', DATEADD(d, -1, GETDATE()), 2800

The tricky part about this report comes when trying to determine how much inventory is left after each transaction. For the PoolTable product above, there are 5000 units available for sale at the end of the first day. At the end of the second day, 4500 of those units have been sold, leaving only 500 units. At the end of the third day, 3900 units have been sold, which means that more inventory needs to be purchased. Because the next result is totally dependent on the preceeding results, it makes sense to try to use a cursor to loop through each records and store values in variables and temp tables, and just report the result at the end. However, with some crafty TSQL, cursors can be avoided.

In the example below, I use a http://blogs.techrepublic.com.com/datacenter/?p=275 and the DENSE_RANK() windowing function, two new features in SQL Server 2005, to recurse through the results and return the final output as one TSQL statement.

;WITH RecursiveCTE(RowNumber, Product, InventoryCount, InventoryDate, RemainingInventory, Ranking)
AS
(
SELECT *
FROM
(
SELECT
RowNumber = CAST(DENSE_RANK() OVER(PARTITION BY product ORDER BY InventoryDate ASC) AS INT),
Product, InventoryCount, InventoryDate, ValueColumn = InventoryCount, Ranking = 0
FROM Inventory sho
) G
WHERE G.RowNumber = 1
UNION ALL
SELECT
r.RowNumber, r.Product, r.InventoryCount, r.InventoryDate, c.RemainingInventory - r.InventoryCount, c.Ranking + 1
FROM RecursiveCTE c
JOIN
(
SELECT RowNumber = CAST(DENSE_RANK() OVER(PARTITION BY Product ORDER BY InventoryDate ASC) AS INT),*
FROM Inventory
)r ON c.Product = r.Product AND r.RowNumber = c.RowNumber + 1
)
SELECT Product, InventoryDate, InventoryCount, RemainingInventory
FROM RecursiveCTE
ORDER BY Product, InventoryDate

Using a recursive CTE is not the only way to accomplish the desired results. You can also make use of subqueries to return the same thing. In fact, the subquery used below significantly outperforms the recursive CTE example mentioned above.

SELECT First.Product,First.InventoryDate, First.InventoryCount, Outage= 2*MAX(Second.InventoryCount)-SUM(Second.InventoryCount)
FROM
(
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Product,InventoryDate ASC),*
FROM Inventory
) First
INNER JOIN
(
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Product,InventoryDate ASC),*
FROM Inventory
) Second
ON First.Product = Second.Product AND First.RowNumber >= Second.RowNumber
GROUP BY
First.Product,  First.InventoryDate, First.InventoryCount
ORDER BY
First.Product, First.InventoryDate ASC

Why not use a cursor?

The two examples used above are by no means simple TSQL queries. You need a strong foundation of TSQL and of some new SQL Server 2005 features to avoid cursors. But, why should you avoid using a cursor for this problem?  They are a little more simple to write and understand because all of the processing happens to one record at a time. However, the use of cursors require more code to write, typically more memory to accomplish the same task, and cursors are typically slower because they only handle one record at a time. The example code below uses cursors to achieve the same as the two queries above, but at the cost of a lot more code and a lot slower execution time.

IF OBJECT_ID('tempdb..#InventoryTemp') IS NOT NULL
DROP TABLE #InventoryTemp
DECLARE @First BIT, @RemainingInventory INT
DECLARE @Product VARCHAR(20), @InventoryID INT, @InventoryCount INT
SELECT * INTO #InventoryTemp
FROM Inventory
ALTER TABLE #InventoryTemp
ADD RemainingInventory INT
DECLARE  ProductCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT Product FROM Inventory
OPEN ProductCursor
FETCH NEXT FROM ProductCursor
INTO   @Product
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @First = 1
DECLARE  InventoryCursor CURSOR FAST_FORWARD FOR
SELECT InventoryID, InventoryCount
FROM #InventoryTemp
WHERE Product = @Product
ORDER BY InventoryDate ASC
OPEN InventoryCursor
FETCH NEXT FROM InventoryCursor
INTO   @InventoryID, @InventoryCounT
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @RemainingInventory = ISNULL(@RemainingInventory, @InventoryCount)
BEGIN
UPDATE #InventoryTemp
SET RemainingInventory = CASE WHEN @First = 1 THEN InventoryCount ELSE @RemainingInventory - @InventoryCount END
WHERE InventoryID = @InventoryID
SELECT @RemainingInventory = RemainingInventory
FROM #InventoryTemp
WHERE InventoryID = @InventoryID
END
SET @First = 0
FETCH NEXT FROM InventoryCursor
INTO   @InventoryID, @InventoryCount
END
CLOSE InventoryCursor
DEALLOCATE InventoryCursor
FETCH NEXT FROM ProductCursor
INTO @Product
END
CLOSE ProductCursor
DEALLOCATE ProductCursor
SELECT * FROM #InventoryTemp

Conclusion

Cursors aren’t all bad. In fact, they can make some database problems a lot easier to solve. But, the next time you’re saddled with a problem and your first thought is to use a cursor to solve it, take a step back and really examine the problem. There is a chance that you can use a more efficient set-based approach to solve your problem.

  aneesa replied to koteswara rao arvapalli
20-Sep-11 01:30 AM

SQL Server: When We Should Use Read Only Cursors


Everyone of us knows that processing of data through cursors, is a worst choice, because SQL Server are designed to work best with sets of data and not one row at a time.
Still processing through cursors can be made faster by making little changes. Like, if we need to use cursor to process one row at a time and we don’t need to update base table through this cursor, we MUST use read only cursor. As read only cursors are non-updateable so no locks are required on the base table. Only shared locks are held. And due to this phenomenon read only type of cursor are considered FASTER and SAFER.
Syntax is almost same to ordinary cursor and only keyword of READ_ONLY is added.

DECLARE YourCursorNameHere CURSOR READ_ONLY
    FOR SELECT columnNameHere
      FROM  tableNameHere
      WHERE filterConditionHere

OPEN YourCursorNameHere
FETCH NEXT FROM YourCursorNameHere INTO @parameterName
WHILE @@FETCH_STATUS = 0
    BEGIN
        YourImplementationHere
      .
      .
      .
    FETCH NEXT FROM YourCursorNameHERE INTO @parameterName
    END
CLOSE YourCursorNameHere
DEALLOCATE YourCursorNameHere
  Reena Jain replied to koteswara rao arvapalli
20-Sep-11 02:14 AM
hi,

Cursors are a looping construct built inside the database engine and come with a wide variety of features. Cursors allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. Depending on the type of cursor you request, you can even fetch records that you’ve previously fetched.
I would like to give you one eg for this
DECLARE my_cursor CURSOR
FOR SELECT au_lname FROM authors ORDER BY au_lname
  DECLARE @au_lname varchar(40)
  OPEN my_cursor
  FETCH NEXT FROM my_cursor INTO @au_lname
  WHILE @@FETCH_STATUS = 0
  BEGIN
  /* Do something interesting with @au_lname. */
  FETCH NEXT FROM my_cursor
  END
CLOSE my_cursor

The CASE statement can be used as a switch statement in C
/* This is the original MS SQL
   statement, using the * traditional pubs database. */
CREATE PROCEDURE list_states
AS
SELECT
  CASE state
  WHEN 'CA' THEN 'California'
  WHEN 'US' THEN 'United state'
  ELSE 'unknown'
  END
FROM authors

hope this will help you
Create New Account
help
replication sql 2000 - -> sql 2005 SQL Server , sql, 2005" / > Is it possible to replicate a db from sql server 2000 to sql server 2005? When I set the publications, I look this "select
SSIS for SQL Server 2005? SQL Server How do I download SSIS for SQL Server 2005? SQL Server Programming Discussions SQL Server 2005 (1) Distributed (1) Imp (1) Exp (1) E5C6F1F60688
DTS in SQL Server 2005 SQL Server With the SQL server 2005, how can I invoke DTS? Should I install 'SQL Server Business Intelligence Development Studio'? If
Where is my SQL Server 2005 ? SQL Server I installed SQL Server 2005 on my machine, but when I go to the SQL Server Management Studio and connect
Cannot open server 2000 file in SQL SERVER 2005 SQL Server I installed SQL SERVER 2005 replacing SQL SERVER 2000 and now I cannot open the 2000 database file. Do