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
- Name (varchar)
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;
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
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
set @FlagValue = 0;
--update original table
update MyTestTable set Flag = @FlagValue where Code = @Code;
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.