I know this is one of the timeworn discussed topics and we can find an N number of good articles on the internet.
I also like this topic very much so in this post; I would like to write a small note on this.
First, Very important point:
Temp table is stored in TempDB and Table variable is stored in Memory.
A above statement is 100% wrong.
Table variable is also stored definition into TempDB and if the data volume is increased, sometimes it also stores data into TempDB.
For that reason, SQL Server 2014 introduced Hekaton storage engine for Memory optimized Table.
"SQL Server 2014: What is Hekaton?"
"SQL Server 2014: Create Memory Optimized File Group and Table"
If we require creating a unique index on data, we should use Temporary Table. We can not apply Index on Table Variable.
If we are dealing with large number records for inserting and deleting, we should use a temporary table. If we have a small quantity of data, we should use Table Variable.
If the amount of data is frequently changing, query planner requires updated statistics to recompile object so for that we should use Temporary Table.
If the amount of data is not frequently changing, we should use Table Variable for better performance.
When we require rollback because of an outer user transaction, we should use Table Variable because it automatically discard all the data from the session.
If we would like to avoid locking issue, we should go with Table Variable because Temp Table is fully dependent on TempDB so sometimes it holds the transactions.
We can define explicit transaction on Temp Table where with the Table Variable we cannot define explicit transaction.
The maintenance and creation of metadata for Table Variable require less time than Temp Table.
The scope of Table Variable is up to Batch or stored procedure after completion of execution they drop automatically.
The scope of Temp Table is up to sessions, and once the session ends, it drop automatically, or we can also drop explicitly.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.