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