views:

120

answers:

1

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.

  1. USE Change Data Capture on both tables and identify changes and apply them on the new table 'TbSalesAge'
  2. Use a materialized view instead of a physical table
  3. Some other method (explain please...)

PS: I don't need real-time replica

A: 

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.

rusty_scupper