Advantages of Table variables over Temp tables in Stored Procedure

Question

Post reply

Advantages of Table variables over Temp tables in Stored Procedure

Sivagurunathan

SSC Enthusiast

Points: 193
More actions
June 24, 2004 at 6:14 am

#161565

Hi all,
I have the following code snippets in my stored procedure.
declare @aa table(name varchar(10),dept varchar(10))
insert @aa select name,dept from test
select * from @aa
Same thing I can use a temp table in my stored procedure.
What will be the advantage of using table variables over temp tables in stored procedure?
I would appreciate your valuable inputs.
Thanks and Regards,
Sivaguru

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 1

As far as i see this the major advantages of using table variables in sps are

1) They result in less recompilations

2) These variables when used in transactions involve less of locking.

For more information find "Table Variables" in the index in BOL (Books Online). This is something new in SQL Server 2000.

Cheers,

Arvind

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 2

As far as i see this the major advantages of using table variables in sps are

1) They result in less recompilations

2) These variables when used in transactions involve less of locking.

For more information find "Table Variables" in the index in BOL (Books Online). This is something new in SQL Server 2000.

Cheers,

Arvind

Sivagurunathan SSC Enthusiast Points: 193 More actions · Answer 3

Aravind,

Thanks a lot for your reply.

Do send me the Book online URL and your email id.

we will be in touch.

Regards,

Sivaguru

schleep SSChampion Points: 12582 More actions · Answer 4

You need to do some more research, and evaluate how and where these tables are going to be used.

In short, @tables are not always the best way to go. As always, your particular situation will determine if they will be beneficial or not. In my experience, when the table will have more than 4-500 rows, it has sometimes made a positive difference to switch to #temp tables.

P

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 5

Schleep,

I agree with you there....its recommended to use temp tables rather than table variables when the expected size of data that will be stored is huge...but it also depends on the allocated storage size of Tempdb and the growth factor there. I am sure you are aware of this!!

Siva,

you can contact me @ dnivrav@rediffmail.com

Cheers,

Arvind

sholliday SSCrazy Points: 2100 More actions · Answer 6

#tempTables also have nested scope. Aka, if you call a recursive stored procedure OR you have prcA call prcB (and prcA defines the #tempTable), then prcB can use it also.

The same is NOT true of @variableTables

That being said, I use @variableTables the most now. I like them, and they don't cause scope problems. (Aka, if prcA and prcB declare the same-named #tempTable, its messes things up). @variableTables are local scope only.

I do have a recursive stored procedure where I NEED a #tempTable.

..