how can i change this view?

I want to index this view but because it has subquery i cant index. Can anyone suggest how to change this view so that i can index it.

ALTER VIEW [dbo].[Recon2] WITH SCHEMABINDING AS SELECT
dbo.Transactions.CustomerCode, dbo.Customer_Master.CustomerName, dbo.Transactions.TransDate, dbo.Transactions.PubCode, dbo.Transactions.TransType, dbo.Transactions.Copies, SUM(dbo.Transactions.TotalAmount) AS TotalAmount, '0' AS ReceiptNo, '2008-01-01' AS PaymentDate, 0 AS Amount, dbo.Transactions.Period, dbo.Transactions.Year, dbo.Publication_Master.PubName, dbo.Customer_Master.SalesCode, COUNT_BIG(*) AS COUNT FROM
dbo.Publication_Master INNER JOIN dbo.Customer_Master INNER JOIN dbo.Transactions ON dbo.Customer_Master.CustomerCode = dbo.Transactions.CustomerCode ON dbo.Publication_Master.PubCode = dbo.Transactions.PubCode WHERE
(dbo.Customer_Master.CustomerCode NOT IN (SELECT CustomerCode FROM dbo.StreetSaleRcpt WHERE (PubCode = dbo.Transactions.PubCode) AND (TransactionDate = dbo.Transactions.TransDate) AND (Updated = 1) AND (PeriodMonth = dbo.Transactions.Period) AND (PeriodYear = dbo.Transactions.Year))) GROUP BY dbo.Transactions.CustomerCode, dbo.Customer_Master.CustomerName, dbo.Transactions.TransDate, dbo.Transactions.PubCode, dbo.Publication_Master.PubName, dbo.Customer_Master.SalesCode, dbo.Transactions.[Update], dbo.Transactions.TransType, dbo.Transactions.Copies, dbo.Transactions.Period, dbo.Transactions.Year, dbo.Transactions.TotalAmount

From stackoverflow jazzrai

• I can't run it (obviously) but what about this?:

  SELECT
  dbo.Transactions.CustomerCode, 
  dbo.Customer_Master.CustomerName, 
  dbo.Transactions.TransDate, 
  dbo.Transactions.PubCode, 
  dbo.Transactions.TransType, 
  dbo.Transactions.Copies, 
  '0' AS ReceiptNo, 
  '2008-01-01' AS PaymentDate, 
  0 AS Amount, 
  dbo.Transactions.Period, 
  dbo.Transactions.Year, 
  dbo.Publication_Master.PubName, 
  dbo.Customer_Master.SalesCode, 
  dbo.StreetSaleRcpt.CustomerCode,
  SUM(dbo.Transactions.TotalAmount) AS TotalAmount, 
  COUNT_BIG(*) AS COUNT 
  FROM dbo.Publication_Master 
  INNER JOIN dbo.Customer_Master ON dbo.Customer_Master.CustomerCode = dbo.Transactions.CustomerCode 
  INNER JOIN dbo.Transactions ON dbo.Publication_Master.PubCode = dbo.Transactions.PubCode 
  LEFT OUTER JOIN dbo.StreetSaleRcpt ON (
   dbo.StreetSaleRcpt.PubCode = dbo.Transactions.PubCode 
   AND dbo.StreetSaleRcpt.TransactionDate = dbo.Transactions.TransDate
   AND dbo.StreetSaleRcpt.PeriodMonth = dbo.Transactions.Period
   AND dbo.StreetSaleRcpt.PeriodYear = dbo.Transactions.Year
   AND dbo.StreetSaleRcpt.Updated = 1
   AND dbo.StreetSaleRcpt.CustomerCode = dbo.Customer_Master.CustomerCode
  )
  WHERE dbo.StreetSaleRcpt.CustomerCode IS NULL
  GROUP BY 
  dbo.Transactions.CustomerCode, 
  dbo.Customer_Master.CustomerName, 
  dbo.Transactions.TransDate, 
  dbo.Transactions.PubCode, 
  dbo.Publication_Master.PubName, 
  dbo.Customer_Master.SalesCode, 
  dbo.Transactions.[Update], 
  dbo.Transactions.TransType, 
  dbo.Transactions.Copies, 
  dbo.Transactions.Period, 
  dbo.Transactions.Year, 
  dbo.Transactions.TotalAmount,
  dbo.StreetSaleRcpt.CustomerCode
  

  Make your correlated sub-query a left join and test for its absence ('WHERE dbo.StreetSaleRcpt.CustomerCode IS NULL') versus 'NOT IN'.

  Good luck.

  From Corbin March

• At least in Oracle, you can change from VIEW to MATERIALIZED VIEW. There will be a few other issues like table space and methods of synchronization to consider but it might be worth exploring.

  Depending on your application, another option would be to create a normal table based on the select of this view and either update it at an acceptable interval or use a lot of foreign keys.

  What's most practical depends on a number of factors -- table size, frequency of updates, need for most current data, etc.

  jazzrai : I am using sql 2005 and iam designing a view for the monthly reconciliation report. This is just a part of the query and iam having unions to join other parts..it returns 1MB.
  From igelkott

• This form would allow use of an index on StreetSaleRcpt for each Publication_Master row:

  ALTER VIEW [dbo].[Recon2] WITH SCHEMABINDING AS SELECT
  dbo.Transactions.CustomerCode, dbo.Customer_Master.CustomerName, dbo.Transactions.TransDate, dbo.Transactions.PubCode, dbo.Transactions.TransType, dbo.Transactions.Copies, SUM(dbo.Transactions.TotalAmount) AS TotalAmount, '0' AS ReceiptNo, '2008-01-01' AS PaymentDate, 0 AS Amount, dbo.Transactions.Period, dbo.Transactions.Year, dbo.Publication_Master.PubName, dbo.Customer_Master.SalesCode, COUNT_BIG(*) AS COUNT
  FROM dbo.Publication_Master 
  INNER JOIN dbo.Customer_Master 
  INNER JOIN dbo.Transactions ON dbo.Customer_Master.CustomerCode = dbo.Transactions.CustomerCode ON dbo.Publication_Master.PubCode = dbo.Transactions.PubCode 
  WHERE
  (NOT EXISTS 
     (SELECT NULL FROM dbo.StreetSaleRcpt 
     WHERE (PubCode = dbo.Transactions.PubCode) 
     AND (TransactionDate = dbo.Transactions.TransDate) 
     AND (Updated = 1)
     AND (PeriodMonth = dbo.Transactions.Period) 
     AND (PeriodYear = dbo.Transactions.Year)
     ANMD (CustomerCode = dbo.Customer_Master.CustomerCode)
     )
  ) GROUP BY dbo.Transactions.CustomerCode, dbo.Customer_Master.CustomerName, dbo.Transactions.TransDate, dbo.Transactions.PubCode, dbo.Publication_Master.PubName, dbo.Customer_Master.SalesCode, dbo.Transactions.[Update], dbo.Transactions.TransType, dbo.Transactions.Copies, dbo.Transactions.Period, dbo.Transactions.Year, dbo.Transactions.TotalAmount
  

  From Tony Andrews