Temporary Tables vs Table Variables

Every time there is a lot of debate on #Temporary Tables & Table Variables. And everyone cites his/her own definition and examples. Most of them are bogus and conflicting. So I searched online and compiled a list of enough definition & points that are valid and tested:

Temporary Tables:

1. create table #T (…)

2. Any procedure with a temporary table cannot be pre-compiled. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

3. A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.

4. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures).

5. The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you’re done with it.

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

7. One minor problem with #temp tables is that, because of the session-specific identifier that is tacked onto the name, the name you give it is limited to 116 characters, including the # sign (while other table types are limited to 128).

8. Another potential problem with #temp tables is that, if you enter a transaction and use a #temp table, and then cancel without ever issuing a ROLLBACK or COMMIT, you could be causing unnecessary locks in tempdb.

9. Global temporary tables (##) operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions).

Table Variables:

1. declare @T table (…)

2. Transaction logs are not recorded for the table variables.

3. An execution plan of procedures with table variables can be statically compiled in advance.

4. Table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

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 automatically cleared when the procedure or function goes out of scope, so you don’t have to remember to drop or clear the data.

7. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements. This is not possible with temp tables.

8. Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.

9. You cannot use a table variable in either of the following situations:

a. INSERT @table EXEC sp_someProcedure (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 a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like.

13. You cannot create a named constraint. 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. A table variable will always have a cardinality of 1, because the table doesn’t exist at compile time.

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

19. You cannot insert explicit values into an identity column (the table variable does not support the SET IDENTITY_INSERT ON).

Source: from my personal blog SQLwithManoj: https://sqlwithmanoj.wordpress.com/2010/05/15/temporary-tables-vs-table-variables/