Introduction
Before reading this article, I will recommend you read these articles:
This article explains the basic differences between table variables and temp tables. Because these things are mostly and widely used for temporary storing mechanisms in the stored procedures in SQL Server.
Difference Between Temp Table and Table Variable
| Temp Table |
Table Variable |
| A Temp table is easy to create and back up data. |
Table variable involves effort when you usually create normal tables. |
| Temp table results can be used by multiple users. |
The table variable can be used by the current user only. |
| The temp table will be stored in the tempdb. It will make network traffic. When you have large data in the temp table then it has to work across the database. A Performance issue will exist. |
The table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb. |
| The temp table can do all the DDL operations. It allows for creating the indexes, dropping, altering, etc. |
The table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only. |
| Temp table can be used for the current session or global. So that a multiple-user session can utilize the results in the table. |
A table variable can be used up to that program. (Stored procedure) |
| When we do the DML operations with the temp table then it can be rollback or commit the transactions. |
Temp variable cannot use the transactions. But we cannot roll back or commit for a table variables. |
| The function allows us to use the table variable. |
Functions cannot use the temp variable. Moreover, we cannot do the DML operation in the functions but using the table variable we can do that. |
| Where the table variable won't do like that. |
The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every subsequent call. |