March 27, 2003 at 2:23 am
Here my 2 cents, i am usgin Schema Alert of lumigent and from the messages i found this
"The user alpha created a new table named "@dtTable" in the "tempdb"
database on Server1. The user was logged in via an application that
identified itself as "Internet Information Services".
So this tells that tables variables are also created in tempDB and only contexts diifers an SP needs no recompilation.
is it correct?
Kind Regards,
Affan
March 29, 2003 at 9:24 am
Here are my two (euro) cents:
To quote http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B305977
quote:
Q: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?A: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Regards, Hanslindgren.
March 31, 2003 at 9:22 pm
Ever since I found out about table variables I have used it extensively, however, there is a downside. There is a noticable degradation in performance the larger the amount of data returned. When that happens, I switch to temp tables.
I now use table variables for the purpose of looping,instead of using cursors. This is where I find that it shines.
Guess I am saying the same thing everybody else is 🙂
April 4, 2003 at 12:15 am
I think the problem with the temp tables are recompiles and keeping fix plans is not a good idea. and down side whould be same for table variables or temp tables as both are created in tempdb. only scope and recomplation differs.
AM i right?
Kind Regards,
Affan
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply