Difference between Table Variable VS Local Temp Table Vs Global Temp Tables in SQL

Difference between Table Variable , Local Temp Table and Global Temp Tables in SQL

In my Earlier blog I explained you what is Table variable ,Local Temp Table Global Temp Table

and how to create these table in this blog i will tell you the difference between them and at what cnditons we can prefer table variable ,local or GLobal table.

1-Table variable is Used with Small dataset or where there are less no. of rows as it will perform fast with small data if the data is with in the page size i.e. 8 kb if data exceeds this size it's better to go with Temp Tables as managing Data in more than one page you need indexes to fetch data fast which is the limitation of table variable .

Note: You can have automatic index on table variable like creating primary key or unique key by default primary keys create clustered indexes so you can have automatic index on table variables but cannot have foreign key.

2-Similarly Use Temp Table with large dataset as you can have constraint's index on them using index on large no. of rows makes the data retrieval fast best used with joins.

3-Use Global Temp Tables where you want to shared Temporariry Data through out the connections mean any session  can usethe Global temp Table.

4-The Scope of Table Variable is within batch ,UDF and Stored Proc ,Local temp table scope connection created temp table and nested Stored Proc and Scope of Global temp table is in all Sessions.

5-You can use Select * into temp table but with Table variable it will not work

6-Similarly insert using Exec procedure not allowed in table variable in sql 2000 now its there

whereas in temp table you can use insert /exec.

7-You cannot Set Identity field i.e. Auto increament field in table variable whereas in Temp Tables you can.

8-Truncate is not allowed with table variable whereas you can truncate a temp table.

9-With Table Variable Data Cannot be Roll Back whereas Data can be Roll Back with Temp Tables.

10-Stored Procedures cannot recompiled with table variable but with temp tables stored procedures can lead to recompilation.

11-You must be aware of the Fact that tempDB uses SQL server Collation and if your database have different collation then while comparing the data or joining the data from temp tables or table variables can cause problem.

If you want to know about Local temp table Global temp table and Table Variables read my previous Blog.