#TEMP tables vs ##GLOBALTEMP tables -- why would I ever use a ##GLOBALTEMP table?

Question

Hi All,

Just wondering, I'm trying to understand the advantage of ##GLOBAL temp tables vs #TEMP tables. Why would I ever use a ##GLOBAL table?

I can't think of any useful scenario where ##GLOBAL temp tables would solve a problem that can't be solved in some other more elegant way.

What am I missing here?

Thanks for any insights!

--Dan

Answer 1

You are not missing anything. Global temp tables have very few use cases.

I can think of two use situations where people may consider them.

The first is when you want export the result of a query with bcp that you spawn from xp_cmdshell. Because bcp runs in a separate process, you cannot use a local temp table. But then again, this arrangement is somewhat dubious even without the global temp table.

The second situation where i have seen people trying global temp tables is when they want to create a temp table inside dynamic SQL with SELECT INTO. They can't use a local temp table, as it goes out of scope when the dynamic SQL exits. But they have to work with a table they don't know the structure of. This arrangement is highly dubious.

In both cases, you get an issue if two users would run the same code in parallel, since they will clash on the global temp table.

I also briefly discuss global temp table as option the end of this section on my article: https://www.sommarskog.se/share_data.html#prockeyed. That will have to count on the advanced side, and it is nothing I've used in practice myself.

Answer 2

Thanks!

I was scratching my head when I tried to explain this to a colleague, and realized I couldn't define a useful scenario where I would ever use ##GLOBAL!

--Dan