Temporary Tables - SQL Server

SQL Server Temporary tables are a special type of tables that are written to the TempDB database and act like regular tables.

There are four main types for the temporary tables;

• a Local Temporary table, which is named starting with a # symbol (e.g. #TempShipments), that can be referenced only by the current database session and discarded by its disconnection,
• a Global Temporary table, which is named starting with ## (e.g. ##TempShipments), that can be referenced by any process in the current database and discarded when the original database session that created that temp table disconnected or until the last statement that was referencing the temp table has stopped using it, as anyone who has access to the system TempDB database when that global temporary table is created will be able to use that table,

Both temporary tables and table variables are stored in the TempDB database, but there are many differences between them such as:

• Temporary tables are created using CREATE TABLE T-SQL statement, but table variables are created using DECLARE @name Table T-SQL statement.
• You can ALTER the temporary tables after creating it, but table variables don’t support any DDL statement like ALTER statement.
• Temporary tables can’t be used in User Defined Functions, but table variables can be.
• Temporary tables honor explicit transactions defined by the user, but table variables can’t participate in such transactions.
• Temporary tables support adding clustered and non-clustered indexes after the temporary table creation and implicitly by defining Primary key constraint or Unique Key constraint during the tables creation, but table variables support only adding such indexes implicitly by defining Primary key constraint or Unique key constraint during tables creation.
• Temporary tables can be dropped explicitly, but table variables can’t be dropped explicitly, taking into consideration that both types are dropped automatically when the session in which they are created is disconnected.
• Temporary tables can be local temporal tables at the level of the batch or stored procedure in which the table declared or global temporal tables where it can be called outside the batch or stored procedure scope, but table variables can be called only within the batch or stored procedure in which it is declared.

Reference: https://www.sqlshack.com/