Why do you create a View in a database?

Question

When and Why does some one decide that they need to create a View in their database? Why not just run a normal stored procedure or select?

Answer 1

When I want to see a snapshot of a table(s), and/or view (in a read-only way)

Answer 2

A view is an encapsulation of a query. Queries that are turned into views tend to be complicated and as such saving them as a view for reuse can be advantageous.

Answer 3

It can function as a good "middle man" between your ORM and you tables.

Example:

We had a Person table that we needed to change the structure on it so the column SomeColumn was going to be moved to another table and would have a one to many relationship to.

However, the majority of the system, with regards to the Person, still used the SomeColumn as a single thing, not many things. We used a view to bring all of the SomeColumns together and put it in the view, which worked out nicely.

This worked because the data layer had changed, but the business requirement hadn't fundamentally changed, so the business objects didn't need to change. If the business objects had to change I don't think this would have been a viable solution, but views definitely function as a good mid point.

Answer 4

Among other things, it can be used for security. If you have a "customer" table, you might want to give all of your sales people access to the name, address, zipcode, etc. fields, but not credit_card_number. You can create a view that only includes the columns they need access to and then grant them access on the view.

Answer 5

I usually create views to de-normalize and/or aggregate data frequently used for reporting purposes.

EDIT

By way of elaboration, if I were to have a database in which some of the entities were person, company, role, owner type, order, order detail, address and phone, where the person table stored both employees and contacts and the address and phone tables stored phone numbers for both persons and companies, and the development team were tasked with generating reports (or making reporting data accessible to non-developers) such as sales by employee, or sales by customer, or sales by region, sales by month, customers by state, etc I would create a set of views that de-normalized the relationships between the database entities so that a more integrated view (no pun intended) of the real world entities was available. Some of the benefits could include:

1. Reducing redundancy in writing queries
2. Establishing a standard for relating entities
3. Providing opportunities to evaluate and maximize performance for complex calculations and joins (e.g. indexing on Schemabound views in MSSQL)
4. Making data more accessible and intuitive to team members and non-developers.

Answer 6

Several reasons: If you have complicated joins, it is sometimes best to have a view so that any access will always have the joins correct and the developers don;t have to remember all the tables they might need. Typically this might be for a finacial application where it would be extremely important that all financial reports are based on the same set of data.

If you have users you want to limit the records they can ever see, you can use a view, give them access only to the view not the underlying tables and then query the view

Crystal reports seems to prefer to use views to stored procs, so people who do a lot of report writing tend to use a lot of views

Answer 7

There is more than one reason to do this. Sometimes makes common join queries easy as one can just query a table name instead of doing all the joins.

Another reason is to limit the data to different users. So for instance:

Table1: Colums - USER_ID;USERNAME;SSN

Admin users can have privs on the actual table, but users that you don't want to have access to say the SSN, you create a view as

CREATE VIEW USERNAMES AS SELECT user_id, username FROM Table1;

Then give them privs to access the view and not the table.

Answer 8

I like to use views over stored procedures when I am only running a query. Views can also simplify security, can be used to ease inserts/updates to multiple tables, and can be used to snapshot/materialize data (run a long-running query, and keep the results cached).

I've used materialized views for run longing queries that are not required to be kept accurate in real time.

Answer 9

here are two common reasons:

you can use it for security. grant no permissions on the main table, create views that limits column or row access and grant permissions to users to see the view.

you can use use it for convenience. join together some tables that you use together all the time in the view, can makes queries consistant and easier.

Answer 10

A view provides several benefits.

1. Views can hide complexity

If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

2. Views can be used as a security mechanism

A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

3. Views can simplify supporting legacy code

If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.

These are just some of the many examples of how views can be useful.

Answer 11

Views can be a godsend when when doing reporting on legacy databases. In particular, you can use sensical table names instead of cryptic 5 letter names (where 2 of those are a common prefix!), or column names full of abbreviations that I'm sure made sense at the time.

Answer 12

Views also break down very complex configuration and tables into managable chunks that are easily queried against. In our database, our entire table managment system is broken down into views from one large table.

Answer 13

This doesn't answer your question exactly but I thought it would be worth mentioning Materialized Views. My experience is mostly with Oracle but supposedly SQL-Server is fairly similar.

We used something similar in our architecture to address XML performance problems. Our systems are designed with a lot of data stored as XML on a row and applications might need to query particular values within it. Handling lots of XMLTypes and running XPaths across large number of rows has a large impact on performance so we use a form of materialized views to extract the desired XML nodes out into a relational table anytime the base table changes. This effectively provides a physical snapshot of the query at a point in time as opposed to standard views which would run their query on demand.

Answer 14

I see a stored procedure more as a method I can call against my data, whereas to me a view provides a mechanism to create a synthetic version of the base data against which queries or stored procedures can be created. I'll create a view when simplification or aggregation makes sense. I'll write a stored procedure when I want to provide a very specific service.

Answer 15

The one major advantage of a view over a stored procedure is that you can use a view just like you use a table. Namely, a view can be referred to directly in the FROM clause of a query. E.g., SELECT * FROM dbo.name_of_view.

In just about every other way, stored procedures are more powerful. You can pass in parameters, including out parameters that allow you effectively to return several values at once, you can do SELECT, INSERT, UPDATE, and DELETE operations, etc. etc.

If you want a View's ability to query from within the FROM clause, but you also want to be able to pass in parameters, there a way to do that too. It's called a table-valued function.

Here's a pretty useful article on the topic:

http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

EDIT: By the way, this sort of raises the question, what advantage does a view have over a table-valued function? I don't have a really good answer to that, but I will note that the T-SQL syntax for creating a view is simpler than for a table-valued function, and users of your database may be more familiar with views.

Answer 16

One curious thing about views are that they are seen by Microsoft Access as tables: when you attach a Microsoft Access front-end to an SQL database using ODBC, you see the tables and views in the list of available tables. So if you are preparing complicated reports in MS Access, you can let the SQL server do the joining and querying, and greatly simplify your life. Ditto for preparing a query in MS Excel.

Answer 17

Think of it as refactoring your database schema.

Answer 18

Generally i go with views to make life easier, get extended details from some entity that's stored over multiple tables (eliminate lots of joins in code to enhance readability) and sometimes to share data over multiple databases or even to make inserts easier to read.

Answer 19

I only have 10 or so views in my production databases. I use several for columns I use all the time. One set I use come from 7 tables, some with outer joins and rather than rewrite that constantly I only have to call that view in a select and make one or 2 joins. To me it is just a time saver.

Answer 20

I am creating xxx that maps all the relationships between a main table (like Products table) and reference tables (like ProductType or ProductDescriptionByLanguage). This will create a view that will allow me retrieve a product and all it's details translated from its foreign keys to its description. Then I can use an ORM to create objects to easily build grids, combo boxes, etc.

Answer 21

Hey I found this site that touches on creating views for databases. There's even some example coding. Enjoy!

http://www.izenda.com/Site/KB/Integration/18

Answer 22

What about performance? Isn't a DB engine able to optimise a view, and let you put indices on it for instance? Or at least know it's a view and therefore cache the query results into a table in the background?