Non Temp Temp Tables

Question

Non Temp Temp Tables

Steve Jones - SSC Editor

SSC Guru

Points: 736068
More actions
July 19, 2014 at 3:45 pm

#100891

Comments posted to this topic are about the item Non Temp Temp Tables
July 21, 2014 at 2:28 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Easy on, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Hardy21

SSCrazy Eights

Points: 9708

July 21, 2014 at 12:15 am

#1730889

Easy one, thanks 🙂

Thanks

Sreepathi1987 Default port Points: 1425 More actions · Answer 3

Easy One. 🙂

But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Sreepathi1987 (7/21/2014)
Easy One. 🙂
But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?

The answer is Yes.

Nice and easy question, but still tricky if you have never created a table in tempdb before. So Thanks for sharing Steve.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Sreepathi1987 (7/21/2014)
Easy One. 🙂
But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?

Yes, which makes tempdb useful as an experimental sandbox. If you forget to clear up your tables, SQL Server will do it for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 6

sharath.chalamgari

SSCertifiable

Points: 5680

July 21, 2014 at 3:46 am

#1730942

easy one on monday morning 🙂

Jamsheer Ten Centuries Points: 1136 More actions · Answer 7

ChrisM@Work (7/21/2014)
Sreepathi1987 (7/21/2014)
Easy One. 🙂
But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?
Yes, which makes tempdb useful as an experimental sandbox. If you forget to clear up your tables, SQL Server will do it for you.

Now the question becomes useful for me. Thanks..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Sreepathi1987 (7/21/2014)
Easy One. 🙂
But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?

Mostly yes.

If there is corruption in tempdb, that corruption will persist after a service restart. At that point, you have to manually drop tempdb files.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

Nice, and not so easy for me that I didn't have to think, but still easy enough for a Monday. And brought up an interesting point or two. Thanks Steve.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

Creating permanent tables in tempdb this way is tricky. They will behave "sort of" like permanent tables (they will not be removed when the batch ends or when you leave the scope), but they will not really be permanent in that they will be loist when the box restarts. (Or fails over in the case of a box that is part of a high availability setup).

ChrisM@Work (7/21/2014)
Sreepathi1987 (7/21/2014)
Easy One. 🙂
But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?
Yes, which makes tempdb useful as an experimental sandbox. If you forget to clear up your tables, SQL Server will do it for you.

Beware of putting too much trust in this sandbox. Especially for tuning, tempdb is not a good place to try. Various features of the optimzed are not used in tempdb! (I know because I have been bitten by this often enough that I now, finally, remember. Most of the time).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Miles Neale SSChampion Points: 13147 More actions · Answer 11

So you return from being gone and you give us a real easy one to lull us to sleep, and tomorow, WHAM! We must watch out for this.

🙂

M...

Not all gray hairs are Dinosaurs!

Revenant SSC-Forever Points: 42467 More actions · Answer 12

Revenant

SSC-Forever

Points: 42467

July 21, 2014 at 3:38 pm

#1731236

Thanks for an easy one, Steve!

Sean of the Lynchmob Ten Centuries Points: 1038 More actions · Answer 13

Welcome back, Steve. Thank you for treating us gently on your return! 😀

Sean