Below is a break down of the comparison between Temporary Tables and Table Variables.

Temporary Table Table Variable
Ability to create local and global YES NO
SELECT INTO YES NO
Uses Statistics YES NO
Use TRUNCATE YES NO
USE DELETE YES YES
Collation Based on User database NO YES
Use in Views NO NO
Use in Stored Procedures YES YES
Use in Triggers YES YES
Use in Transaction * YES NO
Allows ALTER TABLE command YES NO
Allow User Defined Data Types ** NO YES
Constraints work *** YES YES
IDENTITY **** YES YES
Add Indexes ***** YES YES

Use in Transaction *
Additional info and clear example:
http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

Allow User Defined Data Types **
It is possible to use User Data Types if you add the data type to TempDB when using the temporary table.

Constraints work ***
You can add Primary Keys, Check constraints Identities , But you cannot add Foreign keys (This applies to both Temporary Tables and Table Variables). You will get the message (Note message not Error):

IDENTITY ****
Both Temporary Tables and Table Variables allow the use of IDENTITIES but sadly you CANNOT manually edit Table Variables. You can Temporary Tables.

CREATE
TABLE #tmpidenitity
(
[id] INT IDENTITY(1, 1)
PRIMARY KEY ,
[name] VARCHAR(20)
)
SET
IDENTITY_INSERT #tmpidenitity ON
INSERT
#tmpidenitity
( id, name )
VALUES
( 1, -- id - int
'Timmy' -- name - varchar(20)
)
SET
IDENTITY_INSERT #tmpidenitity OFF

Add Indexes *****
You can add Indexes when you create either Temporary Tables or Table Variables. You can also Add Indexies to Temporary Tables. But NOT to Table Variables.

Additional Notes

You can find out how heavily your TempDB is using temporary objects based on the

Sys.dm_db_file_space_usage table </code>(Look at User_object_reserved)

There is another type of temporary object that you might want to use which is creating a table directly in TempDB. This will have the same characteristics as a table in a normal user database. The only difference will be that the table is deleted when the SQL Service is restarted.

TABLE VARIABLES

WITH (Recompile) can help with the lack of statistics on volatile tables but the results are still not the same as having STATISTICS. Still not as good as stats

Inserting or modifying data in a table variables forces execution plans to STOP use of parallelism

Advertisements