Difference Temp Tables and Table Variables in SQL Server 2008

This article demonstrates the basic differences between table variables and temporary tables.
  • 2471

In 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 procedure too.
  • 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 Temporary Tables

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.

Example

1. Effect of transaction on Table variables

rollback-in-TableVariables.jpg

Records are not inserted. It is indicating that, the temporary table will bound to the transaction strategies.

2. Effect of transaction on Temporary Tables

rollback-in-TemporaryTables.jpg

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.


© 2020 DotNetHeaven. All rights reserved.