ScottPletcher (4/1/2013)

---

sharky (3/31/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?

Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging

2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...

Yes, really. SQL can take certain shortcuts when logging data for tempdb, "knowing" that a forward recovery will never be required.

Of course there are at least a dozen ways to use a non-tempdb table to do it, but all of them will intrinsically have at least slightly more overhead, and more if the non-tempdb db is in full recovery vs simple, as user dbs tend to be.