Table Variable VS Temporary Table

Table Variable introduced in SQL Server 2000. Table variables can offer performance benefits and flexibility when compared to temporary tables, and you can let the server clean up after wards.

Table Variable
1. Scoped to the stored procedure, batch, or user-defined function, no need to clean up.
2. Can't use as an input or output parameter
3. Fewer resources, less locking and logging overhead
4. Constraints same
DECLARE @MyTable TABLE
(
  ProductID int UNIQUE,
  Price money CHECK(Price < 10.0)
)
5. You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table
6. Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure.Table variables can often avoid this recompilation hit. For more information on why stored procedures may recompile, look at Microsoft knowledge base article 243586 (INF: Troubleshooting Stored Procedure Recompilation).


When to use Temporary Table
1. Nested stored procedures which use the result set
2. you need transaction rollback support
3. Large result set even required indexes to improve query performance