January 28, 2011 at 3:47 pm
There is a bit of a debate going on in our office over the difference, if any, between a #temp table and the table variable (@temp).
One person in the office claims 'insider' knowledge and is indicating that these are both exactly the same and that both always get physically written to tempdb. His claim is that the 'memory' table is simply a marketing gimic.
Documentation says that the table variable (@temp) table is more efficient and lists a number of reasons including scoping, and lack of need for recompilation.
Is anyone able to offer their experience and knowledge into the inner workings of these and what the real differences are?
thanks!
January 28, 2011 at 4:03 pm
Here is a short blog post showing the differences:
http://www.sqlservercurry.com/2010/03/temporary-tables-vs-table-variables.html
Table variables can be written to the temp db, but they can also perform better with small data sets.
p.s., I don't think that Microsoft would try to pull a marketing hype scheme like that since performance is easily measureable and plenty of very smart people, such as the DBAs and the developers on this site, who know how to measure it and aren't afraid to test it. 😀
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
January 28, 2011 at 10:32 pm
A huge difference can be seen in execution plans. Statistics can be created on temp tables helping with cardinality estimates whereas table variables do not so they are assumed to have one record when a plan is created. Small sets of data it isn't a huge deal but larger sets can cause a less than optimal execution plan and can result in performance issues. Each has their strengths and weaknesses it's import and to know the differences and use them when appropriate they don't perform the same in all instances.
January 29, 2011 at 12:10 am
mtillman-921105 (1/28/2011)
Here is a short blog post showing the differences:http://www.sqlservercurry.com/2010/03/temporary-tables-vs-table-variables.html
Unfortunately that article is completely wrong on many points.
Both temp tables and table variables are created in tempDB and assigned space in that database. They are not memory only.
Changes to table variables are logged
A temp table's log activity does not remains till it is manually cleared or the server restarts. TempDB is in simple recovery and the logs are truncated on checkpoint no matter what is in them.
Stored procedures are not pre-compiled. They have execution plans created on first execution and temp tables don't always cause recompiles anymore (caching)
Table variables are not limited to just a primary key, though you do need to play a bit of a trick to get indexes on them.
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2011 at 12:12 am
jerry-621596 (1/28/2011)
One person in the office claims 'insider' knowledge and is indicating that these are both exactly the same and that both always get physically written to tempdb. His claim is that the 'memory' table is simply a marketing gimic.
You know what, he's pretty much right.
There are differences - less logging for table variables (not no logging), no recompiles because they have no statistics, tighter scope, but the memory vs disk behaviour is the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2011 at 12:59 am
Gails wonderful article explains it all very well.
Just in case you still want other refs :
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 29, 2011 at 1:05 am
@Gail
Do you think table variables might also face "Allocation Page Contention" the way temp tables do ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 29, 2011 at 5:23 am
Sachin Nandanwar (1/29/2011)
Do you think table variables might also face "Allocation Page Contention" the way temp tables do ?
Yes. They're tables in TempDB, they're allocated pages just like any other table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2011 at 5:50 am
GilaMonster (1/29/2011)
jerry-621596 (1/28/2011)
One person in the office claims 'insider' knowledge and is indicating that these are both exactly the same and that both always get physically written to tempdb. His claim is that the 'memory' table is simply a marketing gimic.You know what, he's pretty much right.
There are differences - less logging for table variables (not no logging), no recompiles because they have no statistics, tighter scope, but the memory vs disk behaviour is the same.
Don't table variables also behave differently in transactions? (I remember a Question of the Day that tested this).
And if I'm not mistaken, you cannot perform DDL statements on a table variable once it is created.
TRUNCATE TABLE can also not be performed on a table variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 29, 2011 at 6:07 am
yes, yes and yes. I wasn't listing all the differences (that would be an essay), just some.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 2:06 am
January 31, 2011 at 8:19 am
GilaMonster (1/29/2011)
Unfortunately that article is completely wrong on many points.Both temp tables and table variables are created in tempDB and assigned space in that database. They are not memory only.
According to this article which may be dated already (2008), a table variable can be in memory - or at any rate it implies as much:
"First, the table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb."
Apparently, SQL will use memory instead when it can. If so, that's an intelligent design since of course writing to disk is slower than using memory.
edited to include more of the quote
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
January 31, 2011 at 8:27 am
mtillman-921105 (1/31/2011)
Apparently, SQL will use memory instead when it can. If so, that's an intelligent design since of course writing to disk is slower than using memory.
They are always entered into the tempDB system tables. They are always allocated space within TempDB. Whether SQL manages to keep them in the buffer pool or whether it writes them down into the actual file on disk is another matter, and for that they are the same as temp tables. Kept in the buffer pool if possible, written to disk if not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 1:13 pm
GilaMonster (1/31/2011)
mtillman-921105 (1/31/2011)
Apparently, SQL will use memory instead when it can. If so, that's an intelligent design since of course writing to disk is slower than using memory.They are always entered into the tempDB system tables. They are always allocated space within TempDB. Whether SQL manages to keep them in the buffer pool or whether it writes them down into the actual file on disk is another matter, and for that they are the same as temp tables. Kept in the buffer pool if possible, written to disk if not.
I'm confused. The post on your site tries to prove that a table variables' data is written to the temp db. But above you say that table variables and temp tables can be retained "in the buffer pool" - i.e., in memory? :unsure:
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
January 31, 2011 at 2:04 pm
Do you understand how SQL uses the buffer pool for data cache? Need to see where to start explanation from.
Being in the buffer pool and being on disk are not mutually exclusive possibilities. Being in memory doesn't mean not allocated space in the DB and vis versa.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply