Click here to Skip to main content
13,256,123 members (44,158 online)
Click here to Skip to main content
Add your own
alternative version


8 bookmarked
Posted 21 Oct 2013

Using Table variable instead of Cursor Variable

, 3 Nov 2013
Rate this:
Please Sign up or sign in to vote.
I will explain here how to use table variable and over come cursor limitation


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
	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. 



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


About the Author

S. M. Ahasan Habib
Bangladesh Bangladesh
I believe in and stay with living-learning culture. I believe learning is fun. I feel coding is like poetry. I am work with various technologies in my professional career (AngularJS, ASP.NET MVC/WebAPI, Scala-Play Framework, Nodejs, SharePoint, Redshift).

You may also be interested in...


Comments and Discussions

QuestionBut what about Fast_forwar Pin
cmorton92494-Nov-13 9:48
membercmorton92494-Nov-13 9:48 
AnswerRe: But what about Fast_forwar Pin
S. M. Ahasan Habib5-Nov-13 10:48
professionalS. M. Ahasan Habib5-Nov-13 10:48 
GeneralMy vote of 5 Pin
Brian A Stephens21-Oct-13 6:21
professionalBrian A Stephens21-Oct-13 6:21 
GeneralRe: My vote of 5 Pin
S. M. Ahasan Habib21-Oct-13 6:48
memberS. M. Ahasan Habib21-Oct-13 6: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 3 Nov 2013
Article Copyright 2013 by S. M. Ahasan Habib
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid