本地临时表与全局临时表 - 何时使用什么？
我有一个报告，在执行时使用 my_report_user 用户名连接到数据库。该报告可能有许多最终用户。在每次执行中，将使用 my_report_user 建立与数据库的新连接（没有连接池）
我有一个结果集，我认为该结果集只能创建一次（可能在第一次运行时）报告）和其他报告执行可以重用这些东西。基本上每个报告执行都应该检查该结果集（存储为临时表）是否存在。如果它不存在，则创建该结果集，否则只需重用可用的结果集。
我应该使用本地临时表(#)还是全局临时表(##)？
有没有人尝试过这样的东西，如果是的话，请让我知道我应该关心哪些事情？ （几乎同时报告运行等）
编辑：我正在使用 Sql-Server 2005
I have a report which on execution connects to the database with my_report_user username. There can be many end-users of the report. And in each execution a new connection to the database will be made with my_report_user (there is no connection pooling)
I have a result set which I think can just be created once (may be on the first run of the report) and other report executions can just reuse that stuff. Basically each report execution should check whether this result set (stored as temp table) exists or not. If it does not exist then create that result set else just reuse whats available.
Should I use local temp tables (#) or global temp tables (##)?
Has anyone tried such stuff and if yes, please let me know what all things should I care about? (Almost simultaneous report runs, etc.)
EDIT: I am using Sql-Server 2005
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
两者都不是
如果您想在自己的控制下缓存结果集，则不能使用任何类型的临时表。您应该使用普通用户表，存储在 tempdb 中，甚至拥有自己的结果集缓存数据库。
临时表、bot #local 和 ##shared 的生命周期由连接控制。如果您的应用程序断开连接，临时表将被删除，这与您所描述的情况不太相符。
真正困难的问题是在并发运行下填充这些缓存的结果集，而不会混淆（最终结果集包含来自并发报告运行的重复项目，并且双方都认为是“第一次”运行）。
附带说明一下，SQL Server Reporting Services 已经做到了开箱即用。您可以缓存和共享数据集，可以缓存和共享报告，它已经可以运行并经过测试。
Neither
If you want to cache result result sets under your own control, then you cannot use temp tables, of any kind. You should use ordinary user tables, stored either in tempdb or even have your own result set cache database.
Temp tables, bot #local and ##shared have a lifetime controlled by the connection(s). If your application disconnect, the temp table is deleted, and this does not work well with what you describe.
The real difficult prolem will be to populate these cached result sets under concurent runs without mixing things up (end up with result sets containing duplicate items from concurent report runs that both believed are the 'first' run).
As a side note SQL Server Reporting Services already does this out-of-the-box. You can cache and share datasets, you can cache and share reports, it already works and was tested for you.
我发现 #temp 表在某些情况下很有用，但不是最佳实践。我还没有找到全局 ##temp 表的有效用途，无论是在我自己的工作中，还是在其他任何撰写过相关文章的人的工作中。我能想到的唯一情况是 BCP 或其他外部进程需要构建临时数据存储，然后在后续步骤中检索它。在这种情况下，我更愿意使用带有某种密钥的永久表和后台进程来处理清理。
I find #temp tables can be useful in certain scenarios, but not as a best practice. I have yet to find a valid use for global ##temp tables, either in my own work, or in the work of anyone else who has written about them. The only case I can think of is BCP or other external process which needs to build a temporary data store and then retrieve it in some subsequent step. In that case I would prefer to use a permanent table with some kind of key and a background process to handle cleanup.
听起来您现在正在进入 OLTP 模式。阅读数据库仓库方面的知识肯定会对您有所帮助。
It sounds like you are getting into an OLTP mode now. Reading up on database warehousing will definitely help you.