In this article, We will learn about the concept of Cursor in MS SQL Server.
- A cursor in SQL Server is a temporary work area that, is created in system memory when a SQL statement is executed. A SQL cursor is a set of rows together with a pointer that identifies a current row.
- It is a database object to retrieve data from a result set one row at a time.
- A cursor can hold more than one row but, can process only one row at a time. The set of rows the cursor holds is called the active set.
- A cursor is useful when we want to manipulate one row at a time.
Cursors extend result set processing by Following :
- Allowing positioning at specific rows of the result set.
- Retrieving one row from the current position in the result set.
- Supporting data modifications to the rows at the current position in the result set.
- Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.
- Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.
There are two types of cursors in SQL:
- Implicit Cursor: Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors are allocated by SQL SERVER when the user performs DML operations.
- Explicit Cursor: Explicit Cursors are Created by Users whenever the user requires them. Explicit Cursors are used for Fetching data from Tables in Row-By-Row Manner.
Cursor Scope :
Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.
- GLOBAL: specifies that the cursor name is global to the connection.
- LOCAL: specifies that the cursor name is local to the Stored Procedure, trigger, or query that holds the cursor.
Life Cycle of the cursor
We can describe the life cycle of a cursor in the five different sections as follows:
1. Declare Cursor
The first step is to declare the cursor using the below SQL statement :
Syntax :
DECLARE cursor_name CURSOR FOR select_statement;
We can declare a cursor by specifying the name of the cursor with the Keyword CURSOR after the DECLARE keyword. Then, write a SELECT statement that defines the result set for the cursor.
2. Open Cursor
A second step is to open the cursor to store data retrieved from the result set. We can do this by using the below SQL statement :
Syntax :
OPEN cursor_name;
3. Fetch Cursor
A third step is in which rows can be fetched one by one to do data manipulation like insert, update, and delete operations on the currently active row in the cursor.
There is a total of 6 methods to access data from the cursor. They are as follows :
FIRST is used to fetch only the first row from the cursor table.
LAST is used to fetch only the last row from the cursor table.
NEXT is used to fetch data in the forwarding direction from the cursor table.
PRIOR is used to fetch data in a backward direction from the cursor table.
ABSOLUTE n is used to fetch the exact nth row from the cursor table.
RELATIVE n is used to fetch the data in an incremental way as well as decremental way.
Syntax :
FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM cursor INTO list_Of_Variable;
We can also use the @@FETCHSTATUS function in SQL Server to get the status of the most recent FETCH statement cursor that was executed against the cursor. The FETCH statement was successful when the @@FETCHSTATUS gives zero output. The WHILE statement can be used to retrieve all records from the cursor.
@@FETCHSTATUS return type is Integer.
The return value will be,
0 The FETCH statement was successful.
-1 The FETCH statement failed or the row was beyond the result set.
-2 The row fetched is missing.
-9 The cursor is not performing a fetch operation.
For Example :
WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cursor_name; END;
4. Close Cursor
A fourth step is in which the cursor should be closed after we finished work with a cursor. The below statement is used to close the cursor.
Syntax :
CLOSE cursor_name;
5. Deallocate Cursor
The fifth and final step in which we will erase the cursor definition and release all the system resources associated with the cursor. The below statement is used to deallocate a cursor.
Syntax :
DEALLOCATE cursor_name;
Example :
First, we create a table as follows,
CREATE TABLE tbl_Employee (ID INT PRIMARY KEY IDENTITY(1,1), EmplyeeName VARCHAR(50), City VARCHAR(50), Salary INT )
Now we, insert the record into the Table as follows,
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Mustakim','Valsad',1000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Mihir','Surat',2000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Satish','Bardoli',3000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Priyank','Rander',4000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Nishant','Navsari',5000)
A Record will be as follows,
Let’s create a CURSOR as follows,
DECLARE @Ename VARCHAR(50)
,@Salary INT
,@City VARCHAR(50)
DECLARE EmpCursor CURSOR FOR
SELECT e.EmplyeeName,e.Salary,e.City
FROM [TEMP].[dbo].tbl_Employee e With(Nolock);
OPEN EmpCursor;
FETCH NEXT FROM EmpCursor into @Ename,@Salary,@City
PRINT '| Employee Name | City | Salary |'
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' | ' + @EName + ' | ' + @City + ' | ' + CAST(@Salary as varchar(50)) + ' | '
FETCH NEXT FROM EmpCursor INTO @Ename,@Salary,@City
END
CLOSE EmpCursor;
DEALLOCATE EmpCursor;
Output as follows,
Advantages of Cursor:
- By using Cursor we can perform row by row 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 the cursor, a better response time is achieved.
- If we make updates to us without using cursors in your application then we must send separate SQL statements to the database server to apply the changes. This will be because of 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 the cursor, better concurrency Control can be achieved.
- Cursors can be faster than a while loop but at the cost of more overhead.
Disadvantages of Cursor:
- A cursor in SQL is a temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So it will occupy 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.
Now, We will see the live example to compare Between WHILE Loop and CURSOR to see the performance.
So, First, we insert the record in Bulk,
Declare @Min int
,@Total int
SELECT @Min=0
,@Total=20000
while (@Min<@Total)
Begin
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Mustakim','Valsad',1000)
SET @Min=@Min + 1
End
Now, we will get all records by using WHILE Loop as follows,
While Loop Example :
Declare @TotalRows Int
,@Id Int
,@Cnt Int
,@EmplyeeName Varchar(50)
,@City Varchar(50)
,@Salary Int
Select @TotalRows = Count(1)
,@Id = 0
,@Cnt = 0
From tbl_Employee As te WIth (Nolock)
While (@Cnt < @TotalRows)
Begin
Select Top 1
@Id = te.ID
,@EmplyeeName = te.EmplyeeName
,@City = te.City
,@Salary = te.Salary
From tbl_Employee As te With (Nolock)
Where te.ID > @Id
Order By te.ID Asc
PRINT ' | ' + @EmplyeeName + ' | ' + @City + ' | ' + CAST(@Salary as varchar(50)) + ' | '
Select @Cnt += 1
End
Output,
In the Above result, We can see that while loop takes 00.00.00.716 Elapsed time to get 20,0000 records.
Now, will get all records by using above created CURSOR and We can see the result and time taken by CURSOR as follows,
In the Above result, We can see that CURSOR takes 00.00.00.904 Elapsed time to get 20,0000 records.
As we see the comparison between CURSOR and WHILE Loop, We can see that CURSOR takes more time than WHILE Loop.
So here, you can use WHILE Loop or CURSOR as per your requirement and I hope this article will help you to understand the concept of CURSOR.
Thank you.
