Persistent temp tables in SQL?

Question

Is it possible to have a 'persistent' temp table in MS-SQL? What I mean is that I currently have a background task which generates a global temp table, which is used by a variety of other tasks (which is why I made it global). Unfortunately if the table becomes unused, it gets deleted by SQL automatically - this is gracefully handled by my system, since it just queues it up to be rebuilt again, but ideally I would like it just to be built once a day. So, ideally I could just set something like set some timeout parameter, like "If nothing touches this for 1 hour, then delete".

I really don't want it in my existing DB because it will cause loads more headaches related to managing the DB (fragmentation, log growth, etc), since it's effectively rollup data, only useful for a 24 hour period, and takes up more than one gigabyte of HD space.

Worst case my plan is to create another DB on the same drive as tempdb, call it something like PseudoTempDB, and just handle the dropping myself.

Any insights would be greatly appreciated!

Answer 1

I have to admit to doing a double-take on this question: "persistent" and "temp" don't usually go together! How about a little out-of-the-box thinking? Perhaps your background task could periodically run a trivial query to keep SQL from marking the table as unused. That way, you'd take pretty direct control over creation and tear down.

Answer 2

I would go with your plan B, "create another DB on the same drive as tempdb, call it something like PseudoTempDB, and just handle the dropping myself."

Answer 3

How about creating a permanent table? Say, MyTable. Once every 24 hours, refresh the data like this:

1. Create a new table MyTableNew and populate it
2. Within a transaction, drop MyTable, and use rename_object to rename MyTableNew to MyTable

This way, you're recreating the table every day.

If you're worried about log files, store the table in a different database and set it to Recovery Model: Simple.

Answer 4

If you create a table as tempdb.dbo.TempTable, it won't get dropped until:

a - SQL Server is restarted

b - You explicitly drop it

If you would like to have it always available, you could create that table in model, so that it will get copied to tempdb during the restart (but it will also be created on any new database you create afterwards, so you would have to delete manually) or use a startup stored procedure to have it created. There would be no way of persisting the data through restarts though.