SQL Server 2005 中的具体化查询表
DB2 支持物化查询表 (MQT)。基本上，您编写一个查询并创建一个 MQT。但与 View 的区别在于，查询是预先执行的，结果数据存储在 MQT 中，并且有一些选项何时刷新/同步 MQT 与基表。
我希望在 SQL Server 中具有相同的功能。 有没有办法达到相同的结果？
我有数百万行的表格，我想在仪表板中显示摘要（例如会员总数、总费用等）。所以我不想每次用户访问仪表板时都进行计数，而是想将它们存储在表中，并且我希望该表每晚刷新一次。
欢迎任何类型的提示、答案、建议和想法。 谢谢。
In DB2 there is a support for Materialized Query Table (MQT). Basicly you write a query and create a MQT. But the difference from View is that the query is pre-executed and resulting data is stored in MQT and there are some options when to refresh/syncronize the MQT with base tables.
I want same functionality in SQL Server. Is there a way to achieve same result?
I've tables with millions of rows, and I want to show summary (like total # of members, total expense and etc) in dashboard. So I don't want to count every time user gets to dashboard, instead I want to store them in table and I want that table to be refresh each night.
Any kind of hints, answers,suggestions and ideas are welcome.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，看看这篇文章
http://msdn.microsoft.com/en-us/library/cc917715。 aspx 关于“索引视图”
并查看 (WITH SCHEMABINDING) 选项
Yes, look at this article
http://msdn.microsoft.com/en-us/library/cc917715.aspx about "Indexed view"
and look also at (WITH SCHEMABINDING) option
您对复制有何看法，他们说几乎每个业务场景考虑复制数据报告
What Do You think abaut Replication, They said that almost every business scenario considering replication for data reporting
但绝对是这样！！
“索引视图”是 SQL Server 中视图的具体化 - 生成的数据被组装并存储在磁盘上。因此，从这个意义上说，查询是预先执行的。
不，您不必为每个字段建立索引 - 只是您向视图添加聚集索引（基于合适的列）实际上会将结果数据存储到磁盘。在 SQL Server 中，聚集索引实际上就是数据。
查看 SQL Server 2000 联机丛书中的这篇文章：创建索引视图
Microsoft 明确写道：
当在视图上创建唯一聚集索引时，将执行视图，并且结果集将存储在数据库中与具有聚集索引的表的存储方式相同。
But absolutely it does!!
An "indexed view" is a materialization of a view in SQL Server - the resulting data is assembled and stored on disk. So the query is pre-executed, in that sense.
And no, you don't have to index on every field -- just the fact you're adding a clustered index to the view (based on a suitable column) actually stores the resulting data to disk. In SQL Server, the clustered index is the data, really.
Check out this article in SQL Server 2000 Books Online: Creating an Indexed View
Microsoft clearly writes:
When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored.
物化视图基于一个或多个源表，并且当源表更新时将立即更新其数据。这是一个强大的功能，但根据之前帖子的讨论，它听起来不像您想要或需要的。
执行您想要的操作的一个简单方法是：
汇总数据需要在（或“截至”）特定时间（例如上午 12:01）做好准备？如果是这样，请创建一个 SQL 代理作业并将其配置为在凌晨 12:01 启动该过程。是否只能在之前的一两个例程准备或完成前一天的数据后才能准备汇总数据？如果是这样，请在该过程结束时添加对汇总例程的调用。
（在 DB2 中如何配置？如何确定或配置 MQT 何时刷新？）
Materialized views are based on a source table (or tables), and will update their data immediately when the source table(s) are updated. This is a powerful feature, but based on discussion on the prior posts it does not sound like what you want or need.
A simple way to do what you want is to:
Does the summarized data need to be prepared at (or "as of") a specific time, such as 12:01am? If so, create a SQL Agent job and configure it to launch the procedure at 12:01am. Can the summarized data only be prepared after a prior routine or two has prepared or finalized the preceding day's data? If so, add a call to the summarizing routine at the end of that process.
(How would this be configured this in DB2? How do you determine or configure when an MQT is refreshed?)