Temporary Tables (# tables) , table variables and CTEs are commonly used for storing data temporary.
Many people believe that Table Variable will be in the memory and Temp tables will be stored in the Tempdb database.
Let us see this. Following script will declare table variable and query the sys.objects in tempdb database.
So, declaring table variable it self will create a # table in tempdb throwing away the above myth.
Another myth is, table variable will be moved once the data load is high on the table variable. However, Following is a disk usage report taken for temp table and table variable.
Above report shows, that even for 5 rows, temp tables and table variable both have same in sizes which breaks the myth.
I did another test with #tables , table variables and CTE. Bellow graph is write transactions/sec for each scenario. So CTE does not use tempdb at all.
Following is from a KB article (http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k) but this applies to SQL server 2000. Couldn’t find any thing applies to 2008 or 2005.
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).
LinkedIN Group: PASS Professional Association for SQL Server
ReplyDeleteGood job dispelling that myth that the defining characteristic of temp tables is that they are *always* written and served from disk and table variables are *always* stored in memory. Not aware that that it's a commonly thought about myth, but to the extent that it is, one might read up all SQL Server performance in general
http://msdn.microsoft.com/en-us/library/ff647793.aspx
Posted by Chris Anderson
LinkedIN Group: PASS Professional Association for SQL Server
ReplyDeleteYou misunderstand what a CTE is.
A CTE is semantically equivalent to a derived table with the ability to be self-referential.
The table variable is memory residient thing is very old news. More important are the limitations on temp tables and table variables.
Temp tables cannot be used in table valued functions where table variables can be used
Temp tables may have indexes added and table variables only can have Primary and unique key constraints as indexes.
Table variables are dropped at the end of a batch but temp tables are dropped at the end of a session / stored procedure
Table variables are not transactional and do not roll back. temp tables are transactional and do roll back.
Temp tables are visible to called procedures but table variables
Query optimizer always assumes that one row is returned from a table variable. Temp tables with indexes have statistics that generate better plans.
Posted by Alan Weber
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDelete- Even though you can't rollback changes to a table variable, both table variables and temp tables have logged transactions, so dumping a large number of rows into a table variable generally won't lessen your transaction log usage.
- For both temp tables and table variables, SELECT INTO table will generally result in a small fraction of the transaction logging that one gets with INSERT INTO table.
- Generally speaking, for both temp tables and table variables, creating indexes on them offers little performance imovements. Creating an index on a table requires scanning all pages, which negates whatever performance advantage you would get from a one-off reference to an indexed temp table or variable. If you reference the temp table/variable multiple times, then perhaps indexing will get you a net performance improvement, but it generally won't.
Posted by Eric Russell
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDeleteinteresting factoids... i was struggling to see the use of table variables affecting log usage... Is that in the user database or the tempdb? I am working with an environment now that sometimes sees excessive log file size in the tempdb - we know the cure but not the prevention... Minimal use of #temp tables so the phenomonem suprises me.
Posted by Chris Anderson
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDeleteDiscussion:Temp Tables Vs Table Variables Vs CTE
I can't see much use for a #temp table if you are not going to reference it more than once.
Select into creates a table dynamically and cannot be used for @table variables. Table variables require insert into as the table variable is a pre-existing table.
After loading a temp table creating an index makes updating the table much more efficient. A common design pattern is to create a temp table and update the columns by successive queries instead of one very complex query that references all the tables with source data at once.
You can capture error data in a catch block in a table variable before rollback, rollback and still have the error data for logging after the rollback. If the transaction in the catch block is in an uncommitable state then a rollback is required before logging the error data.
Posted by Alan Weber
LinkedIn : SQL Server Professionals
ReplyDeleteBoth temp tables and table variables can either reside in memory or be pushed to disk. There is also a difference in how collation is applied. For temp tables the default is the collation of tempdb and for table variables the collation of the current database.
Posted by Jack Corbett
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDeleteFrom my experience, recompilation of statements used with temp tables is not a given condition. There are conditions where this is true, but it is not as easy as a blanket statement that it "always" happens. So your mileage may vary.
Posted by Ben Miller
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDeleteTo eliminate recompilations where #temp tables are involved, declare them at the beginning of the stored procedure. This will eliminate recompilations; at worse, reduce the number.
Also, in high throughput systems, if you eliminate the drop of the #temp table at the end of the stored procedure, the underlying object can be reused without the overhead of creating it again.
The ability for subroutines to use #temp tables is also critical. I think that thought was incomplete in the first poster (alan's) statements (which are an excellent synopsis!).
I'd also avoid reusing CTEs (multiple joins) as the performance on that is abysmal! Hopefully, the new windowing functions will take care of that.
Posted by Jonathon Moorman