SQL 中的持久临时表？
MS-SQL 中是否可以有一个“持久”临时表？ 我的意思是，我当前有一个后台任务，它生成一个全局临时表，该表由各种其他任务使用（这就是我将其设为全局的原因）。 不幸的是，如果该表变得未使用，它会被 SQL 自动删除 - 这由我的系统妥善处理，因为它只是将其排队以再次重建，但理想情况下我希望它每天只构建一次。 因此，理想情况下我可以设置一些超时参数，例如“如果 1 小时内没有任何内容触及此内容，则删除”。
我真的不希望它出现在我现有的数据库中，因为它会导致与管理数据库相关的更多麻烦（碎片、日志增长等），因为它实际上是汇总数据，仅在 24 小时内有用，并且占用更多超过 1 GB 的硬盘空间。
最糟糕的情况是，我的计划是在与 tempdb 相同的驱动器上创建另一个数据库，将其称为 PseudoTempDB 之类的名称，然后自己处理删除操作。
任何见解将不胜感激！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
经过 20 年处理现有所有主要 RDBMS 的经验，我只能建议您考虑以下几点：
请注意矛盾的概念：“持久”和“临时”是完全相反的。 选择一项，并且只选择一项。
您不会为数据库提供任何支持，以手动、半永久、用户驱动的方式将数据写入临时数据库。 普通表空间（即用户）已经用于此目的。 临时数据库用于临时的事情。
如果您已经知道这样的表将被永久使用（“每日”是永久的），则将其创建为用户数据库/架构上的普通表。
每次删除并重新创建同一个表时，您都会对整个数据库产生碎片。 并且有一个反常的好处，那就是永远不会让数据库引擎优化器有机会帮助您进行任何类型的粗略优化。 相反，尝试截断它。 您的回滚段将感谢您的这一小小的缓解，并且当您第二天再次重新填充它时，磁盘空间可能仍会被分配。 您可以通过单独为该表指定单独的表空间和数据文件来强制实现所需的行为。
最后，也是更重要的一点：不要再为了区区 1 GB 的数据而让您和您的数据库引擎感到羞愧。 为了节省 0.02 美分的硬件实际状态，您浪费了 CPU、I/O 周期、增加了延迟、碎片等。 谈论穿着燕尾服掉到地板上捡起一枚棕色美分的故事。
After 20 years of experience dealing with all major RDBMS in existence, I can only suggest a couple of things for your consideration:
Note the oxymoronic concepts: "persistent" and "temp" are complete opposites. Choose one, and one only.
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.
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.
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.
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. ????
我不得不承认在这个问题上做了双重考虑：“持久”和“临时”通常不会同时出现！ 来点开箱即用的思考怎么样？ 也许您的后台任务可以定期运行一个简单的查询，以防止 SQL 将表标记为未使用。 这样，您就可以非常直接地控制创建和销毁。
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.
创建一个永久表怎么样？ 说，我的桌子。 每 24 小时刷新一次数据，如下所示：
这样，您每天都会重新创建该表。
如果您担心日志文件，请将表存储在不同的数据库中并将其设置为“恢复模式：简单”。
How about creating a permanent table? Say, MyTable. Once every 24 hours, refresh the data like this:
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.
我会采用你的 B 计划，“在与 tempdb 相同的驱动器上创建另一个数据库，将其称为 PseudoTempDB 之类的东西，然后自己处理删除问题。”
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."
如果您将表创建为 tempdb.dbo.TempTable，则它不会被删除，直到：
a - SQL Server 重新启动
b - 您显式删除它
如果您希望它始终可用，您可以在模型中创建该表，这样它就会在重新启动期间复制到 tempdb（但它也会在您之后创建的任何新数据库上创建，因此您必须手动删除）或使用启动存储过程来创建它。 但是，无法通过重新启动来保留数据。
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.