Monday, November 30, 2009

Temp table vs Table variable



Ok. Another done to death topic. I will try my best to say something that has not been said.( or atleast said little ).

Before we get started , couple of popular myths to be clarified.

* Both temp table and table variable use tempdb.
The difference is table variable's data is initally stored in memory
and when the data grows bigger it is pushed to tempdb.
The object definition of table variable when it is created is stored in tempdb.

* Performance wise both have their advantages and disadvantages. There are a few
scenarios where temp table outperforms a table variable which we will attempt to
see.

Few pointers about them are given below:

* Table variable can have constraints but not indexes. This means that one can
create primary key( creates cluster index ) on table variable or a unique
constraint ( creates non cluster ) but not non unique clustered or non
clustered indexes.However, temp table can have constraints and indexes.
* Statistics are not maintained for table variable but yes for temp table
* Table variables are not recompiled but temp table's are recompiled
* Scope of a table variable is limited to the batch/section but temp table is
available thro out the session.
* changes made to a table variable cant be rollbacked but on a temp table
rollback is possible.

The points 1,2,3 have serious implications on the performance of your code and becomes a major factor in deciding your temp object type.

This post is just a starter placing a few facts on this topic. Next few posts,
I will come up withn few scenarios and examples to compare the performance of
both the objects.So please hang on for few more days for in depth analysis :)

No comments: