Cursor in SQL Server

What is cursor in sql server?

A cursor is a database object used to retrieve data from a result set, one row at a time. It can also be used when the data needs to be updated row by row
In order to work with a cursor, we need to perform some steps, as follows:
Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory

There are different types of cursors in sql server as listed below.
1. Forward-Only
2. Static
3. Keyset
4. Dynamic

Why and When to use Cursor?

There are some conditions when we want to get record from one table and need to insert into another with performing some logic or some conditions .For example if we want to get value from one table row by row  and need to perform some logic over that and update /insert into another table then we can use cursors. Cursor basically works as for/While loop.

Advantages of using Cursor: 
Using Cursor we can perform row by row processing so we can perform row wise validation or operations on each row.
Cursors can provide the first few rows before the whole result set is assembled. Without using cursors, the entire result set must be delivered before any rows are displayed by the application. So using cursor, better response time is achieved. 
If we make updates to our without using cursors in your application then we must send separate SQL statements to the database server to apply the changes. This can cause 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. So using cursor, better concurrency Control can be achieved.
Cursors can be faster than a while loop but at the cost of more overhead.

Disadvantages of using Cursor: 

Cursor in SQL is temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So occupies more resources and temporary storage. 
Each time when a row is fetched from the cursor it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE etc that makes only one round trip. 
Repeated network round trips can degrade the speed of the operation using the cursor.


Syntax of cursor:
1.   DECLARE EX_CURSOR_NAME CURSOR  
2.   FOR  
3.   select Statement  
4.   OPEN EX_CURSOR_NAME  
5.   FETCH NEXT FROM EX_CURSOR_NAME INTO Variables  
6.   WHILE @@FETCH_STATUS = 0  
7.   BEGIN  
8.   FETCH NEXT FROM EX_CURSOR_NAME INTO Variables  
9.   END  
10.  CLOSE EX_CURSOR_NAME  
11.  DEALLOCATE EX_CURSOR_NAME  


Example of cursor:
Below fig shows the data of Employee_Details table in sql server.

Cursor in SQL Server


Here we create a cursor in sql server and print id and name of employee from employee_details table with the help of cursor.



Cursor in SQL Server

Post a Comment

0 Comments