Using Table variable instead of Cursor Variable

Introduction 

• Download script source  

We all know that SQL is a set based language. We can process data based on row set. But many times we need to process data row by row. In that cases often we use Cursor for fetching single row at a time and process that row. For the performance point of view cursor is very slow. It has some limitations too.

First limitation, when it reads rows from table that time it locks the table rows before fetching, no other thread/process can access that row and all wait until it  that lock is released(If you allow dirty read in that case it is different). 

Second limitation, if you want to update source rows of the cursor, it will throw exception, because it will exclusively locked that row set.  If we use table variable instead of cursor variable then often we can overcome that limitations. 

Using the code 

For better understanding I create a table name MyTestTable which has 3 fields 

• Code(char) 
• Name (varchar)  
• Flag(int)   

 Now i want to update Flag field based on the following business logic: 

•    if Name field length >=5 then value will be 1
•    if Name  field length >=3 but < 5 then flag value will be 2
•    else flag value will be 0   

If so then just need to write following script   

First define schema(table/column) then insert sample data 

create table MyTestTable(Code char(3), Name varchar(50), Flag int); 

truncate table MyTestTable;--remove all previous rows
insert into MyTestTable(Code, Name) values('001', 'ABCDEF'), ('002', 'GHI'), ('003', 'JK'), ('004', 'L');  

Then row by row processing tsql code  

--declare a table variable with extra sl auto identity field
declare @MyTableVariable TABLE(Sl int identity, Code char(3), Name varchar(50), Flag int);                                                                                            --insert all table data to table variable
insert into @MyTableVariable select * from MyTestTable;
 
declare @Counter int = 0, @Name Varchar(50), @Code char(3);
while (1=1)--start infinite loop
begin
	set @Counter +=1; --increment loop counter
	set @Code = null; --initialize with null
	select @Name=Name, @Code = Code from @MyTableVariable WHERE SL = @Counter;
	if @Code is null
	    break;--when no more rows found then exit from the infinite loop
	
	declare @FlagValue int = 0, @NameLength int = len(@Name);                           
        --flag value set logic 
	if @NameLength >= 5
	    set @FlagValue = 1
	else if @NameLength >= 3
	    set @FlagValue = 2
	else
	    set @FlagValue = 0;                                                             
        --update original table
	update MyTestTable set Flag = @FlagValue where Code = @Code;
end 

In the above code just create a auto generated identity field named sl Based on that field fetch every row and its field and process that.  

Point of Interest  

If we use table variable instead of cursor, no row locking issues will be exists and no exception will through when update source data. When i find any situation where row by row processing is needed, instead of cursor i try to find out alternate solution with table variable. Another important things is, table variable processing is much more faster then cursor processing. Previously i told that cursor lock source table row-set so if we use cursor wrongly then dead lock will be raised.  If you consider all that cases then table variable is much more safer for processing data row by row.