As you all know tempdb is responsible of storing all the temporary objects created for a server. One of my co-workers asked me "what is the scope of permanent tables created in the tempdb database?". The answer is it can be accessed via tempdb from various connections just like objects are accessed in other databases. When the server is restarted, the tempdb is recreated and all the objects including permanent tables are dropped.
Let us analyse these with these examples
create the following table
create table test(i int)
You can now access this table from any connection pointing to the server
select * from tempdb..t
Ok. Next question is "Does this equal to global temporary table?". Yes. But there is a significant difference between a permanent table and global temporary tables created in the tempdb database. Global temporary table can be accessed from any database without qualifying the databasename whereas permanent table created in the tempdb should be accessed by qualifying the tempdb name when accessed via other database.
Create a global temporary table
create table ##test(i int)
You can access this from any database without qualifying the tempdb name as follows
select * from ##t
But the following will fail
select * from t
Because the object name is searched in the master database and not in tempdb database. But in the case of temporary tables, as long as SQL Server finds a table name prefixed with #, or ## it directly searches them in the tempdb database no matter which database the connection points to.
So you need to be aware of these points when using permanent table in the tempdb database