Difference between Table Variables and Temporary Tables

There are four types of temporary tables Local Temporary tables, Global Temporary Tables Permanent Tables and Tables variables.

How to declare Temporary Tables

CREATE TABLE #people
(
    id INT,
    name VARCHAR(32)
)

DECLARE @people TABLE
(
    id INT,
    name VARCHAR(32)
)

 Scope

The temporary table is deleted automatically when it is goes out of the scope but you should manually drop it to use it again.

DROP TABLE #people

But in the table variable the table is dropped automatically when it goes out of the scope. 

What are the difference between Temp and Table Variables

1.Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.

2. You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable.

FYI: However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

3. You use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table because table variables doesnot create any indexes.

4. You cannot use a table variable in Select INTO

    SELECT * INTO @table FROM someTable

5. You cannot truncate a table variable. 

6.  You cannot drop a table variable.

7. You cannot generate a table variable's column list dynamically, the below will throw the error

     SELECT * INTO @tableVariable 

 8. Table variables must be referenced by an alias name when it is used in the JOINS.

      SELECT id
    FROM @foo f
    INNER JOIN #foo
    ON f.id = #foo.id

9.  Table variable use fewer resource than a temporary table because of there limited scope. 

10. we cannot change the definition of the table after the table is created. 

11. Microsoft also say transactions involving table variables require less logging. I assume they say this because the transactions are shorter.