Thursday, January 28, 2010

Cursors in SQL Server


-It is a special programming construct that allows data to be manipulated on a row-by-row basis.
-They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.
-A cursor is used to access the result set stored in the memory on execution of a query.

Steps to use Transact-SQL Cursor.

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.

Example of a cursor

Declare @EmpId int
Open curEmp
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Print @EmpId
Fetch next from curEmp into @EmpId
Close curEmp
Deallocate curEmp

Different types of cursors

1. DYNAMIC: It reflects changes happened on the table while scrolling through the row.

2. STATIC: It works on snapshot of record set and disconnects from the server. This kind
doesn’t reflects changes happened on the table while scrolling through the row.

3.KEYSET: In this kind, new record is not reflected, but data modification can be seen.
Define the cursor lock types.

SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.

Disadvantages/limitation of the cursor
- consume network resources.
- Close cursor when it is not required.
- You shouldn’t forget to deallocate cursor after closing it.
- Cursor requires a network roundtrip each time it fetches a record.

Avoid cursors
- Using the SQL while loop
- User defined functions
- Using CTE(Common Table Expression) Using WITH Clause.
-Temporary table
- Table variable
- You should fetch least number of records.
- You should use FORWARD ONLY option when there is no need to update rows.

1 comment:

Kiran said...

using cursor is a good idea , knowing that ur application is in a fast network, but potential programming chanllenge is that application has handle all cursor faill,cursor lost condition or at least a meta feauter to handle it..

good one