What are views good for?

Question

I'm just trying to get a general idea of what views are used for in RDBMSes. That is to say, I know what a view is and how to make one. I also know what I've used them for in the past.

But I want to make sure I have a thorough understanding of what a view is useful for and what a view shouldn't be useful for. More specifically:

1. What is a view useful for?
   • Are there any situations in which it is tempting to use a view when you shouldn't use one?
   • Why would you use a view in lieu of something like a table-valued function or vice versa?
   • Are there any circumstances that a view might be useful that aren't apparent at first glance?

(And for the record, some of these questions are intentionally naive. This is partly a concept check.)

Answer 1

The responses so far are correct -- views are good for providing security, denormalization (although there is much pain down that road if done wrong), data model abstraction, etc.

In addition, views are commonly used to implement business logic (a lapsed user is a user who has not logged in in the last 40 days, that sort of thing).

Answer 2

I remember a very long SELECT which involved several UNIONs. Each UNION included a join to a price table which was created on the fly by a SELECT that was itself fairly long and hard to understand. I think it would have been a good idea to have a view that to create the price table. It would have shortened the overall SELECT by about half.

I don't know if the DB would evaluate the view once, or once each time in was invoked. Anyone know? If the former, using a view would improved performance.

Answer 3

Views can be used to provide security (ie: users can have access to views that only access certain columns in a table), views can provide additional security for updates, inserts, etc. Views also provide a way to alias column names (as do sp's) but views are more of an isolation from the actual table.

Answer 4

In a sense views denormalize. Denormalization is sometimes necessary to provide data in a more meaningful manner. This is what a lot of applications do anyway by way of domain modeling in their objects. They help present the data in a way that more closely matches a business' perspective.

Answer 5

In a way, a view is like an interface. You can change the underlying table structure all you want, but the view gives a way for the code to not have to change.

Views are a nice way of providing something simple to report writers. If your business users want to access the data from something like Crystal Reports, you can give them some views in their account that simplify the data -- maybe even denormalize it for them.

Answer 6

The OP asked if there were situations where it might be tempting to use a view, but it's not appropriate.

What you don't want to use a view for is a substitute for complex joins. That is, don't let your procedural programming habit of breaking a problem down into smaller pieces lead you toward using several views joined together instead of one larger join. Doing so will kill the database engine's efficiency since it's essentially doing several separate queries rather than one larger one.

For example, let's say you have to join tables A, B, C, and D together. You may be tempted to make a view out of tables A & B and a view out of C & D, then join the two views together. It's much better to just join A, B, C, and D in one query.