techniques for ensuring isolated temp table scope?

Question

techniques for ensuring isolated temp table scope?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
July 28, 2015 at 4:14 pm

#299264

Hi, what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

As long as you are using a single # before the temp table name, they will be isolated without any intervention on your part.

If you use two ## before the name, they become accessible to other processes.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 2

oh right. thanks for refreshing my memory on that. it's been a while.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Temp tables are local to the session which created them. SQL automatically adds numbers to the name of a temp table to ensure that it is unique, even if several sessions all create temp tables with the same names.

The only thing you need to ensure is that you don't create constraints on those temp tables with explicit names, as those can clash. If you're going to create constraints on temp tables, allow SQL to automatically name them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass