My research confirms that global temp table does allow the plan to be shared among processes. However now we are forced to deal with the problem of ownership of data within the global temp table. I really don’t know if it’s worth the effort to add some sort of session id field. We’ll need to do more testing and subject it to our load test scenarios.
This is pretty much the trade-off between using global and local temp tables. If we use a global temp table but we want to restrict each session to its own data, then a session identifier is definitely necessary — @@spid would be a good candidate.
Note that if your goal is to restrict data access by session and to get plan re-use, you should be able to do that with a local temp table — if it’s created outside the body of the stored procedure, and if the cardinality of its data meets the restrictions discussed here, here, and here.
Either way, it is absolutely possible to build a procedure which uses temporary structures and supports plan re-use. The driving factor may well be one of concurrency — if the temporary structure is heavily used, it may be advantageous to use session-specific temporary structures to minimize resource contention. On the other hand, if there’s ever a need to look at this data across processes, then the global temp table (or a permanent table) is absolutely the way to go.
Thanks for your comment and your kind words, Gerard!
-wp (a/k/a “Professor”)