Table Variable vs. Temp Table

 Table Variable vs. Temp Table Summary

Feature	Table Variable	Temp Table	Note
Table Name	Max 128 characters	Max 116 characters
Data Storage	In memory and TempDB	TempDB
Meta Data	In memory	TempDB	A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB.
Scope	Current batch	Current session	Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP.
Constraints	Allowed	Allowed	For table variables, since no DDL is allowed, constraints can not be created in separate DDL statements.
DDL	Not allowed	Allowed.	E.g. create Index on the temp table.
Concurrent	Supported	Supported	Constraints and Indexes with explicit name in a temp table cause duplicate name error.
Statistics	Not supported	Supported	Estimated row number in execution plan for table variable is always 1
Parallel execution plan	Supported only for select	Supported	Parallel query execution plans are not generated for queries that modify table variables.
Transaction and Locking	Not participated	Participated	Data in table variable is not affected if the transaction is rolled back
Cause Recompile	No	Yes	Temp Table creation causes SPs/batches to recompile
SELECT INTO <t>	Not supported	Supported
INSERT <t> EXEC	Not supported	Supported
Use	UDFs, Stored procedures, Triggers, Batches	Stored procedures, Triggers, Batches	Temp tables can't be used in UDFs.