When to use a View instead of a Table?

Question

Oh there are many differences you will need to consider

Views for selection:

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. Views can model complex joins easily.
3. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
4. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
5. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.

Views for insertion/updates:

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view

Drawbacks

1. You lose information about relations (primary keys, foreign keys)
2. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you

Views can:

• Simplify a complex table structure
• Simplify your security model by allowing you to filter sensitive data and assign permissions in a simpler fashion
• Allow you to change the logic and behavior without changing the output structure (the output remains the same but the underlying SELECT could change significantly)
• Increase performance (Sql Server Indexed Views)
• Offer specific query optimization with the view that might be difficult to glean otherwise

And you should not design tables to match views. Your base model should concern itself with efficient storage and retrieval of the data. Views are partly a tool that mitigates the complexities that arise from an efficient, normalized model by allowing you to abstract that complexity.

Also, asking "what are the advantages of using a view over a table? " is not a great comparison. You can't go without tables, but you can do without views. They each exist for a very different reason. Tables are the concrete model and Views are an abstracted, well, View.

Views are acceptable when you need to ensure that complex logic is followed every time. For instance, we have a view that creates the raw data needed for all financial reporting. By having all reports use this view, everyone is working from the same data set, rather than one report using one set of joins and another forgetting to use one which gives different results.

Views are acceptable when you want to restrict users to a particular subset of data. For instance, if you do not delete records but only mark the current one as active and the older versions as inactive, you want a view to use to select only the active records. This prevents people from forgetting to put the where clause in the query and getting bad results.

Views can be used to ensure that users only have access to a set of records - for instance, a view of the tables for a particular client and no security rights on the tables can mean that the users for that client can only ever see the data for that client.

Views are very helpful when refactoring databases.

Views are not acceptable when you use views to call views which can result in horrible performance (at least in SQL Server). We almost lost a multimillion dollar client because someone chose to abstract the database that way and performance was horrendous and timeouts frequent. We had to pay for the fix too, not the client, as the performance issue was completely our fault. When views call views, they have to completely generate the underlying view. I have seen this where the view called a view which called a view and so many millions of records were generated in order to see the three the user ultimately needed. I remember one of these views took 8 minutes to do a simple count(*) of the records. Views calling views are an extremely poor idea.

Views are often a bad idea to use to update records as usually you can only update fields from the same table (again this is SQL Server, other databases may vary). If that's the case, it makes more sense to directly update the tables anyway so that you know which fields are available.

According to Wikipedia,

Views can provide many advantages over tables:

• Views can represent a subset of the data contained in a table.

• Views can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

• Views can join and simplify multiple tables into a single virtual table.

• Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.

• Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.

• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

• Views can provide extra security, depending on the SQL engine used.

Views are handy when you need to select from several tables, or just to get a subset of a table.

You should design your tables in such a way that your database is well normalized (minimum duplication). This can make querying somewhat difficult.

Views are a bit of separation, allowing you to view the data in the tables differently than they are stored.

A common practice is to hide joins in a view to present the user a more denormalized data model. Other uses involve security (for example by hiding certain columns and/or rows) or performance (in case of materialized views)

You should design your table WITHOUT considering the views.
Apart from saving joins and conditions, Views do have a performance advantage: SQL Server may calculate and save its execution plan in the view, and therefore make it faster than "on the fly" SQL statements.
View may also ease your work regarding user access at field level.