物化视图还是CDC？
我有两个包含数百万条记录的表（使用 SQL 2008 存储）的视图。
CREATE VIEW VwSalesAge
AS
SELECT
Transactions.ID
,Transactions.Amount
,Customer.ID
,Customer.Name
,Customer.Age
FROM Transactions
INNER JOIN Customer
ON Transactions.CustomerID=Customer.ID
现在我想使用物理表来存储这些信息，以避免扫描大型表以进行更小的查询，例如“
SELECT *
FROM VsSalesAge
WHERE Customer.ID = 123
那么就性能而言，哪种方法是最好的方法”。
- 在两个表上使用更改数据捕获并识别更改和 将它们应用到新表“TbSalesAge”
- 使用物化视图而不是物理表
- 其他一些方法（请解释...）
PS：我不需要实时副本
I have a view on two tables (stored using SQL 2008) with millions of records.
CREATE VIEW VwSalesAge
AS
SELECT
Transactions.ID
,Transactions.Amount
,Customer.ID
,Customer.Name
,Customer.Age
FROM Transactions
INNER JOIN Customer
ON Transactions.CustomerID=Customer.ID
Now I want to use a physical table to store this information to avoid scannig of large tables for even smaller queries like
SELECT *
FROM VsSalesAge
WHERE Customer.ID = 123
So which one is the best approach in terms of performance.
- USE Change Data Capture on both tables and identify changes and
apply them on the new table 'TbSalesAge'- Use a materialized view instead of a physical table
- Some other method (explain please...)
PS: I don't need real-time replica
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
恕我直言，我认为最好的方法是使用索引视图。您将需要使用 SCHEMABINDING 选项创建视图，并且对计算列、分组函数等有一些限制，但我认为这将为您提供单个合并对象，并通过您正在寻找的索引来提高性能。
IMHO, I think the best approach would be to use an Indexed View. You will need to create the view with the SCHEMABINDING option and there are some restrictions on computed columns, grouping functions, etc., but I think this gets you the single consolidated object with the performance improvements with indexing you're looking for.