We generally have resultset of our select statements. Some times we want to perform any logic on the each row of the resultset. In these kind of scenario where we want to process each row and perform any logic on the row we make use of Cursor. A Cursor is always associated with SQL Select statement and will process each row from the resultant resultset.
--Declare Variables DECLARE @ID int DECLARE @Description nvarchar(100) --Declare Cursor DECLARE PRODUCTCURSOR CURSOR FOR SELECT ID,[Description] FROM Product WHERE ID < 2000 --With Open command cursor will point towards first row of result set OPEN PRODUCTCURSOR --Cursor will retrieve first row and set to @Id and @description FETCH NEXT FROM PRODUCTCURSOR INTO @ID,@Description --@@FETCH_STATUS global variable to state whether cursor contains rows in the resultset or no --When all records are looped it will will return -1 WHILE(@@FETCH_STATUS=0) BEGIN PRINT ('ID= ' +CAST(@ID AS NVARCHAR(40)) +' Description ='+@Description) --Cursor will retrieve next row and set to @Id and @description FETCH NEXT FROM PRODUCTCURSOR INTO @ID,@Description END --Close the Cursor CLOSE PRODUCTCURSOR --DeAllocate Cursor DEALLOCATE PRODUCTCURSOR
Consider that we have a below table with 450002 number of records with below structure.
Now business team recognized that there are some customers which has wrong Account Numbers updated in the table corresponding to Pan number and they want this to be rectified, they share the xls file with the Pan Number and there respective Account details as shown below:
So now in order to update these AccountNumbers i first store them on database by uploading via excel import in sql server.
Edit the table Name if you want to give new name or use the Sheet as given by SQL server. Click on edit mapping if you want to change the data type of column.
Click on Next
Status of Import
Now in order to update the same we will make use of Cursor to update these 890 records. We will use Cursor for the same.
In order to do the same i have created belo cursor which will update the records of the corresponding records present in Sheet table which we imported from excel.
--Declare Variables DECLARE @PanNumber nvarchar(20) DECLARE @AccountNumber nvarchar(20) begin transaction declare @ToUpdateAccountNumber nvarchar(20) DECLARE @ToUpdatePanNumber nvarchar(20) --Declare Cursor DECLARE UpdateCustomerCURSOR CURSOR FOR SELECT [Pan Number],[Account number] FROM [dbo].[Sheet1$] OPEN UpdateCustomerCURSOR FETCH NEXT FROM UpdateCustomerCURSOR INTO @PanNumber,@AccountNumber WHILE(@@FETCH_STATUS=0) BEGIN update mstCustomer set AccountNumber=@AccountNumber where PanNumber=@PanNumber --Cursor will retrieve next row and set to @Id and @description FETCH NEXT FROM UpdateCustomerCURSOR INTO @PanNumber,@AccountNumber END --Close the Cursor CLOSE UpdateCustomerCURSOR --DeAllocate Cursor DEALLOCATE UpdateCustomerCURSOR
So now when we execute the below block to update the records as shown below
We can see it took 1 min 18 second to complete the update process, we can say it as time consuming query. Generally it’s said to avoid using cursor due to it’s performance issues as we just saw in above figure. So in order to get the performance benefits generally i solve this situation using Joins.
update mstCustomer set AccountNumber=[dbo].[Sheet1$].[Account number] from mstCustomer inner join [dbo].[Sheet1$] on mstCustomer.PanNumber= [dbo].[Sheet1$].[Pan Number]
We can clearly see the performance we get threw join. The same query where took minutes whereas over it took only 6 seconds to perform the same operation. There is a drastic improvement of performace when we used join over cursor.