1. Temp table easy to create and backup data. Table variable involves the effort when we usually create the normal table.
2.Result of temp table can be used by multiple user. Table variable can be used by current user only.
3. Both are stored in tempdb.
4. Temp table supports all the DDL operations. We can alter table , drop table. Table Variable won't allow DDL operations.
5. Temp table can be used for current session or global so that multiple user session can utilize the results in table. Table variable can be used up to current program(stored procedure)
6.Table variable does not affected by transaction means we can not roll back table variable. While temp table operation can be rollback using transaction.
7. Function cannot use the temp table. But the function allows us to use the table variable. More over we cannot do the DML operation in the functions but using the table variable we can do that.
8. The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Where the table variable won't do like that.
9. As far as performance is concerned table variables are useful with small amounts of data (like only a few rows). Temporary table is useful when sifting through large amounts of data.
10. Table variables can not have Non-Clustered Indexes
11. You can not create constraints in table variables
12. You can not create default values on table variable columns
13. Statistics can not be created against table variables.
14. Temp table support explicit index, we can create index on temp table. Table variable does not support index, we can not create index on table variable.
2.Result of temp table can be used by multiple user. Table variable can be used by current user only.
3. Both are stored in tempdb.
4. Temp table supports all the DDL operations. We can alter table , drop table. Table Variable won't allow DDL operations.
5. Temp table can be used for current session or global so that multiple user session can utilize the results in table. Table variable can be used up to current program(stored procedure)
6.Table variable does not affected by transaction means we can not roll back table variable. While temp table operation can be rollback using transaction.
7. Function cannot use the temp table. But the function allows us to use the table variable. More over we cannot do the DML operation in the functions but using the table variable we can do that.
8. The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Where the table variable won't do like that.
9. As far as performance is concerned table variables are useful with small amounts of data (like only a few rows). Temporary table is useful when sifting through large amounts of data.
10. Table variables can not have Non-Clustered Indexes
11. You can not create constraints in table variables
12. You can not create default values on table variable columns
13. Statistics can not be created against table variables.
14. Temp table support explicit index, we can create index on temp table. Table variable does not support index, we can not create index on table variable.
No comments:
Post a Comment