When to use a View instead of a Table

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Oh there are many differences you will need to consider  Views for selection    Views provide abstraction over tables  You can add remove fields easily in a view without modifying your underlying schema Views can model complex joins easily  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 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  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    You can handle security issues with views by using such functionality as Oracle s  WITH CHECK OPTION  clause directly in the view   Drawbacks   You lose information about relations  primary keys  foreign keys  It s not obvious whether you will be able to insert update a view  because the view hides its underlying joins from you

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to database-design

Examples related to view