Ramesh,

I disagree. When the databases are on the same server, query plans are built the same from a view in the view-database as if it were querying on the base-table-database. You still have the ability to index the base tables to optimize the views. You only lose the functionality when you use linked servers in the views.

We have an environment for reporting here that needs to use dynamic aggregations on products. The product aggregations are on what we call market definitions and product groups (1-m-m; market def-prod grp-prod). Users have the ability to add and remove products from these groupings based on rules regarding attributes of the products. In our environment, we have a reference database with the most recent product data available that have views in the market definition database referencing them. This allows us to load a separate (parallel) database for the product reference while still using the prior load's data for updates to the market definitions. Once the new product data is loaded, we switch database names - making the newly loaded data the live reference data. Views work great with this implementation.

On the reporting end, we have several data marts that have views in them referencing the OLTP (Market Definition) database (we actually do the same for data mart loads - load to a separate database and switch database names). This is the only time I've ever considered mixing OLTP and Reporting applications' databases on the same server.

My point is that during tuning exercises, we've been very successful in tuning the base tables to improve view performance.