Cursors In SQL Server

Cursors In SQL Server

14 Mar 2024
Intermediate
403K Views
22 min read
Learn via Video Course & by Doing Hands-on Labs

SQL Server Course

Cursors In SQL Server: An Overview

In this SQL Server Tutorial, the cursor is a database object that retrieves data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use a cursor when we need to update records in a database table in a singleton fashion, which means row by row in this SQL Server Course.

Cursor in SQL Server

In SQL Server, a cursor is a database object that allows us to retrieve and manipulate each row one at a time. A cursor is nothing more than a row pointer. It is always used alongside a SELECT command in SQL. It is typically a set of SQL logic that loops through a set number of rows one by one.

Example of Cursor in SQL Server

CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 

Output of Cursor in SQL Server

Output of Cursor in SQL Server

Life Cycle of Cursor

The life cycle of a cursor can be divided into five phases, as follows:

  1. Declare Cursor
  2. Open Cursor
  3. Fetch Cursor
  4. Close Cursor
  5. Deallocate Cursor

Life Cycle of Cursor

1. Declare Cursor

Declares and associates a cursor variable with a certain SELECT statement.

SQL Statement

DECLARE cursor_name CURSOR FOR
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;

2. Open Cursor

Opens the cursor, making it possible to retrieve rows.

SQL Statement

OPEN cursor_name;

3. Fetch Cursor

Retrieves the next row from the cursor's result set.

SQL Statement

FETCH cursor_name INTO variable1, variable2, ...;

4. Close Cursor

The cursor is closed, freeing the resources connected with it.

SQL Statement

CLOSE cursor_name;

5. Deallocate Cursor

The cursor definition is removed and its resources are released.

SQL Statement

DEALLOCATE cursor_name;

Types of Cursors in SQL Server

The different types of cursors in SQL Server are given below:

  1. Static Cursors
  2. Dynamic Cursors
  3. Forward-Only Cursors
  4. Keyset Cursors

Types of Cursors in SQL Server

1. Static Cursors

SQL Server static cursors retrieve a snapshot of the result set at the moment of cursor formation and keep that snapshot for the duration of the cursor's life. Changes made to the underlying data after the cursor is created do not affect the result set displayed by the cursor.

Example Static Cursor in SQL Server

SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 

Output of Static Cursor in SQL Server

Output of Static Cursor in SQL Server

2. Dynamic Cursors

SQL Server dynamic cursors are more flexible because they reflect changes in the underlying data, updating the result set dynamically as changes occur. Because of the dynamic nature of the result set, this form of cursor allows for real-time updates, although it may incur additional expense.

Example of Dynamic Cursor for Update in SQL Server

--Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empupdate CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Mohan'
 Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

Output of Dynamic Cursor for Update in SQL Server

Output of Dynamic Cursor for Update in SQL Server

Example of Dynamic Cursor for Delete in SQL Server

-- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empdelete CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Deepak'
 DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee 

Output of Dynamic Cursor for Delete in SQL Server

Output of Dynamic Cursor for Delete in SQL Server

3. Forward-Only Cursors

Forward-only cursors in SQL Server allow for only forward traversal of the result set. They are designed for forward scrolling, making them more efficient for one-way data access and consuming less overall resources than other cursor kinds.

Example of Forward Only Cursor for Update in SQL Server

--Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empupdate CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

Output of Forward Only Cursor for Update in SQL Server

Output of Forward Only Cursor for Update in SQL Server

Example of Forward Only Cursor for Delete in SQL Server

-- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empdelete CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Sonu'
 DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
 Go
Select * from Employee 

Output of Forward Only Cursor for Delete in SQL Server

Output of Forward Only Cursor for Delete in SQL Server

4. Keyset Cursors

In SQL Server, keyset cursors keep a static set of SQL keys from the result set, enabling changes to the data but prohibiting changes to the key values. This strikes a balance between the fixed nature of static cursors and the dynamic adaptability of dynamic cursors, allowing for quick navigation and change.

Example of Keyset Driven Cursor for Update in SQL Server

 -- Keyset driven Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empupdate CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Pavan'
 Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 END
END
CLOSE Keyset_cur_empupdate
DEALLOCATE Keyset_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

Output of Keyset Driven Cursor for Update in SQL Server

Output of Keyset Driven Cursor for Update in SQL Server

Example of Keyset Driven Cursor for Delete in SQL Server

-- Keyset Driven Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empdelete CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 END
END
CLOSE Keyset_cur_empdelete
DEALLOCATE Keyset_cur_empdelete
SET NOCOUNT OFF
 Go Select * from Employee 

Output of Keyset Driven Cursor for Delete in SQL Server

Output of Keyset Driven Cursor for Delete in SQL Server

Limitations of SQL Server Cursor

A cursor has some limitations, thus it should only be used when there are no other options. The limitations are as follows:

  • Cursor uses network resources by requiring a network roundtrip for every record it retrieves.
  • A cursor is a memory resident set of pointers, which implies it consumes memory that other processes on our machine could use.
  • When processing data, it puts locks on a section of the database or the full table.
  • The cursor's performance and speed are slower since it updates table records one row at a time.
  • While cursors are faster than loops, they have additional overhead.
  • Another factor influencing cursor speed is the number of columns and rows brought into the cursor. It is the amount of time required to open your cursor and perform a fetch statement.

Read More:

Summary

Cursors provide row-by-row control in SQL Server, allowing operations that set-based commands cannot. Understand their lifecycle, types, and restrictions so that you may use them efficiently as needed, balancing their benefits against any performance drawbacks.

FAQs

Q1. What is a cursor and what are the different types of cursor?

A cursor holds information about a select statement and the rows of data that it accesses. A cursor is a program that is used to retrieve and process the rows returned by the SQL statement one at a time. Cursors are classified into two types: 
  1. Implicit Cursors
  2. Explicit Cursors

Q2. What is the example of cursor?

Cursors are frequently used to draw attention to text or objects on the screen so that they can be selected. In a word processor, for example, the cursor can be used to select text, format it, and enter new text. Cursors are controlled by users via input devices such as mice, touchpads, and trackballs.

Q3. What are SQL Server cursor attributes?

Each cursor contains a set of properties that allow an application programme to test the cursor's condition. These are the properties %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. This characteristic is used to identify whether or not a cursor is open.

Q4. Which is called cursor?

A cursor is an indicator on a computer display screen that indicates where a user can enter text. In a graphical user interface (GUI) operating system, the cursor is also a visible and moving pointer that the user controls with a mouse, touchpad, or similar input device.

Q5. What are the five different cursors?

Cursor forms are classified into five types: point, polygon, rectangular box, circle, and ellipse.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
Full-Stack .NET Developer Certification Training Jun 30 SAT, SUN
Filling Fast
11:00AM to 01:00PM (IST)
Get Details

Can't find convenient schedule? Let us know

About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this