Views vs. inline subqueries SQL Server 2005/2008

Question

In certain areas in my application I need data from several tables in the database (I have an application, the application has many attributes, each attribute has several definitions, and each definition has a value). I need the data from all these tables for an application. Should I use a view (one that would be rather large) or subqueries for selecting the records? In the case of subqueries is the optimizer able to work efficiently? I'm also interested if caching will work for subqueries.

Answer 1

Views are typically expanded in place into subqueries, unless you explicitly mark the views as persisted by dropping a clustered index on them.

Answer 2

This is an 'It depends' question. A view might help to make the code more maintainable but complex selection predicates might confuse the optimiser.

Another option is a stored procedure that returns a record set. If you reuse a subquery several times you may get some mileage from splitting up the query, selecting the subquery into a temporary table and combining the parts in a later step.

Without a more specific description of the problem it's hard to really give a meaningful answer.