SQL Server: Table Variables (VS) Temp Tables
Posted by Karthick P.K on January 15, 2009
Table Variables (VS) Temp Tables
SQLServer2005 caches temp tables and temp variables only under some conditions.
Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000.
Following are scenarios where temp table/variable are not cached:
1. select into #t
2. alter table #t
3. create index on #t
4. Global temp tables (##t)
5. Local temp tables on adhoc level (nest level 0)
6. table variables are also not cached for dynamic SQL.
What are some of the drawbacks of table variables?
These are some of the drawbacks as compared to temporary tables:
Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement.
Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
The table definition cannot be changed after the initial DECLARE statement.
Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was
created outside the EXEC statement or the sp_executesql stored procedure because table variables can be referenced in their local scope only, an EXEC statement and
a sp_executesql stored procedure would be outside the scope of the table variable.
However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table
variables local scope is in the EXEC statement or the sp_executesql stored procedure.
Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because temporary or permanent tables
are maintained in a database that resides on the physical disk and also logged?
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).
Do I have to use table variables instead of temporary tables?
The answer depends on these three factors:
The number of rows that are inserted to the table and are they joined with other tables.
The number of recompilations the query is saved from.
The type of queries and their dependency on indexes and statistics for performance.
Option recompile can help optimizer to estimate the number of rows table variable refer http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
MSSQLWIKI 
ambien said
Your current write-up has proven helpful to myself. It’s very useful and you are clearly extremely well-informed in this region.
You possess exposed my personal face to be able to numerous thoughts about this kind of matter together with intriquing, notable and solid content material.
Rajesh Tiwari & Manish said
select into #t is always cached.
http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
Karthick P.K said
Read carefully
tangham tanghan tanghin tanghinin tangi tangibile tangibility tangible tangibleness tangibles tangibly tangie tangier tangiest tangile tangilin tanginess tanging tangka tanglad tangle tangleberries tangleberry tangled tanglefish tanglefishes tanglefoot ta said
Excellent confident synthetic vision with regard
to detail and can foresee issues before these people occur.
เรียนพิเศษคณิตศาสตร์ said
It’s hard to come by experienced people in this particular subject,
however, you seem like you know what you’re talking about!
Thanks
bangkok office for rent said
Great blog here! Additionally your website loads up very fast!
What host are you the usage of? Can I get your associate hyperlink on your host?
I desire my web site loaded up as quickly as yours lol
leticia said
Thanks for visiting,
I’m Leticia Figuero.
If you’ve ever been too busy and couldn’t finish a research assignment , then you’ve come to the right place. I assist students in all areas of the writing process . I can also write the essay from start to finish.
My career as a professional writer started during high school. After learning that I was very capable in the field of academic writing, I decided to take it up as a job .
Talented Academic Writer- Leticia Figuero- http://www.magnoliacharterschools.org Company
leonard said
Hi Everybody
Thanks for checking out my about page. My name is Leonard.
I have worked since high school in this niche. My aptitude for writing started at a young age. I wrote poetry as a child and eventually went on to work with my school newspaper.
This early tryst into journalism eventually led me to academic writing. There is plenty of work for qualified writers. I specialize in research papers , but have the skills to do all types of academic writing.
Reach out for more information about rates and a price quote. I’m looking forward to helping you.
Academic Writer – Leonard – socialwebtools.info Company