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

Views save a lot of repeated complex JOIN statements in your SQL scripts. You can just encapsulate some complex JOIN in some view and call it in your SELECT statement whenever needed. This would sometimes be handy, straight forward and easier than writing out the join statements in every query.

Answer 2

In addition to what the others have stated, views can also be useful for removing more complecated SQL queries from the application.

As an example, instead of in an application doing:

sql = "select a, b from table1 union select a, b from table2";

You could abstract that to a view:

create view union_table1_table2_v as
select a,b from table1
union
select a,b from table2

and in the app code, simply have:

sql = "select a, b from union_table1_table2_v";

Also if the data structures ever change, you won't have to change the app code, recompile, and redeploy. you would just change the view in the db.

Answer 3

Views can centralize or consolidate data. Where I'm at we have a number of different databases on a couple different linked servers. Each database holds data for a different application. A couple of those databases hold information that are relavent to a number of different applications. What we'll do in those circumstances is create a view in that application's database that just pulls data from the database where the data is really stored, so that the queries we write don't look like they're going across different databases.

Answer 4

Anytime you need [my_interface] != [user_interface].

Example:

TABLE A:

• id
• info

VIEW for TABLE A:

• Customer Information

this is a way you might hide the id from the customer and rename the info to a more verbose name both at once.

The view will use underlying index for primary key id, so you won't see a performance loss, just better abstraction of the select query.

Answer 5

Views hide the database complexity. They are great for a lot of reasons and are useful in a lot of situations, but if you have users that are allowed to write their own queries and reports, you can use them as a safeguard to make sure they don't submit badly designed queries with nasty cartesian joins that take down your database server.

Answer 6

1) What is a view useful for?

IOPO In One Place Only

•Whether you consider the data itself or the queries that reference the joined tables, utilizing a view avoids unnecessary redundancy.

•Views also provide an abstracting layer preventing direct access to the tables (and the resulting handcuffing referencing physical dependencies). In fact, I think it's good practice¹ to offer only abstracted access to your underlying data (using views & table-valued functions), including views such as

CREATE VIEW AS
      SELECT * FROM tblData

¹I hafta admit there's a good deal of "Do as I say; not as I do" in that advice ;)

2) Are there any situations in which it is tempting to use a view when you shouldn't use one?

Performance in view joins used to be a concern (e.g. SQL 2000). I'm no expert, but I haven't worried about it in a while. (Nor can I think of where I'm presently using view joins.)

Another situation where a view might be overkill is when the view is only referenced from one calling location and a derived table could be used instead. Just like an anonymous type is preferable to a class in .NET if the anonymous type is only used/referenced once.

    • See the derived table description in   http://msdn.microsoft.com/en-us/library/ms177634.aspx

3) Why would you use a view in lieu of something like a table-valued function or vice versa?

(Aside from performance reasons) A table-valued function is functionally equivalent to a parameterized view. In fact, a common simple table-valued function use case is simply to add a WHERE clause filter to an already existing view in a single object.

4) Are there any circumstances that a view might be useful that aren't apparent at first glance?

I can't think of any non-apparent uses of the top of my head. (I suppose if I could, that would make them apparent ;)