table variables or Temp tables which is better
-
Hi,
I have to create a stored procedure which will insert results into the tables. For this, I am thinking of creating a table variable and pass that table variable to the stored procedure. Is it possible? Also which one would be better. To create a table variable or to create a temp table?
Thanks,
Sridhar!!
-
If you need to share data between procs, you'll have to use a temp table.
Otherwise test both versions (variable, perm) and see which is faster in that situation.
-
The answer is: It depends. Based on the # of records, what is to be done with them, etc.. will determine #table or @table.
Generally, @table is for SMALL amounts of data with minimal processing done to them (I use them for 100 records or less).
#Table are for large data, large amounts of work, etc..
Reasoning:
1. @Table will write to DISK once it reaches a certain size
2. @Table has index limitations that #Table does not have
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thank you.
-
AND table variable datatype is not permited as a procedure parameter!
* Noel -
is the #temp table allowed as a parameter?
-
I didn't mean to imply that! ... just that the poster said:
I am thinking of creating a table variable and pass that table variable to the stored procedure
Because #temp is not a variable you may think that the other one (@Table) which it is, may be used that way
* Noel -
That's why I was asking for clarifications
.Are you back from vacations? Looks like you've been away forever.
-
Yep! Was on vacation last week.
Lots of rain in FL
but every minute of sun and beach that could be taken ... were taken
* Noel -
Lucky b@$t@rd. My vacations are in one month.
-
so YOU are the reason I was soggy last week. I was blaming my mom for flying in. I live in Tampa
Hope you had a good vaca Noel
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply