Difference in Temporary Table and Table Variable in Sql Server

Difference in Temporary Table and Table Variable in Sql:

Temporary tables in sql server :

Temporary tables can be used in Stored Procedures, Triggers and Batches but not in user defined functions. Local temporary tables are temporary tables that are available only to the session that created them. Global temporary tables are temporary tables that are available to all sessions and all the users. Local temporary tables are automatically destroyed at the end of the procedure or session that created them. Global temporary tables are dropped automatically when the last session using the temporary table has completed.

We can also drop temporary tables explicitly using drop command similar to normal table. Temporary table name can be of maximum 116 characters PRIMARY KEY, UNIQUE, NULL, CHECK etc can be implemented at the time of creating temporary tables using CREATE TABLE statement or can be added after the table has been created. FOREIGN KEY not allowed.

Temporary table supports adding Indexes explicitly even after creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint. We can’t return a temporary table from a user-defined function.

Table variables in sql server:

Table variables can be used in user defined functions, stored procedures, and batches. Its scope is in the stored procedure, user defined function or batch where it is declared like any local variable we create with a DECLARE statement.

Table variables are automatically cleaned up at the end of the user defined function, stored procedure, or batch in which they are defined. Table variable name can be of maximum 128 characters. PRIMARY KEY, UNIQUE, DEFAULT values, NULL, CHECK can be added, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.

Table Variables doesn’t allow the explicit addition of Indexes after it is declared, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined at the time of declaring Table Variable. We can return a table variable from a user-defined function