6

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!

Andomar
  • 232,371
  • 49
  • 380
  • 404
Mark
  • 582
  • 3
  • 5
  • 20
  • 2
    Go for plan B, manage the lifetime yourself, there's no support for what you ask for in SQL Server alone. – Lasse V. Karlsen May 10 '09 at 19:29
  • I found this post that might be helpful: http://stackoverflow.com/questions/27835/does-ms-sql-support-in-memory-tables – Kekoa May 10 '09 at 19:30
  • kekoav, my understanding is that Table Variables work much the same as temp tables (in terms of persistence). So my assumption is that the table variable would just disappear once I stopped accessing it (as does the temp table). – Mark May 10 '09 at 20:09
  • Mark the post that Kekoa shared the posting http://stackoverflow.com/a/27849/77198 notes that a table with a double hash prefix eg ##TempTableName will persist in the tempdb. Even after the object referencing or creating it goes out of scope. The way to remove it is to explicitly drop the table, or restart the database instance – SQLBobScot Dec 07 '13 at 11:39
  • 1
    @BobF for global temp table ## they are persistent only in the same sql instance if the table is left idle and the sql instance which created is destroyed then these tables are also removed. Please correct me if i am wrong – hardyz009 May 13 '15 at 18:45

5 Answers5

9

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.

LeoPasta
  • 300
  • 1
  • 4
  • 4
    Interesting, so if you create it using TempDB as the database instead of prepending the # or ##, then it becomes effectively perminent until reboot (when tempdb gets dumped anyway)? That's exactly what I'm after - I can handle recreating it if it doesn't exist. – Mark May 11 '09 at 18:59
  • 3
    @Mark, can you please confirm that whatever LeoPasta was saying is right or not? – peakit Oct 20 '09 at 18:16
  • @Mark was this ever confirmed? – Code Novice Apr 16 '20 at 16:02
4

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

codeulike
  • 22,514
  • 29
  • 120
  • 167
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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    That's basically what I outlined above - the only difference is that you propose doing it in the same DB. I certainly could put it in the same DB, but since it's not useful longterm data I don't want it in backups for the DB (and don't want to start complicating things with exclusions). – Mark May 10 '09 at 20:38
  • @Mark Though I do think that the “different database and set it to Recovery Model: Simple” has merits because it allows you to use a multi-tenanted SQL Server without worrying about name conflicts in `tempdb.dbo.`. – binki Nov 28 '18 at 18:03
2

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.

Mark Brittingham
  • 28,545
  • 12
  • 80
  • 110
  • Yeah, a bit of a dichotomy - I just want a larger window for my 'temp' data. :) You actually don't even need to run a query in the background, as long as you have a connection open that ran a query against it once, it will remain. As a hack, that's what I'm currently doing, but if the connection gets dropped for some reason, then it needs to rebuild everything, so it's not as clean a solution as my plan B. – Mark May 10 '09 at 20:07
  • This would be a quite easy thing do to. If the temp table does not hold too many rows a select count(*) from foo would do it. – George Silva Oct 21 '09 at 10:55
1

After 20 years of experience dealing with all major RDBMS in existence, I can only suggest a couple of things for your consideration:

  1. Note the oxymoronic concepts: "persistent" and "temp" are complete opposites. Choose one, and one only.

  2. You're not doing your database any favors writing data to the temp DB for a manual, semi-permanent, user-driven basis. Normal tablespaces (i.e. user) are already there for that purpose. The temp DB is for temporary things.

  3. If you already know that such a table will be permanently used ("daily basis" IS permanent), then create it as a normal table on a user database/schema.

  4. Every time that you delete and recreate the very same table you're fragmenting your whole database. And have the perverse bonus of never giving a chance for the DB engine optimizer to assist you in any sort of crude optimization. Instead, try truncating it. Your rollback segments will thank you for that small relief and disk space will probably still be allocated for when you repopulate it again the next day. You can force that desired behavior by specifying a separate tablespace and datafile for that table alone.

  5. Finally, and utterly more important: Stop mortifying you and your DB engine for a measly 1 GB of data. You're wasting CPU, I/O cycles, adding latency, fragmentation, and so on for the sake of saving literally 0.02 cents of hardware real state. Talk about dropping to the floor in a tuxedo to pick up a brown cent.

alejandrob
  • 603
  • 8
  • 6