Cursor

Cursor is a database object to retrieve data from a result set one row at a time.
We are using cursor when we have have to perform operation Row by Row.

Life Cycle of Cursor:

Declare Cursor

A cursor is declared by defining the SQL statement that returns a result set.

Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Fetch

When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

Close

After data manipulation, we should close the cursor explicitly.

Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Example:

Drop Table If Exists Employee

Create table Employee

(

Id int identity(1,1) Primary Key,

FirstName varchar(50) Not Null,

LastName varchar(50) Not Null,

City varchar(50) Not Null,

AadharcardNumber BIGINT Not Null,

FullName varchar(100) Null,

)

Insert Into Employee (FirstName,LastName,City,AadharcardNumber) 

values 

('Manish','Sonawane','Pune',121232565545),

('Ganesh','Khedkar','Sangli',121212125559),

('Pravin','pawar','Pune',653212312123),

('Ghanshyam','Kale','Satara',545456454555),

('Amol','solanki','Nasik',675675674544),

('Nitin','Sonawane','Nagpur',435453453453),

('Sagar','Aher','Nasik',675685674544),

('Govind','Kumar','Nasik',656887674544)

-- declare variables used in cursor & cursor

Declare @Cursor Cursor

Declare @Id Int

Declare @FirstName varchar(50)

Declare @LastName varchar(50)

Set @Cursor=Cursor Forward_only  For

Select Id,FirstName,LastName from Employee 

-- open cursor

Open @Cursor

fetch Next From @Cursor into @Id,@FirstName,@LastName

-- loop through a cursor

  While @@FETCH_STATUS=0

    Begin

   --Select @LastName+' '+@FirstName As EmpName

   Update Employee Set FullName=@LastName+', '+@FirstName where Id=@id

   fetch Next From @Cursor into @Id,@FirstName,@LastName

    End

-- close and deallocate cursor

Close @cursor

Deallocate @cursor

Achieve same output using while loop (cursor Altrenative)

Set Statistics IO ON

Set NoCount ON

Declare @Id Int

Declare @FirstName varchar(50)

Declare @LastName varchar(50)

Declare @i INT=1

Declare @RowCount Int

Set @RowCount=(Select Count(1) from Employee)

 

While (@i<=@RowCount)

BEGIN

Select @id=Id,@FirstName=FirstName,@LastName=LastName from Employee where id=@i
Update Employee Set FullName=@LastName+' whileloop '+@FirstName where Id=@id
Set @i=@i+1

END

In above case small result set 8 rows only, but when large result set we can see difference  in performance.
if we run same query for 8000 Records, then below stats we can see,
please check above video for more details.

Cursor Stats,

While loop stats,