Difference between temporary table and table variable in sql server

Difference between temporary table and table variable in sql server

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

One comment

  1. 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.

Leave a Reply