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.