2

Possible Duplicate:
Local and Global temporary table in SQL SERVER

I would like to know if anyone out there can give me a better explanation on the workings of #tables in SQL.

In one of our stored procedures, we create a temp table (#table), wherein data is stored by the currently active stored procedure. Lets call this Sproc1 for arguments sake.

Sproc1 is called by sproc2. After the call, sproc2 collects the data from the #table and then drops the #table, since it will no longer be needed.

That all works well in theory, but what if two or twenty people were to call / execute sproc2 at the exact same time?

So, I'd like to know whether SQL will create a new instance (linked by connection) of the #table for every caller using sproc2 or will there be a conflict?

Community
  • 1
  • 1
JAT
  • 317
  • 1
  • 2
  • 12

2 Answers2

2

You should be fine. #table temp tables are only visible to calls within the same SQL session.

A nice explanation can be found here.

tobias86
  • 4,979
  • 1
  • 21
  • 30
1

When you create a table with one hash tag (like in your example) - only connection that created it will have access to it and will automatically be deleted when the connection is closed.

If you need to ensure that all connections can see it you can use global temp table (##table) but they will also be deleted when the connection is closed so make sure you don't assume it exists in your procedures.

Fedor Hajdu
  • 4,657
  • 3
  • 32
  • 50