Difference between Table Variable and Temporary Table in SqlServer?
1. Table variables have a well defined scope.They will be cleared automatically at the end of the batch(i.e current batch of statements) where as local tempoarary table will be visible to current session and nested stored procedures. Global Temporary table will be visible to the all the sessions.
2.Table variables are created using Declare statement.We can't create table variable using Select * into @tableVariableName statement.But we can create temporary table using Create table statement as well as Select * into #tempTableName statement.
3.In Sql Server 2008 onwards we can pass the table variable as a parameter to the StoredProcedures.But we can't pass the temporary table as a parameter to the stored procedure.
4.We can use the table variable inside the UDF(user defined function) but we can't use the temporary table inside the UDF.
5. We can't create the named constraints inside the table variable but we can create named constraints inside the temporary table.
Note: We need to consider multi user issues while creating the named constraints inside the temporary table.Because if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘<objectname>’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.
6.We can't insert explicit values in to the Identity column in table variable. because SET IDENTITY_INSERT ON is not supported by table variable. But we can do it in temporary table as the SET IDENTITY_INSERT ON is supported by temporary table.
7.We can't truncate the table variable but we can truncate the temporary table.
8.Transactions and Rollback do not affect the table variable .But Tempoary table is affected by both the Transactions and Rollback.
9.Table variable will create the only the fewer recompilations as compared to the tempoary table. Tempoary table will create the more recompilation when we create and insert the more records into it..
10.If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.
11.We can use the User defined data type and XML collections in both the table variable and Tempoary table .But incase of the tempoary table it must be in Tempdb database to use.
12.We can use Insert into TableVariable/TemporaryTable Exec SP_Name in Both the Table variable and tempoary table.But Table variable won't support this in Sql Server 2000 .
13. Table variable can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement where as in tempoary table indexes can be added after the table has been created.
14 Table variable name is limited to Maximum 128 characters where as temporary table is limited to 116 character. This is because the database engine must be able to identify different temporary tables created by different sessions at the same time. To do so, it internally appends a numeric suffix to the end of the name.
15.In table variable string columns inherit collation from current database where as in temporary table String columns inherit collation from tempdb database.
16.Although a table variable is a variable, it cannot be assigned to another table variable. but we can do it temp table.
17.Table variable won't support Alter table statement but temp table will support.
18.We can write the temp table into Log file we can't in table variable.
Similarity:
In Both temporary table and table variable we can't create the Foreign key constraint. We can have only Primary key ,Unique key , Null and Check constraints in both the table.
When to Use Table variable and Temporary table
1.If the number of rows that you are going to be inserting into the table is very small, then we can use the table variable or else we can use the temporary table.
2.If your process requires you to perform DDL statements on the table after creation, then you will need to use a temporary table.
Usage:
we can use the table variable inside the UDFs, Stored Procedures, Triggers, Batches. where as we can use the temporary table only inside Stored Procedures, Triggers, Batches.we can't use it in UDF.
Creation:
Table variables are created using Declare statement.
example:
Declare @tableVaribbleName table(col1 int,col2 varchar(50))
Local Temporary tables are created using # and Global Temp tables are created using ## .
example:
Create table #localtemptable(col1 int,col2 varchar(50))
Create table ##GlobalTempTable(col1 int,col2 varchar(50))
Scope:
A.Local Temporary table will be dropped when
i. We we drop it manually by using Drop #tableName statement.
ii.If it is a local temporary table and it was created inside a stored procedure, it will be dropped when the stored procedure is finished.
iii. All other local temporary tables will be dropped at the end of the current session.
B.Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.
C. Table variable will be cleared automatically at the end of the current batch.(i.e. batch of statements).
