Lynn Pettis (4/4/2013)

---

ScottPletcher (4/4/2013)

---

Lynn Pettis (4/4/2013)

---

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.

First of all, I am still going to express my concern of SQL Server "Knowing" that certain things can or can't be done. This implies intelligence in the code itself, not conscious decisions made by developers when designing and implementing the code. Implementing enhancements and optimizations is not the same thing as a piece of software "knowing" something.

On to some interesting things. I have already been able to do a little playing with permanent tables in both a user database and in tempdb. I have found the that there are minor differences in transaction logging between the two. With the simplistic testing I have been able to do so far, I really don't think it is enough to justify using tempdb in the manor suggested by the OP or Scott.

I do firmly believe that if the process being developed is meant to support a business process inherent in the application that the database tables needed to support that process belong in the database itself. It is a waste of time and effort to attempt to bypass change control just because it is a difficult process. If the customer/client wants a specific behavior in the application and it requires changes to the database (the addition of tables in this case), then show them this and explain it. If they want that behavior they should then approve the changes necessary to support it. The other choice is to go without the behavior.

C'mon, that's why I put "knowing" in quotes: to clearly indicate that it's not intended to be taken literally ( even by the perpetually pedantic ;-)).

Hmm, by that latter reasoning, temp tables themselves should not be used then? And all work space used in db processing should be in that db?!

Really, you are going there? And you think I am being perpetually pedantic?

No, I am not advocating NOT using temp tables. I am advocating that temp tables have no business being used to hold temporal data over a period of time to support a business process, especially when that business process hasn't been fully explained. No one has answered the question I put forth earlier. Does the loss of data in that temporary table matter if there is restart fo SQL Server during the time frame in which the data is to be persisted (in this case 120 minutes). If data is written at 9:00 AM and the server restarts at 9:10 AM is there a problem with the loss of data written 10 minutes earlier, or 100 minutes earlier?

Temp tables, table variables, all have a purpose and a time to use them. Use them correctly.