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?