Is writing a query using Views a good strategy?

Question

I just want to know whether it is correct to write a query by first creating it in a view.

Any suggestions?

Answer 1

Querying a view is generally ok, as sql sever optimiser is not easily fooled. But don't use scalar functions in your view or you'll get performance problems.

Answer 2

It depends on what you're doing in the views, and what you plan to do with them.

Creating views, and then layering them on top of one another (by referencing a view inside another view) generally isn't a good idea for instance. Using an ORDER BY in a view also is a general no-no, because if you join the view - the order by is getting executed but there's no need for it.

Answer 3

A view is a macro and is expanded.

Views on top of views simply mean more work for the optimiser to unravel the complexity underneath.

Answer 4

At the end of the day a view is just a sub query so its really no big deal.

SELECT *
FROM (
    SELECT *
    FROM myTable
    WHERE a = 2
) a

is no different then

SELECT *
FROM myView

The plan gets cached so you don't really suffer any performance impacts and you get the flexibility of reusing your code else were.

Answer 5

I know that there is a school of thought that says that you ought to have a view-per-table and develop your code against your views instead of your tables. In this scenario, you can use evolutionary development methods and, as your modify your tables, develop new views for your new code without breaking the views that the old code relies on. I've never actually had a project that I thought would be complicated enough that I couldn't just update the code as the tables changed so I have never used this method.

As for writing queries into views there is one scenario under which I do this. They may be specific to my use of LINQ, however, so take this with a grain of salt. If I have a query that needs some complicated processing and returns a composite object based on more than one table, I will usually write a stored procedure or table-valued function to do the query. I've found it easiest to work with the LINQ designer if I also create a view that I can drag onto the design surface to create the class for the objects that the sp/function returns rather than having it auto-generate a class. It gives me more control over naming the class later if I choose.