August 27, 2009 at 5:26 am
Hi Guys,
I would like to know whether it’s a good idea to create tables on the tempdb.
Are there any considerations or any thoughts on this matter. I am thinking of creating some tables on the tempdb as a temporary working area for calculations etc.
August 27, 2009 at 6:28 am
Why not? You just have to know they won't be there forever.
Anyway, creating tables in tempdb is the exact same thing as creating temp tables or global temp tables. I prefer going that way. Temp tables are deleted when the connection is closed, so you don't have to take care of the cleanup.
Global temp tables are not different from tables created directly in tempdb, maybe this is somehow closer to what you want to do.
Just my two cents
Gianluca
-- Gianluca Sartori
August 27, 2009 at 8:27 am
From BOL about Global Temp Tables:
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
So creating a regular table in tempdb would be somewhat different than a global temp table because the regular table would exist until explicitly dropped or sql server is restarted.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2009 at 8:31 am
I've seen plenty of instances of people creating tables in tempdb. Nothing wrong with it, so long as you manage them correctly, and do things like check for existence before creating (to avoid errors about objects that already exist).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2009 at 11:52 am
I think it's a bad idea because what will happen if two instances of your code are executed at the same time?
--Jeff Moden
August 27, 2009 at 1:47 pm
This is really a good point, Jeff.
And this is the main reason why I like temp tables: it's all mine and I can do whatever I want with it, sure nobody else will touch it.
-- Gianluca Sartori
August 27, 2009 at 1:49 pm
Jeff Moden (8/27/2009)
I think it's a bad idea because what will happen if two instances of your code are executed at the same time?
That's why I mentioned checking for existence before creating.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2009 at 1:57 pm
If 2 instances are trying to read data off the table, I dont see anything that will go wrong here. please explain
August 27, 2009 at 8:08 pm
GSquared (8/27/2009)
Jeff Moden (8/27/2009)
I think it's a bad idea because what will happen if two instances of your code are executed at the same time?That's why I mentioned checking for existence before creating.
Understood and I agree. The thing I'm having a hard time with is why anyone would have the need to do this. If it's an attempt to get around the scope that temp tables have, then something really needs to be done to the code to make it follow the correct scope. If two sessions end up running at the same time, the existence check you mentioned will work just fine but how will each session identify it's own rows? The use of a "permanent" or "real" table in TempDB just smacks of a bigger problem that needs to be addressed. Sure, there may be a real need for this but it would likely be similar to that of a Cursor... 99.9% of the time, there's a better way.
--Jeff Moden
August 27, 2009 at 8:12 pm
Dean Jones (8/27/2009)
If 2 instances are trying to read data off the table, I dont see anything that will go wrong here. please explain
See the post just above this one, Dean. Perhaps if you explained why you might need to do such a thing, I could answer in a less generic fashion. Normally if you need a "real" table somewhere, it would go into a "real" database. Without more information on what you're trying to do, the idea of building "real" tables in TempDB smacks of a larger problem that may need to be addressed.
--Jeff Moden
August 27, 2009 at 8:17 pm
Gianluca Sartori (8/27/2009)
This is really a good point, Jeff.And this is the main reason why I like temp tables: it's all mine and I can do whatever I want with it, sure nobody else will touch it.
As Gus pointed out, a check for existance would certainly solve the problem of two sessions trying to create the same table, but I just don't understand why someone would need to do this. Like I say in the other posts above, there may be a larger problem which needs to be addressed if you need to do this. Can you do it? Sure? Should you do it? Not so sure about that but I don't know enough about Dean's particular problem to say for sure. I can say that it's normally a bad idea for the reasons stated.
--Jeff Moden
August 28, 2009 at 7:36 am
Jeff Moden (8/27/2009)
GSquared (8/27/2009)
Jeff Moden (8/27/2009)
I think it's a bad idea because what will happen if two instances of your code are executed at the same time?That's why I mentioned checking for existence before creating.
Understood and I agree. The thing I'm having a hard time with is why anyone would have the need to do this. If it's an attempt to get around the scope that temp tables have, then something really needs to be done to the code to make it follow the correct scope. If two sessions end up running at the same time, the existence check you mentioned will work just fine but how will each session identify it's own rows? The use of a "permanent" or "real" table in TempDB just smacks of a bigger problem that needs to be addressed. Sure, there may be a real need for this but it would likely be similar to that of a Cursor... 99.9% of the time, there's a better way.
Yeah, I haven't ever used this technique, pretty much because I've never been able to figure out a valid reason to.
The only reason I can think of for using this technique would be if you have a database that needs to be in Full recovery, but you have one or a couple tables that will be better off in Simple recovery. But it seems to me it would be better to have the tables in a second user database that's in Simple, instead of in tempdb, since a second user database wouldn't be subject to having to rebuild the tables whenever you have to restart the SQL Server service (like, pretty much every patch, if not more often than that).
On the other hand, I've seen people do it, and it's not an invalid practice. Done correctly, it won't break anything or kill performance or anything like that. It just makes things slightly more complicated. So, I can't say, "Don't do it!" I can just say, "Do it right, or don't do it." As always, I defer my strong objections to the things that really matter, like triangular joins and nested global cursors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2009 at 8:52 am
Heh... spot on Gus. I pretty much agree with all of that. I'd still like to know why folks would want to use TempDB instead of a "utility" or permanent "working" db.
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply