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

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.