Difference Temp Tables and Table Variables in SQL Server 2008
This article demonstrates the basic differences between table variables and temporary tables.
SQL Server, Microsoft introduces table variables . We can use table variables to
store data that we used to store data in temp tables. Apart from this
following is difference between Table variable and Temporary table.
- Temporary tables are tables that exists
only to the session that created them. When that session time is completed,
table is automatically destroyed at the termination of the session while
Table Variable is used like a variable in which we store data that we store
in a temporary tables. In table variable, we declare a variable of type
table. This is a alternative of temporary tables in which we store records.
- A temp table can have indexes but the
table variable can only have a primary index.
- The table name can be of 128 characters in
table variable while up to 116 characters in temporary table.
- Scope of table variable is limited to
current batch while in temporary table it is all sessions and nested stored
- Table variables are defined with @ sign
preceding the table name while temporary table are defined with # sign
preceding the table name.
Effect of transaction on Table variables and
Table Variables are transaction neutral because
the transaction log are not recorded for the table variables. Table variables
are unaffected by transaction as their scope is very limited.
1. Effect of transaction on Table variables
Records are not inserted. It is indicating
that, the temporary table will bound to the transaction strategies.
2. Effect of transaction on Temporary Tables
Even we have provided rollback transaction.
Records are available in the table variable. This indicates that the transaction
log are not recorded for the table variables.