Table Variables vs Temp Tables
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|
|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|
|Add Indexes *****||YES||YES|
Use in Transaction *
Additional info and clear example:
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):
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.
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.
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