When to use database views and when not?

Question

This question is about database views, not materialized-views.

Pros:

• Query simplification.
• Avoid repeat the same joins on multiples queries.
• Avoid magic numbers.

Cons:

• Hiding real queries (may be you are repeating joins).

What else?

Answer 1

Pros: Allows you to change the underlying data structures without affecting the queries applications are using (as long as your view can hide the data structures)

Answer 2

Views are pretty awesome when you don't entirely trust the party sending queries to your database. A good example might be you would create a view on tables for a contractor so that all they can see is the rows pertaining to their project.

Answer 3

Security. Grant access on a view to the users who should be able to see the columns returned from it.

Answer 4

I used to use them all the time, now rarely. However, I do all my data access thru stored procedures so the usefulness of a view is somewhat less since the SP can hide the complexity of the join where needed.

I'd still consider using a view if a had a particularly complicated joined of many tables, from which I needed to then build many SP's on top of, but to be honest, I can't think of any I have in production right now.

The other scenario would I would use one would be where my users have access to the database for generating their own reports, and I wanted to hide the underlying complexity for them.

Answer 5

I always use views to select data from a database since all the reasons for it are mentioned before (security, changing data-structures, etc.) there is nothing else to say.

Answer 6

Now that SQL Server has common table expressions I find myself creating fewer views. When I do create a view it's usually for a normalized hierarchy that can be used in many queries not something that replaces one query.

For example Region, Market, and City may be three normalized tables (snowflake). 90% of my queries need this data, so I'll create a view. The view never replaces a single query but makes all the other queries simple and DRY.

Answer 7

Although views can hide complexity and multiple joins, these is complexity that would have been in the SP anyway.

If the SP could have been optimized, then the view should be optimized, which would lead to increased performance on all SPs that hit that view.

Views are incredibly powerful and useful for one reason that stands out above all the other very good reasons. They reduce code duplication. That is, in most cases, the bottom line. If a query will be used in three or more places, then a view will drastically simplify your changes if the schema or query parameters change.

I once had to edit 22 stored procedures to change some query logic. If the original architecture had utilized views, then I would have had only three changes.

Answer 8

I've had to use views a few times for doing strange joins and grouping by aliases.

By strange joins, I mean selecting a list of distinct dates and then outer joining them back to the table they came from to get null entries for empty days. I couldn't figure out any other way of doing it.

As for grouping by aliases, it seemed to depend on the complexity of the formula inside the alias. If the alias didn't reference any actual columns, or columns that were already being grouped, everything was ok, but aliases on columns that weren't included in the grouping were throwing errors.

I seem to recall reading or hearing somewhere during my university days that selecting from a view was faster than selecting from a bunch of joined tables, but I don't know if that's true.

One last advantage of using a view: pivot tables in Excel. I don't think there is a way of joining tables, or at least not in the wizard interface. It might be possible to do joins in Microsoft Query, but I haven't tried yet because the thought just occurred to me now.