How to preserve global temporary table data

Question

How to preserve global temporary table data

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The only overhead not involved is the recovery process when SQL Server starts up. It doesn't have to run the redo/undo process on tempdb since it is recreated on startup. But then it probably still runs just doesn't have a lot to do since the t-log is empty.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Which goes to my concern in this case. If the data needs to be around for a period of time, 120 minutes apparently, then keeping this data in a permanent table in tempdb isn't necessarily a good idea. If there is a server restart for any reason, you lose that data.

I like the idea of another database if you can't use the main database the process is using to store a permanent temporary table.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

I quite like the idea of having a permanent tables (not in tempdb) with a process to remove them, but not automatically...

I would have a system whereby you write an entry to another permanent table that records the "temporary" table name and it's expiration date/time, then have a process that cleans up expired tables. (You could even included a trigger on your "temporary" tables to perform a sliding expiration if you wanted)

You wold have to put some thought into making this process secure enough that the cleanup process couldn't be abused and tricked into removing anything except these "temporary" tables though!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Another problem with doing it using a permanent table, whether in TempDB or some other database, is the chance of a naming conflict. You could go through some gyrations to resolve such conflicts by including a DATETIME stamp in the name of the table, but I think it might be easier just to populate a single permanent table and delete only those rows that are no longer necessary using a "BatchID" or other form of row identification.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sharky Default port Points: 1494 More actions · Answer 6

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"...

ScottPletcher SSC Guru Points: 100947 More actions · Answer 7

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.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 8

Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.

DiverKas SSCrazy Points: 2049 More actions · Answer 9

rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.

Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.

Dont make this harder than it is.

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 10

DiverKas (4/2/2013)
rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.
Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.
Dont make this harder than it is.

Actually all the people who are architect of this database are from Oracle background (me as well). Without knowing the limitation of sql server temp table the database design was made and approved by client. Now if we want to create any new table that needs to be passed through lot of process which we want to prevent and that is the reason for so much complexity.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

rajarshi_ghosh_05 (4/2/2013)
DiverKas (4/2/2013)
rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.
Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.
Dont make this harder than it is.
Actually all the people who are architect of this database are from Oracle background (me as well). Without knowing the limitation of sql server temp table the database design was made and approved by client. Now if we want to create any new table that needs to be passed through lot of process which we want to prevent and that is the reason for so much complexity.

If it is to support a process inherent in the design of the database, then it should be included in the database.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 13

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?!

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

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.

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 15

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.

No. We dont bother about the temp table data not if anything happens like network failure or server restart or user system restart! We dont have to preserve that data. Only thing if nothing happens then we need to keep the data till 120 min; because user can download the data in excel at anytime between this.