Start Learning Free
Previous Tutorial
21
NovCursors In SQL Server
16 Oct 2024
Intermediate
419K Views
23 min read
Cursors In SQL Server
Cursors in SQL Server are a useful tool for handling data row by row rather than working with complete result sets at once. Normally, SQL works with sets of data, which means it retrieves or updates numerous rows at once. But what if you need to execute operations on each row separately, such as applying sophisticated logic to each one? That's where cursors come in.
In this SQL Server Tutorial, We will see how 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.What is a Cursor in an SQL Server?
In SQL Server, a cursor is a database object that allows us to retrieve and manipulate each row one at a time. It is nothing more than a row pointer. A cursor 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
Read More - Top 50 DBMS Interview Questions and Answers
Life Cycle of Cursor
The life cycle of a cursor can be divided into five phases, as follows:
- Declare Cursor
- Open Cursor
- Fetch Cursor
- Close Cursor
- Deallocate 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:
- Static Cursors
- Dynamic Cursors
- Forward-Only Cursors
- Keyset Cursors
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
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
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
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
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
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
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
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. Also, consider our SQL Server Course to learn more basic operations of SQL Server.
FAQs
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:
- Implicit Cursors
- Explicit Cursors
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.
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.
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.
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.