What is the difference between #table and ##table

If i create a ##table using my stored procedure, I can do the select * from ##table

but when i created the #table using the stored procedure and tried to do the select * from #table
I am getting the error message invalid object #table
that means it is going out of scope.
But if i create the ##table, will that table going to remain in the database even until i drop the ##table or it gets dropped when i close the database connection.
Thank you very much.
9/24/2004 5:09:47 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 0
Get it on Google Play
Get it on Apple App Store

There are two types of temporary tables: 

Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

Darrell Norton, MVP
Darrell Norton's Blog

Please mark this post as answered if it helped you!
9/24/2004 8:35:13 PM