What is the difference between a temp table and a variable table in SQL Server?
What is the difference between a temp table and a variable table in SQL Server?
Ask by Revati S Misra
Updated 02 Jul 2023
Sure. A temporary table and a variable table are both types of in-memory tables in SQL Server. However, they have some key differences.
Temporary tables are persistent, which means that they remain in memory until the transaction that created them is committed or rolled back. Variable tables are non-persistent, which means that they are destroyed when the current batch or stored procedure is completed.
Temporary tables can be indexed, while variable tables cannot. Temporary tables can also be used in joins, while variable tables cannot.
Here is a table that summarizes the key differences between temporary tables and variable tables:
Which one you should use will depend on your specific needs. If you need to create a temporary table that can be used by multiple users, or if you need to create a temporary table that can be indexed or used in joins, then you should use a temporary table. If you only need to create a temporary table that is available to the current user, and if you do not need to index it or use it in joins, then you should use a variable table.