There have been many discussions on whether a temp table is better to use or a table variable. It depends on what you would like to do. They both have their pros, cons and limitations.
Table Variable Limitations
- Table variable changes do not get rolled back during a transaction ROLLBACK whereas temp table changes will.
- Table variables cannot have Non-Clustered Indexes
- You cannot create constraints in table variables
- You cannot create default values on table variable columns
- Statistics cannot be created against table variables
- Table variables cannot have a parallel plan, whereas temp tables are not restricted in this manner
- Table variables cannot be selected into, altered, truncated or be the target of the DBCC commands such as DBCC CHECKDENT or of SET IDENTITY INSERT
- Query hints are not supported
- CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Similarities between Temp Tables and Table Variables
- Clustered indexes can be created on both
- Both are logged in the transaction log
- Instantiated in the tempdb
- You can perform DML queries against the tables
Table variables behave more as if they were part of the current database. The table variable column collations if not specified explicitly will be that of the current database. Temp tables will use the default collation of tempdb. User-defined data types and XML collections must be in tempdb in order for you to use it in temp tables, however, table variables can use them from the current database.
- In a contained database, temporary table data is collated in the collation of the contained database. All metadata associated with the temporary tables will be in the catalog collation.
- Named constraints may not be used in temporary tables.
- Temporary tables may not refer to user-defined functions, user-defined types or XML collections.
Visibility to different scopes
Table variables are limited in such that they can only be accessed in the current batch and scope in which they are declared. Temp tables are accessible within child batches (nested triggers, procedures, exec calls). Temp tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends.
Table variables are created implicitly when containing a DECLARE @MyTableVariable TABLE statement above any user code is executed, and they are dropped implicitly at the end. Temp tables are created explicitly when the CREATE TABLE #MyTemp statement is encountered and can be dropped explicitly with the DROP TABLE or will be dropped when the session ends. If created in a child batch with @@NESTLECT > 0 it will be dropped implicitly.
Within stored routines both types can be cached rather than repeatedly creating and dropping new tables. There are restrictions on when this can occur, it is possible to violate temp tables, the restrictions on table variables prevent any violations. The maintenance overhead on cached temp tables is slightly greater than for table variables. For more info on this, take a look here.
Temp table operations are carried out as part of the user transaction itself, whereas table variable operations are carried out as system transactions, independent of any outer user transaction. A ROLLBACK command will therefore affect a temp table but leave a table variable untouched.
Both generate log records in the tempdb transaction log. A common misconception is that this is not the case for table variables. The quantity of logging is very similar, a very important difference is that log records related to temp tables cannot be cleared out until any containing user transaction finishes. A long running transaction that at some point writes to temp table will prevent log truncation in the tempdb whereas the autonomous transaction spawned for table variables do not.
For versions prior to SQL Server 2016 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. This means that only unique indexes are supported. Table variables do not support included columns, filtered indexes (until 2016) or partitioning, whereas temp tables do.
In SQL Server 2014 non unique indexes can be declared inline in the table variable definition. Now in SQL Server 2016 filtered indexes can be applied to table variables.
Hope the above gives you a bit more clarity on the benefits of each one in helping you decide whether to use a temp table or table variable in your scenarios.