August 12, 2004 at 3:19 pm
If using the TABLE data type in any SQL statement, where does the structure get built.....in memory or tempdb?
August 13, 2004 at 1:35 am
From the same web site:
http://www.sqlservercentral.com/columnists/dasanka/temptablesinsqlserver_printversion.asp
Basically a structure in Memory. BOL has some good info and tips on a TABLE variable, one of those is that its cleaned up automatically and completely.
August 13, 2004 at 6:24 am
Structure will created in the memory.This increases the performance of the query.
August 13, 2004 at 6:56 am
This is a common misconception on table variables. Please see following site ...
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
and in particular...
Q4: 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?
A4: 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).
August 13, 2004 at 6:59 am
You also need to be aware that the performance using a table variable can be far worse than using a #temp table or a temporary permanent table created in tempdb.
table variables are best for small result sets
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply