What table prefix "##" in SQL Server 2005 really mean?

[katrina11]

I am struggling with somebody code trying to adopt it to my purposes....
It creates tables but I am unable to find then in the DW.


select *
into ##XX
from......

Does it mean that these are just temporary tables and that I would not be able to find them after logoff?

Thank you!

Katrin

 

[markros]

## means global temp table, so temp table that can be accessed from other sessions by other users.

PluralSight Learning Library

 

[katrina11]

Thank you markros.

Does it mean:

1. That it might be seen just through Select query?
2. That other users can accidentally delete it?


It seems to me that I accidentally deleted one table.If I would have restored it, would be user who created it, still be able to access that temp table?

 

[markros]

It means that other users can alter data in that temp table and can access it. It will be automatically closed when all sessions that reference that table close.

I also just did a quick experiment

select * into ##Clients from Clients
in one SSMS query window

and then executed

drop table ##Clients in another window.

Then back to original and tried to query this table and it didn't exist.

So, it is possible by other users to drop the global table created in another session.

PluralSight Learning Library

 

[gmmastros]

If you don't want the behavior that Markros describes, then you should use a single # in front of the table name. Tables with a single # sign are not visible to other users and cannot be affected by anything another user does.

To be honest, in all my (considerable) years working with SQL Server, I have never found a good use for global temp tables.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom