This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Sharing one of the timeworn topics and we can also find an N number of good articles on this.
I also like this topic very much so 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.
The 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 cannot 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 requires less time than Temp Table.
The scope of Table Variable is up to batch or stored procedure after completion of execution it drops automatically.
The scope of Temp Table is up to sessions, and once the session ends, it drops automatically, or we can also drop explicitly.
