Difference between temporary table and table variable in sql server
| Table variable | Temporary table |
| You can use it in User defined function | You can not use it in User defined function |
| Table name can only be allowed maximum of 128 charecter | Table name can only be allowed maximum of 116 charecter |
| The scope of the table is just for current batch | You can use it in all the sessions and nested store procedure |
| SET IDENTITY_INSERT is not supported | SET IDENTITY_INSERT is supported |
| You can not truncate the table | You can truncate the table for further enhancement |
| After current scope it is automatically destroy | You need to drop the table explicitly |
| More useful for small bunch of data | More useful for large data |
| You can pass table variables back from functions | You can not pass Temporary table back from functions |
| Store Procedure which uses table variable is pre-compiled. So the execution speed is much faster. | Store Procedure which uses temporary table can not be pre-compiled. So the execution speed is lower. |
| Can only have primary index | Can have indexes |
| We can not use COMMIT and ROLLBACK | We can use COMMIT and ROLLBACK in some cases with temporary table |
| Don’t have dispose facility | Have dispose facility |
| DECLARE @Temp TABLE ( Id INT NOT NULL, FName VARCHAR(50), LName VARCHAR(50) ) |
CREATE TABLE #Temp ( Id INT NOT NULL, FName VARCHAR(50), LName VARCHAR(50) ) |
| Similarities | |
| 1. Both are stored in tempDB 2. Both are logged in trasaction log 3. Both support PRIMARY KEY, UNIQUE, NULL, CHECK |
|
I have added differences on this link “https://technothirsty.com/difference-between-temporary-table-and-table-variable-in-sql-server/” let me know if any other difference is missing.