[sql] When to use a View instead of a Table?

When should a View actually be used over an actual Table? What gains should I expect this to produce?

Overall, what are the advantages of using a view over a table? Shouldn't I design the table in the way the view should look like in the first place?

First of all as the name suggests a view is immutable. thats because a view is nothing other than a virtual table created from a stored query in the DB. Because of this you have some characteristics of views:

• you can show only a subset of the data
• you can join multiple tables into a single view
• you can aggregate data in a view (select count)
• view dont actually hold data, they dont need any tablespace since they are virtual aggregations of underlying tables

so there are a gazillion of use cases for which views are better fitted than tables, just think about only displaying active users on a website. a view would be better because you operate only on a subset of the data which actually is in your DB (active and inactive users)

check out this article

hope this helped..

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.

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.

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)

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.

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.