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?
2. Are there any situations in which it is tempting to use a view when you shouldn't use one?
3. Why would you use a view in lieu of something like a table-valued function or vice versa?
4. 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.)

Tinytinya

similar to question posted here: #1279021 – Saguenay

I feel #1279021 provide better answers to this question. – Razz

Does this answer your question? Why do you create a View in a database? – Tribunate

Answer 1

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 ;)

Globigerina

I wouldn't agree that it makes sense to create a view on a table when it is a SELECT * FROM tblData. Can you offer an explanation as to why this would be beneficial? – Wilmoth

IOPO - In One Place Only - Is this a well known design phrase? I cant seem to find many references to it? Does it have other forms, e.g. DRY? – Tegument

@Tegument Yes, DRY, normalization, IOPO, these are all different flavors of the same philosophy. – Labour

Answer 2

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.

Acord

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.

Beuthen

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.

Brier

-1 "views denormalize"? sorry, but that's nonsense unless it is qualified somehow. – Wiles

They absolutely denormalize. If you have related tables that are normalized to 3rd nomral form and you create a view to 'flatten' that relationship, how is this not denormalization? – Brier

I think that's what @justsomebody meant with the statement "unless it is qualified somehow". By default views have nothing to do with denormalization unless that's what you're doing in your query. Denormalization is a use case sure, but not a primary one. – Pilgarlic

Answer 5

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.

Precipitous

Why whould I want to write complex sql in database instead of app? – Thorfinn

The more complicated your query, the more likely it will change in the future, partly due to it just hitting more tables. When the DB structures change, you would have to also change your code, and do a release. If that complexity is abstracted in a view, the DBA may be able to make structural changes to the underling tables without your code having to change. – Precipitous

Answer 6

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.

Eponymy

Answer 7

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.

Ogle

I don't think that's right, at least that's not how the DBMS theory goes (various implementations may decide to not honor the theory). The query parser will essentially replace any views it sees with the corresponding sql, and the optimizer will go from there. The 2 cases should be equivalent. – Bindweed

This also doesn't hold true when you can add indexes to your views. – Ahwaz

I'm most familiar with PostgreSQL and earlier versions weren't very good about combining queries. But the newer ones are much better. My answer doesn't apply so much anymore, at least for this particular DBMS. – Ogle

Answer 8

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.

Senskell

Answer 9

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).

Bindweed

if you denormalize by brining in 7 reference tables, and then query for something that only needs one of those reference tables. That's a lot of wasted effort. This gets worse if you start joining such views. – Bindweed

are you sure about this? MSDN says 'When SQL Server processes queries that refer to views by name, the definitions of the views normally are expanded until they refer only to base tables. This process is called view expansion. It's a form of macro expansion.' I would imagine that during the expansion process, the engine would be smart enough to include only the underlying tables (of the view) that are needed by the query. – Irresolute

Anthony, imagine a query such as "select foo_col from (select foo.*, bar.* from foo join bar on (foo.id = bar.id)". In this query, the inner select is our view. It is not clear that the bar join can be safely dropped (in fact if the relationship is not strictly 1-1, it cannot). This gets even trickier with more complex queries, and I wouldn't recommend relying on the generic RDBMS to do all the pruning a human can. Maybe SQL Server can; I would be shocked if MySQL did. Oracle? Postgres? As always, read the query plan when in doubt. – Bindweed

Answer 10

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.

Eurhythmic

Answer 11

A view is simply a stored, named SELECT statement. Think of views like library functions.

Orbiculate

Wouldn't stored procedures more of fit the buck for this? Sometimes you need functions with deletes and updates views wouldn't fit the buck for this need. Views should be looked at as abstractions or ways of limiting the target user. – Blackbird

Answer 12

I wanted to highlight the use of views for reporting. Often, there is a conflict between normalizing the database tables to speed up performance, especially for editing and inserting data (OLTP uses), and denormalizing to reduce the number of table joins for queries for reporting and analysis (OLAP uses). Of necessity, OLTP usually wins, because data entry must have optimal performance. Creating views, then, for optimal reporting performance, can help to satisfy both classes of users (data entry and report viewers).

Chief

Answer 13

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.

Mehala

Oracle CBO could choose to evaluate the view once (materialize, they call it) or merge the SQL for the view into the rest of the select statement and run that. It would decide based on what had the lower estimated cost. – Argolis

If the select statement was constant throughout the query and simply repeated several times, then a common table expression (CTE) could resolve the issue. This only applies to SQL Server 2005/2008 since it was not available in SQL Server 2000. Yes, a view still would have been invoked repeatedly. – Wilmoth

@Registered User: CTE's aren't a SQL Server specialty. They originated in DB2 and are present in PostgreSQL as well (since they're useful and in the ISO SQL standard). Whether a view is inlined or treated as a blackbox is implementation-specific, some RDBMS are smarter than others. – Wiles

@just somebody: My comment was restricted to SQL Server 2005/2008 since I don't have experience with CTE's in other RBDMS. Also, the comment was qualified to indicate this does not apply to SQL Server 2000 since CTE's were not available in SQL Server prior to 2005. – Wilmoth

Another alternate to a view would have been to put the price table in a temporary table beforehand. – Mehala

Answer 14

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.

Blackbird