Cursor-SQL-Server-technothirsty

What is cursor in SQL Server?

What is cursor in SQL Server?

A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like record set in the ASP and visual basic.

 Syntax:

DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL] --define cursor scope
 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

Example 1:

CREATE TABLE Student
(
 StuID int PRIMARY KEY,
 StuName varchar (50) NOT NULL,

)
 
INSERT INTO Student(StuID,StuName) VALUES(1,'Jack')
INSERT INTO Student(StuID,StuName) VALUES(2,'Peter')
INSERT INTO Student(StuID,StuName) VALUES(3,'Harry')
INSERT INTO Student(StuID,StuName) VALUES(4,'Frank')
INSERT INTO Student(StuID,StuName) VALUES(5,'Brian')

SELECT * FROM Student

OutPut:
Cursor-1

Example 2:

SELECT * FROM Student 

SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE cur_Stu CURSOR
STATIC FOR 
	SELECT StuID,StuName  from Student
OPEN cur_Stu

IF @@CURSOR_ROWS > 0
 BEGIN 
	 FETCH NEXT FROM cur_Stu INTO @Id,@name
	 WHILE @@Fetch_status = 0
	 BEGIN
		 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name
		 FETCH NEXT FROM cur_Stu INTO @Id,@name
	 END
END

CLOSE cur_Stu
DEALLOCATE cur_Stu
SET NOCOUNT OFF 

DROP TABLE Student

Output:
Cursor-2Live demo  of cursor in SQL Server

Disadvantages of cursors

  • Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
  • There are restrictions on the SELECT statements that can be used.
  • Because of the round trips, performance and speed is slow

How can you avoid cursors?

Answer
The main purpose that cursors fulfill is traversing of the row set. Cursors can be best avoided by:

a. Using the SQL while loop: Using a while loop we can insert the result set into the temporary table.

b. User defined functions : Cursors are sometimes used to perform some calculation on the resultant row set. This cam also be achieved by creating a user defined function to suit the needs

Cursor implementation in application, helps data manipulation easy and even they are very effective but due to some major disadvantage of Cursor normally they are not preferred.

  • As 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 are there are too costly because they require lot of resources and temporary storage so network is quite busy.
  • Apart from these I would like to point out some great advantages of cursor if the entire result set must be transferred to the client for processing and display.
  • 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.
It is true that in some case we can avoid cursors by using PL SQL logics ,I mean by implementing While loop,Tricky queries and through joins but they require great development logic.

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

I would request you to also look into Performance improvement of cursor SQL Server with example.

Leave a Reply