Click here to Skip to main content
Click here to Skip to main content

Using Table variable instead of Cursor Variable

, 3 Nov 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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 

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. 

 

License

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

Share

About the Author

S. M. Ahasan Habib
Software Developer (Senior) The Jaxara IT Ltd.
Bangladesh Bangladesh
Mostly I work with MS technologies (ASP.NET MVC, WPF, C#, SQL Server, SSRS, SharePoint, Entity Framework, MSTest, Enterprise Library, MEF, WCF, WebAPI, MS Excel, IIS).
Non MS technologies which I love and use (Resharper, NHiberNet, JQuery, AngularJS, KnockoutJS, NodeJS, Python, MSpec, RihnoMock, Crystal Report, Subversion, Crome)

Comments and Discussions

 
QuestionBut what about Fast_forwar Pinmembercmorton92494-Nov-13 9:48 
AnswerRe: But what about Fast_forwar PinprofessionalS. M. Ahasan Habib5-Nov-13 10:48 
GeneralMy vote of 5 PinprofessionalBrian A Stephens21-Oct-13 6:21 
GeneralRe: My vote of 5 PinmemberS. M. Ahasan Habib21-Oct-13 6:48 

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

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

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