Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server
Tip/Trick

Using Table variable instead of Cursor Variable

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
3 Nov 2013CPOL2 min read 38.7K   109   8   4
I will explain here how to use table variable and over come cursor limitation

Introduction 

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 

SQL
create table MyTestTable(Code char(3), Name varchar(50), Flag int); 
SQL
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. 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Bangladesh Bangladesh
How do I describe myself to you? How can I explain that this is true?
I am who I am because of you! My work I love you !!

Comments and Discussions

 
QuestionBut what about Fast_forwar Pin
cmorton92494-Nov-13 8:48
cmorton92494-Nov-13 8:48 
AnswerRe: But what about Fast_forwar Pin
S. M. Ahasan Habib5-Nov-13 9:48
professionalS. M. Ahasan Habib5-Nov-13 9:48 
GeneralMy vote of 5 Pin
Brian A Stephens21-Oct-13 5:21
professionalBrian A Stephens21-Oct-13 5:21 
GeneralRe: My vote of 5 Pin
S. M. Ahasan Habib21-Oct-13 5:48
professionalS. M. Ahasan Habib21-Oct-13 5:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.