SQL with Manoj

I see lot of people debating on usage of Temporary Tables & Table Variables. And everyone cites their own definition and examples, and most of them conflicts with each other. So I thought to put the differences & points that are valid and tested:
 

–> Temporary Tables:

1. Syntax: CREATE TABLE #T (..)

2. A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited to 116 chars.

3. The Scope of this Temp-Table is limited to its session, like a Stored Procedure, or a set of nested Stored Procedures.

4. The Temp-Table gets Dropped automatically when the session ends or the Stored Procedure execution ends or goes out of scope.

5. One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved.

6. Global Temporary Tables (##) operate much like Local Temporary Tables; they are also created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all the sessions, until the creating session goes out of scope.

7. One can create desired Indexes on Temporary Tables (like permanent tables) and these make use of Statistics, thus resulting in better query plan compared to Table variables.
 

–> Table Variables:

1. Syntax: DECLARE @T TABLE (…)

2. A Table Variable is also created on disk in the tempDB system database. But the name of this Table Variable is generated completely by the SQL engine and it also differs with other similar named tables created in same or other sessions.

3. The Scope of Table Variables is limited to its BATCH only like other variables. Contrary to the temporary tables, they are not visible in nested stored procedures and in EXEC(@SQLstring) statements.

4. The Table Variable gets Dropped automatically when the BATCH ends (after the GO batch separator) or the Stored Procedure or Function execution ends or goes out of scope.

5. A Table Variable is created in memory, this is a myth. They are also treated as Temp-Tables and created in tempdb, but they performs slightly better than Temp-Tables because there is even less locking and logging in a Table Variable.

6. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a UDF (User Defined Function). You can create a Table Variable within a UDF, and modify the data using one of the DML statements, this is not possible with Temp-Tables.

7. A Table Variable will always have a cardinality of 1, thus statistics are not tracked for them and may result in bad query plan.
 

–> Limitations with Table variables:

8. Table Variables do not participate in TRANSACTIONS and locking.

9. You cannot use a Table Variable in either of the following situations:
a. INSERT @table EXEC spSomeProcedure (Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.)
b. SELECT * INTO @table FROM someTable

10. You cannot Truncate a Table Variable.

11. Table Variables cannot be Altered after they have been declared.

12. You cannot explicitly add an index to a Table Variable, however you can create an inline index through a PRIMARY KEY CONSTRAINT, and multiple indexes via UNIQUE CONSTRAINTs.

13. You cannot create a named Constraint on Table Variables. You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column or DEFAULT CONSTRAINT.

14. You cannot use a user-defined type (UDT) in a column definition.

15. Unlike a #temp table, you cannot DROP a Table Variable when it is no longer necessary, you just need to let it go out of scope.

16. You can’t build the Table Variable inside Dynamic SQL. This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.

17. Table variables are not visible to the calling procedure in the case of nested Stored Procs. It is possible with temp tables.

18. You cannot insert explicit values into an IDENTITY column of a Table variable (the Table Variables does not support the SET IDENTITY_INSERT ON).
 

–> Now the question is when to use either of them?

– Temporary Tables: When you are dealing with large volume of data sets use Temp-Tables, as you can create Indexes on them and they use Statistics for accurate cardinality estimations, thus providing a better query plan.

– Table Variables: When you are dealing with smaller data sets, use Table Variables, as they would not acquire locks and are Transaction free, and may not be affected by the absence of Indexes and Stats.
 

–> Check the full demo here: