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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

It can function as a good  middle man  between your ORM and your 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

User · Answer

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  or tables   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

User · Answer

Here is how to use a View along with permissions to limit the columns a user can update in the table      This creates the view  limiting user to only 2 columns from MyTestTable    CREATE VIEW dbo myTESTview  WITH SCHEMABINDING AS SELECT ID  Quantity FROM dbo MyTestTable      This uses the view to execute an update on the table MyTestTable    UPDATE dbo myTESTview SET Quantity   7 WHERE ID   1

User · Answer

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

User · Answer

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 financial 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  Views are also very useful when refactoring databases  You can often hide the change so that the old code doesn t see it by creating a view  Read on refactoring databases to see how this work as it is a very powerful way to refactor

User · Answer

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

User · Answer

To Focus on Specific Data Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible  Unnecessary data can be left out of the view  This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table  For more information about using views for security purposes  see Using Views as Security Mechanisms   To Simplify Data Manipulation Views can simplify how users manipulate data  You can define frequently used joins  projections  UNION queries  and SELECT queries as views so that users do not have to specify all the conditions and qualifications each time an additional operation is performed on that data  For example  a complex query that is used for reporting purposes and performs subqueries  outer joins  and aggregation to retrieve data from a group of tables can be created as a view  The view simplifies access to the data because the underlying query does not have to be written or submitted each time the report is generated  the view is queried instead  For more information about manipulating data   You can also create inline user-defined functions that logically operate as parameterized views  or views that have parameters in WHERE-clause search conditions  For more information  see Inline User-defined Functions   To Customize Data Views allow different users to see data in different ways  even when they are using the same data concurrently  This is particularly advantageous when users with many different interests and skill levels share the same database  For example  a view can be created that retrieves only the data for the customers with whom an account manager deals  The view can determine which data to retrieve based on the login ID of the account manager who uses the view   To Export and Import Data Views can be used to export data to other applications  For example  you may want to use the stores and sales tables in the pubs database to analyze sales data using Microsoft   Excel  To do this  you can create a view based on the stores and sales tables  You can then use the bcp utility to export the data defined by the view  Data can also be imported into certain views from data files using the bcp utility or BULK INSERT statement providing that rows can be inserted into the view using the INSERT statement  For more information about the restrictions for copying data into views  see INSERT  For more information about using the bcp utility and BULK INSERT statement to copy data to and from a view  see Copying To or From a View   To Combine Partitioned Data The Transact-SQL UNION set operator can be used within a view to combine the results of two or more queries from separate tables into a single result set  This appears to the user as a single table called a partitioned view  For example  if one table contains sales data for Washington  and another table contains sales data for California  a view could be created from the UNION of those tables  The view represents the sales data for both regions  To use partitioned views  you create several identical tables  specifying a constraint to determine the range of data that can be added to each table  The view is then created using these base tables  When the view is queried  SQL Server automatically determines which tables are affected by the query and references only those tables  For example  if a query specifies that only sales data for the state of Washington is required  SQL Server reads only the table containing the Washington sales data  no other tables are accessed   Partitioned views can be based on data from multiple heterogeneous sources  such as remote servers  not just tables in the same database  For example  to combine data from different remote servers each of which stores data for a different region of your organization  you can create distributed queries that retrieve data from each data source  and then create a view based on those distributed queries  Any queries read only data from the tables on the remote servers that contains the data requested by the query  the other servers referenced by the distributed queries in the view are not accessed   When you partition data across multiple tables or multiple servers  queries accessing only a fraction of the data can run faster because there is less data to scan  If the tables are located on different servers  or on a computer with multiple processors  each table involved in the query can also be scanned in parallel  thereby improving query performance  Additionally  maintenance tasks  such as rebuilding indexes or backing up a table  can execute more quickly  By using a partitioned view  the data still appears as a single table and can be queried as such without having to reference the correct underlying table manually   Partitioned views are updatable if either of these conditions is met  An INSTEAD OF trigger is defined on the view with logic to support INSERT  UPDATE  and DELETE statements   Both the view and the INSERT  UPDATE  and DELETE statements follow the rules defined for updatable partitioned views  For more information  see Creating a Partitioned View   https   technet microsoft com en-us library aa214282 v sql 80  aspx sql join

User · Answer

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    Reducing redundancy in writing queries Establishing a standard for relating entities Providing opportunities to evaluate and maximize performance for complex calculations and joins  e g  indexing on Schemabound views in MSSQL  Making data more accessible and intuitive to team members and non-developers

User · Answer

Think of it as refactoring your database schema

User · Answer

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

User · Answer

Here are two common reasons   You can use it for security  Grant no permissions on the main table and 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  This can make queries consistent and easier

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

We create view to limit or ristrict from accessing all rows column in a table If the owner wants that only specific or limited rows column needs to be shared then he will create a view with those column

User · Answer

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

User · Answer

For security  Gives each user permission to access the database only through a small set of views that contain the specific data the user or group of users is authorized to see  restricting user access to other data   Simplicity for queries and structure  A view can draw data from several tables and present a single table  simplifying the information and turning multi-table queries into single-table queries for a view and it give users a specific view of the database structure  presenting the database as a set of virtual tables specific to particular users or groups of users   For create consistent database structure  Views present a consistent  unchanged image of the database structure  even if underlying source tables are changed

User · Answer

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

User · Answer

I think first one  To  hide the complexity of Query  Its  very appropriate for views  How when we normalize Database tables increases Now to fetch data is very difficult when number of tables increases So best way to  handle is follow views If i am wrong correct me

User · Answer

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

User · Answer

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

[sql] Why do you create a View in a database?

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql