Temporary tables and table variables can be used in pretty much the same way. One question which is asked, "Which one is better". To answer that it is important to understand a few of the differences between the two. Once we understand how these two types of tables are different we will better understand which we need to use when.
Creating a temporary table is very similar to creating a regular table. In order to do this we use the following code
create table #myTempTable (columnA typeA, columnB typeB, ...)
Table variables are created as regular variables are declared. The type of the variable is just defined as a table.
declare @myTableVariable table (columnA typeA, columnB typeB, ...)
Table variables do not record any entries in transaction logs. Temporary tables do store their transactions. Depending on what you are trying to accomplish this can be a benefit for either of the two types of tables. It is often very important to keep track of the transaction log, and in these instances a temporary table is far better for your needs, but other times the transaction log is simply unnecessary and a table variable would be the optimal choice.
I've heard of great performance gains in temporary tables because of their ability to be pre-compiled. I've also heard that table variables are faster than temporary tables in general. I believe this has to do with the nicely defined scope of table variables. Because of this they may use fewer resources than temporary tables. An example of this is the transaction log I mentioned earlier.
For the most part a table variable is just as flexible as a temporary table, and in most instances it also out performs temporary tables in my experience.
One thing to watch out for when using temporary tables is the possibility of causing your store procedure to recompile. Microsoft has a Knowledge Base Article about Troubleshooting stored procedure recompilation which discusses this danger. Table variables will not cause this problem.
I would recommend the use of table variables for everyday use, and only using temporary tables when they're required. Instances of this include the need for a transaction log. Or any time you feel like writing extra lines of code simply to perform cleanup on your temporary table. But that is just my $0.02 on this issue.
Happy SQL writing!
Comments