Difference Between Local and Global Temporary Tables in SQL Server

When you create a temporary table in SQL Server, you have the option of making it a local or global temporary table.

Here’s a quick outline of the main differences between local temporary tables and global temporary tables.

Naming

Local temporary tables’ names start with a single number sign (#). For example, #MyTable.
Local temporary tables also get a system generated numeric suffix appended to the name. This is automatically generated by SQL Server. This allows multiple sessions to create local temporary tables with the same name without causing naming conflicts. However, users can still query the table without having to know the suffix.
Global temporary tables’ names start with a double number sign (##). For example, ##MyTable.
Global temporary tables are not appended with a suffix like with local temporary tables.

Local temporary tables are automatically dropped at the end of the current session. A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single T-SQL statement. Therefore, a global temporary table is dropped at the completion of the last T-SQL statement that was actively referencing the table when the creating session ended.