When and Why to use global temporary table over local temporary table in SQL Server

Question

I have a local temporary table like this:

create table #tbl
(
    employeeId int,
    employeeName nvarchar(50)
)

insert into #tbl
values (1, 'a'), (2, 'b'),
       (3, 'c'), (4, 'd')

select * from #tbl

Now I can access the local temporary table in that particular user session and in that particular query window compared to the global temporary table in which I can access the global temp table in any of the query window until the created query window gets closed, so far so good.

Now I am not able to understand the reason why one would require a global temporary table.

My questions are:

1. Is global temporary table instance specific? If yes then how can other user get hold of the newly created global temporary table

2. What is the reason SQL Server developed global temporary table over local temporary table?

Answer 1

Personally, I don't see a need for global temporary tables. My inclination is to store such data in tables along with other tables. But, clearly, they do fill a need.

The answer to your first question is that global temporary tables are instance specific -- if by instance you mean a SQL Server instance. They are available to all users and all connections on the server. Global temporary tables start with the prefix ##. All references to such a table, say ##table, are to the same table (within a server instance).

The answer to the second is that SQL Server supports both global and local temporary tables. Local temporary tables are more common. All temporary tables have the nice feature that they disappear when they are automatically deleted when the server restarts and under other circumstances.

Answer 2

The main problem with global temporary tables is that the names have to be unique across all sessions, for local temporary tables it is only within a session. As a result in practice local temp tables are usually more practical. Local temp tables are also more secure if they contain data that not all server users should be able to see.

I occasionally find global temporary tables useful when creating them in dynamic SQL and wanting them to survive when the dynamic SQL ends to be available in the outer scope.

This is only something I use when adhoc querying however. In production code this would be quite smelly for multiple reasons.

Again for adhoc querying being able to check on the progress of a process that writes to a global temp table from another SSMS connection can also occasionally be quite handy.

Answer 3

A little late to the party, but the local temporary table exists in the scope of the connection that created it. If you create your SQL as a string and then use sp_executesql to run it, the temporary table will be created in the scope of the stored procedure execution and will not exist when the stored procedure completes.

For example:

DECLARE @TestSQL nvarchar(200)

SET @TestSQL = 'SELECT TOP 10 * INTO #Department FROM dbo.st_DEPT_TABLE'

EXEC sp_executesql @TestSQL

SELECT *
FROM
   #Department

-- Returns "Invalid object name #Department"

If I do the same thing with a global temporary table, it returns the 10 records.