SQLRNNR (3/29/2013)

---

Lynn Pettis (3/29/2013)

---

SQLRNNR (3/29/2013)

---

Lynn Pettis (3/29/2013)

---

ScottPletcher (3/29/2013)

---

The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.

Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.

I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.

You can't backup tempdb, you get the following error:

Msg 3147, Level 16, State 3, Line 1

Backup and restore operations are not allowed on database tempdb.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

I just have a real problem with using tempdb in the manner suggested. And I have a problem with SQL Server "knowing" that since it doesn't have to recover data that there are less resources used when writing data to tables in tempdb. I mean, really?

A bit pedantic but what you wrote and what Scott wrote are different. He said less overhead writing data there vs. what you said "when writing data to tables in tempdb."

Different meanings. When writing data (at that moment the write occurs) there is no difference in resources. But the way Scott wrote implies an overall perspecitve - imho. This means not just at the time of write but the whole kit and kaboodle. And since you can't backup tempdb - it is less resources.

Creating a permanent table in tempdb for these things is little different than using a temporary table. It is understood that it can be thrown away. The problem comes from not disposing of the table in tempdb when the process is done. And if the server restarts, then you have to create additional steps to ensure the recreation of the table the next time the process is to be run.

Why put it in tempdb - one good reason is you don't want those tables involved in backup or recovery processes. They are throw-away tables. On the other hand - why not create a database that holds these tables and not worry about backing that up, leave it in simple and build the table recreate processes as appropriate.