Temporary Tables Vs Table Variables
Table Variables :
No Transaction Log Overhead
No Lock Overhead
No Rollback Maintenance
You can only have one Primary Key or UNIQUE Constraint.
You can have only one INDEX
Usually it doesn’t trigger Statement or Stored Procedure re-compilations
No DDL Statement can be executed on Table Variables once its created , which means no indexes or constraints can be added to the table variable later.
No Statistics Created for Table Variables which means they resolve as single row tables in execution plans. This is not an issue when the table actually contains only a small quantity of data , approximately less than 100 rows. It becomes major performance problem when the table variable contains more data.
Cardinality Estimates are skewed for table variables because it returns only one row as the number of rows in the table variable.
DECLARE @t1 TABLE (c1 INT )
INSERT INTO @t1 ( c1)
INSERT INTO @T1 (C1)
SELECT * FROM @T1
Returns two rows. ROLLBACK TRANSACTION doesn’t affect the table variables.
Temporary Tables :
You can rollback
You can create multiple Indexes
Triggers Statement or Stored Procedure re-compilations
Avoid Recompilations while using Temp Table: OPTION (KEEPFIXED PLAN )
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes have been made to a table by running UPDATE, DELETE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.
Forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.