What are the downsides of using SqlServer Views?

Question

What are the downsides of using SqlServer Views?

I create views frequently to show my data in a denormalized form.

I find it much easier and therefore faster, less error prone, and more self documenting, to query one of these joins rather than to generate complex queries with complicated joins between many tables. Especially when I am analyzing the same data (many same fields, same table joins) from different angles.

But is there a cost to creating and using these views?

Am I slowing down (or speeding up?) query processing?

Answer 1

When comes to Views there are advantages and disadvantages.

Advantages:

1. They are virtual tables and not stored in the database as a distinct object. All that is stored is the SELECT statement.
2. It can be used as a security measure by restricting what the user can see.
3. It can make commonly used complex queries easier to read by encapsulating them into a view. This is a double edged sword though - see disadvantages #3.

Disadvantages:

1. It does not have an optimized execution plan cached so it will not be as fast as a stored procedure.
2. Since it is basically just an abstraction of a SELECT it is marginally slower than doing a pure SELECT.
3. It can hide complexity and lead to gotchas. (Gotcha: ORDER BY not honored).

My personal opinion is to not use Views but to instead use stored procedures as they provide the security and encapsulation of Views but also come with improved performance.

Answer 2

One possible downside of using views is that you abstract the complexity of the underlying design which can lead to abuse by junior developers and report creators.

For a particularly large and complex project I designed a set of views which were to be used mostly by report designers to populate crystal reports. I found out weeks later that junior devs had started using these views to fetch aggregates and join these already large views simply because they were there and were easy to consume. (There was a strong element of EAV design in the database.) I found out about this after junior devs started asking why seemingly simple reports were taking many minutes to execute.