Sunday, May 8, 2016

Difference between temp Table(#Table) and Table variable(@Table) in sql server

 Table Variable(@Table):
  • Table variables can not have Non-Clustered Indexes
  • You can not create constraints in table variables
  • You can not create default values on table variable columns
  • Statistics can not be created against table variables
Similarities with temporary tables(#Table) include:

  • Instantiated in tempdb
  • Clustered indexes can be created on table variables and temporary tables
  • Both are logged in the transaction log
  • Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

No comments:

Post a Comment