Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Scope of permanent tables in tempdb database

Feb 14 2012 1:04AM by Madhivanan   

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

use tempdb
Go
create table test(i int)

You can now access this table from any connection pointing to the server

Use master
GO
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

Use master
GO
select * from ##t

But the following will fail

Use master
GO
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

Tags: t-sql, sql_server, tempdb, sqlserver, tsql,


Madhivanan
3 · 40% · 12899
8
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

5  Comments  

  • Also worth noting is that ANY ##temp-table or 'normal table in tempdb' is accessible by ANY other connection too. This requires some planning and taking care of when using this kind of objects in a multi-user environment as different processes might clash on the 'shared' resource. Another major difference between a 'normal table in tempdb' and a double-hash ##temp-table is that when the connection that created the ##temp-table is closed, the ##table will be cleaned up automagically. 'Normal tables in tempdb' exist until you explicitly remove them or, like you mentioned, restart the service.

    PS: I'm not entirely sure how security is handled for tables in tempdb, might have gotten stricter in more recent versions of MSSQL.

    PS: So far I've found very little use for this kind of objects.

    commented on Feb 14 2012 3:44AM
    Roby Van Hoye
    222 · 1% · 209
  • Roby Van Hoye , Thanks for adding some points about global temp table.

    commented on Feb 14 2012 4:06AM
    Madhivanan
    3 · 40% · 12899
  • Roby asks why someone would use a permanent tables in tempdb database.

    I can think of is log-shipping and data-mirroring concerns. I guess it can also be a way of controlling log file growth on non-system database.

    commented on Feb 17 2012 10:15AM
    Eric Bradford
    71 · 2% · 779
  • @Eric Bradford. Putting your data in tempdb would indeed be a way to 'work around the (self-imposed) limitations' of a normal database, but wouldn't it make more sense to use just another normal database that has no mirroring configured and/or is set to SIMPLE for it's recovery model ? The effect would be similar but at least you don't have to fear losing everything when the server goes down for some reason (planned or not planned), you would be able to make backups of your data (impossible on tempdb!) etc etc... and you would be able to set security on it, again something I doubt is possible on tempdb.

    commented on Feb 17 2012 10:36AM
    Roby Van Hoye
    222 · 1% · 209
  • It would make sense to use a normal database. I have never used a "permanent tables in tempdb" but I have seen a third-party application that does. They just don't want to maintain an additional database. After all, when deploying to another environment, they would have to maintain two databases not one (and you mentioned managing the security).

    Don't get me wrong, I will always find workaround to working with these type of tables.

    What is your handle on this Madhivanan? What is the most probable reason why people will use "permanent tables in tempdb".

    Thank you for the post!

    commented on Feb 17 2012 1:03PM
    Eric Bradford
    71 · 2% · 779

Your Comment


Sign Up or Login to post a comment.

"Scope of permanent tables in tempdb database" rated 5 out of 5 by 8 readers
Scope of permanent tables in tempdb database , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]