There is a general misconception of the following statement that you make.

"In this case SQL Server will still have to join all the tables in the view to return the data from a single table"

In the "Enterprise" edition, the query optimizer is smart enough to know that the other tables are not needed and does NOT include them in the query plan. You can easily verify this by generating the "Actual Execution Plan", where you see that the unused tables ar not included in the plan.

With this in mind, generating complex views with many table joins can be defined once (efficiently and correctly) and then used specifying only the desired field.

For example.

CREATE VIEW [dbo].[vwOrderInfo] AS

SELECT <Fields from all tables>

FROM OrderItems itm

JOIN Products prd On prd.ProductId = itm.ProductID

JOIN Brands bra ON bra.BrandID = prd.BrandID

JOIN Orders ord ON ord.OrderID = itm.OrderID

JOIN Customers cus ON cus.CustomerID = ord.CustomerID

JOIN SalesReps rep ON rep.SaleRepID = cus.SalesRepID

IF I need to report on a count of Orders By SalesRep, I can query;

SELECT SalesRepName, COUNT(DISTINCT OrderID)

FROM [dbo].[vwOrderInfo]

GROUP BY SalesRepName

Which would only use the tables Orders, Customers, SalesReps

If I need to report the Total By Brand and product, I can query;

SELECT BrandName, ProductID, ProductName, SUM(ItemUnit)

FROM [dbo].[vwOrderInfo]

GROUP BY BrandName, ProductID, ProductName

Which would only use the tables OrderItems, Products and Brands

The benefit is that this on ONE VIEW can serve many query situations efficiently and using the best query plan

while hiding the specific internals of the joins. If the underlying table structures cjange and some of the joins have to be changed, only this ONE view would change.

This is a very UNKNOWN feature that many people have misunderstood or had the misconception for a long time (myself included).

I hope this is clear.

Jose Ostos