I know this is one of the very old discussed topic and we can find an N number of good articles over the internet.
I also like this topic very much so in this post, I would also 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.
This 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 to create 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 volume of data is changing frequently, query planner requires updated statistics to recompile object so for that we should use Temporary Table.
If the volume of data is not changed frequently, 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 are requiring less time than Temp Table.
The scope of Table Variable is up to Batch or stored procedure after completion of execution they are dropped automatically.
The scope of Temp Table is up to sessions and once the session ends, it dropped 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.