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

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.

Answer 2

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

Answer 3

In a typical scenario I need to get all the attributes for an application, their definitions and values to display in a form-table like structure in a web page. My understanding of a view is that first a huge table will be populate for all the applications and then a select will be performed on the table to get just the data related to my application. If views are expanded in place, that means that the join table will only contain the values for my application?