Differences Between Temporary Table and Table Variable in SQL Server

Hello everyone,

Under this title, we will examine the differences between Temporary Table and Table Variable in SQL Server.

In some cases, such questions may come up in job interviews in SQL Server. It will always be useful for you to learn the following items.

1. While Temporary Table is created with # sign, Table Variable is also created with @ sign.
2. After the Temporary Table is created, we can perform DDL operations. We will be able to add a new column. Table Variable does not support DDL commands such as ALTER, CREATE, DROP.
3. Temporary Tables are kept in TempDB. Table Variable is stored as In – Memory.
4. It supports the Temporary Table Transaction structure. Table Variable does not support Transaction structure.
5. While user-defined functions (UDF) are not allowed in Temporary Tables, it is allowed to use user-defined functions (UDF) in Table Variables.
6. Temporary Tables support indexes, and indexes created with Primary Key and Unique Key are also supported. Indexes are not supported in Table Variables.
7. Temporary Tables are accessible in the environment in which they are defined. If it is desired to be accessible from all environments globally, ‘##’ should be used instead of ‘#’ when creating the Temporary Table. It supports both local and global access, depending on the type of creation. Table Variables can be accessed from the defined environment. Global access type is not available.
8. Tables of Table Variable type work much faster than tables of Temporary Table type.
9. Temporary Table; While it can be used in nested stored procedures, Table Variable; It cannot be used in nested stored procedures.
10. While you can create Temporary Table with SELECT INTO, you cannot create Table Variable with SELECT INTO.
11. If there is a Temporary Table in the Stored Procedure, the stored procedure is recompiled every time, while if there is a Table Variable, the recompile process does not occur.
12. Temporary Table works better than Table Variable in large tables.
13. If the number of rows is less than 100, Table Variable is usually used. If the number of rows is over 100, it is better to use Temporary Table.

Good luck to everyone in business and life.