Wednesday, June 10, 2015

SQL- Difference between Temp Table and Table Variable


What's the difference between a temp table and table variable in SQL Server?
In the real practice, it depends on the situations where we need to choose the current approach to complete the task by using of a table variable or a temp table. I usually use what's more convenient at that time and experiment a bit.

We can easily differentiate them based on the following contents –

Contents


Table Variable

Temporary Table
Storage Location
Table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.
Stored in the tempdb.

Logical Location
It acts like a DDL operation whenever we create a table variable and metadata of the table variable always stored in system catalog.

User-defined data types and XML collections must be in current database to use for table variables.
Temporary tables always stored in the tempdb.

User-defined data types and XML collections must be in tempdb to use for #temp tables.

Scope
Table variables have a limited scope. They are accessed within the same batch and scope in which they are declared.
Temp tables can be accessed within child batches (nested triggers, procedure, exec calls).


Lifetime
Table variable are created implicitly when a batch containing a DECLARE @.. TABLE statement. They are dropped implicitly at the end of the batch execution.

We cannot use the table variable before the DECLARE statement.
Temp tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends.

Transactions and Locking
Table variables don’t participate in transactions or locking. They are carried out as system transactions. 

This can be a useful feature, e.g. during a transaction certain activities can be logged in a table variable - if the transaction is rolled back then the table variable is available for inspection.
Temp tables operations would be carried out as part of the user transactions.

A NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified explicitly when working with temp tables as well.

Indexes
For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key.

Additionally table variables do not support INCLUDE columns, filtered indexes or partitioning.
Temporary tables comply to the same rules as permanent tables when it comes down to indexing. We need to create indexes on the temp table after fully populated the data.

Indexes on temporary tables do not come at a higher priority than indexes on permanent tables

Other Functional Differences
Queries that insert into (or otherwise modify) table variables cannot have a parallel plan.

Table variables can be used inside scalar or multi-statement table UDFs.

Table variables cannot have named constraints.

Table variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT.

Table variables do not support table hints such as WITH (FORCESCAN).

CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Queries that insert into (or otherwise modify) temp tables are not restricted in a parallel plan.

Temp tables cannot be used inside a function.

Temp tables have named constraints.

Temp tables can be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT.

Temp tables supports table hints such as WITH (FORCESCAN).

CHECK constraints on Temp tables are not considered by the optimizer for simplification, implied predicates or contradiction detection.

Statistics 
No statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.
Statistics is maintained on temp tables.

Also please keep in mind, that with CTE there is a third option towards the same goal.
To know more on the different kinds of the tables in SQL Servers at
  1. SQL – Wide Tables
  2. SQL - Table Variables
  3. SQL - Temp Table (Local & Global Temp Tables)
  4. SQL - Common Table Expression
  5. SQL - Difference between Table Variable and Common Type Expression
  6. SQL - Difference between Temp Table and CTE
  7. SQL - Difference between Temp Table and Table Variable

No comments:

Post a Comment

Popular Posts