The below are the few differences between Temp table and table variable .
1.We can create and backup data of temporary table but its not possible in case of table variable.
2.Temporary table structure can be modified after its creation i.e it will support DDL Statements
where as table variable doesn't support it.
3.Temp tables will be stored in the temp db and creates network traffic when its size increases due to this performance issues will occur where as table variables will be stored in physical memory later size increases it will be moved to temp db.
4.Temp tables will participate in the explicit transactions defined by the user where as table variables doesn't participate in the explicit transactions defined by the user.
5.Temporary tables are not allowed in user defined functions where as table variables can be used in user defined functions.
6.Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.
where as
Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration i.e we cannot create non-clustered indexes on table variables.
7.Scope of the temp table is either local or global depending on declaration where as table variable scope is with in the batch or stored procedure in which it is declared.
8.Temp tables can be dropped itself or by the user manually where as table variables will be dropped automatically when batch execution or stored procedure execution completes.
1.We can create and backup data of temporary table but its not possible in case of table variable.
2.Temporary table structure can be modified after its creation i.e it will support DDL Statements
where as table variable doesn't support it.
3.Temp tables will be stored in the temp db and creates network traffic when its size increases due to this performance issues will occur where as table variables will be stored in physical memory later size increases it will be moved to temp db.
4.Temp tables will participate in the explicit transactions defined by the user where as table variables doesn't participate in the explicit transactions defined by the user.
5.Temporary tables are not allowed in user defined functions where as table variables can be used in user defined functions.
6.Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.
where as
Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration i.e we cannot create non-clustered indexes on table variables.
7.Scope of the temp table is either local or global depending on declaration where as table variable scope is with in the batch or stored procedure in which it is declared.
8.Temp tables can be dropped itself or by the user manually where as table variables will be dropped automatically when batch execution or stored procedure execution completes.
No comments:
Post a Comment