Difference between #table and ##table in SQL Server

SQL Server uses #table for Temporary table and ##Table for the Global Temporary Table. Let's understand the difference between these tables.

Use #table to create a temporary table or Temp Table

• Temp table name always starts with the # symbol.

• The temp table scope is within the session. You cannot use it in another session.

• It is dropped automatically once the session terminates.

• You can create a temp table with a similar name in multiple sessions.

• You can access the temp table within the declared stored procedure.

• Temporary tables are virtually created in the tempdb database. They act like regular tables, but they allow developers to change their underlying structure at any time, allowing for the creation of more complex queries.

Temporary table(#table) example:

CREATE TABLE #Temp_Demo
(
       [ID] [int] NOT NULL,
       [Name] varchar(10)
)
GO
SELECT * FROM #temp_demo

You can drop the temp table explicitly using the DROP TABLE statement.

DROP TABLE #temp_demo

As shown below, you cannot use the temp table defined in another session.

Use ##table to create a global temporary table

• The global temporary table name starts with ##.

• Once you define a global temp table, it can be accessed across the sessions.

• You can use the global temporary table in multiple stored procedures.

• The global temporary table is dropped automatically when the last session is closed. The global temporary table will be available if a single session is active.

CREATE TABLE ##myglobaltable
(
       id int,
       [name] varchar(10)
)

As shown below, we can use the global temporary table in another transaction.