Home » Interview Q&A » Difference between temporary table and table variable in sql server

Difference between temporary table and table variable in sql server

Difference between static and non-static variable in C#
Find most used column names in database - 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

About

Hello friends, My self Dhruv Sheth, I have more than 6 years of Industrial experience as a Software Developer. In my career I got chance to work with asp.net, c#,Web API, JavaScript, JQuery, WPF, WCF , Windows Application, PostScript, vb.net, GhostScript etc. So I have decided to share my knowledge with everyone and finally I have come up with TechnoThirsty.

One thought on “Difference between temporary table and table variable in sql server

  1. dhruvsheth33 says:

    I have added differences on this link “http://technothirsty.com/difference-between-temporary-table-and-table-variable-in-sql-server/” let me know if any other difference is missing.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Time limit is exhausted. Please reload CAPTCHA.




DISCLAIMER:
The content is copyrighted to technothirsty.com and may not be reproduced on other websites without permission from the owner.You may contact us using the information below.