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?

Solid

Check my answer to a similar question, hope it helps! – Slivovitz

Answer 1

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.

Unison

item 3 is a reason that no one else seems to have pointed out yet – Solid

I think point 3 is more of stop gap than anything else. Eventually when you get round to updating the legacy code, you'll not only have to change the code behind the view but also all the code that have been built on top of the view. My 2cents – Interpellate

3 Is really the most powerful property of views. It's what help provides logical data independence the fact that you can provide an interface to the DB independent of the underlying logical database is a very powerful concept. – Single

@John this incurred technical debt has to be repaid sooner or later no? In 10 years it might not matter to that engineer who wrote it 10 years ago but it matters to the company. – Interpellate

Changing your main DB and everything depending on it is a bad choice to make because you might 'need it in 10 years'. Technical debt isn't to be avoided at all costs, only if the expected cost of fixing it later is more than the definite cost of fixing it now. – Zurn

@Crappy 4. Views can also be indexed physically (e.g. Indexed Views in MS SQL Server) – Gala

How about query performance? Can view increase the performance of my queries? – Shoer

As per your 3rd point, can we create view with same name as table? – Johnie

It's kind of vaguely implied in the 3rd point - the original table would be renamed to something else while the view is given the original name. – Unison

@devツ in MySQL 8, is not possible. – Riegel

@DaveCarlile I am confused with 3, I understand a schema how the table definition (fields and types) and a View represents complex queries based in the mentioned original table schema (and perhaps from other tables - UNION scenario). So how a view would define a new schema definition? Pls can you expand the idea?. Seems here the trick is have the view having the original table name and returning the same fields retrieved, such as if was used the original table. Am I right? – Riegel

@DaveCarlile Thus the original table can be change its name, add/change/delete fields, the view should update this once and the clients are not aware about this. Am I correct?. Seems here would be problematic if some fields are removed from the original table – Riegel

Answer 2

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.

Nadean

interesting. Security is a good answer. What 'other things' do you have in mind? – Solid

I assumed that the other answers to this question would describe the "other things". :-) – Nadean

Select name, address, zipcode from customer wouldn't serve the purpose instead of creating a view? – Spritsail

@PranavBilurkar Yes, if you completely control the queries that the users run. If the users have the ability to run their own queries (through some interactive SQL program or writing their own scripts), they can run select * from customer which gives them access to everything. If you give them access to the view and not the table, they cannot access fields that aren't in the view. – Nadean

This is the best explanation of why we need to create a view because it included examples. – Mcclintock

Answer 3

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.

Recorder

So you would want to create a view when you have a complicated query? How complicated of a query, what's the threshold? What do you gain from making it a view? – Solid

How complicated is a personal choice really, there's no set threshold. You would often use a view if you don't want to duplicate logic in multiple applications or different points in your application for example. By making it a view you hide that logic and are able to share it easily. – Hickie

can't you do the same with a stored procedure that has a select? have i incorrectly thought of stored procedures as a way to store complex query logic? should complex queries be done in views instead of stored procedures? Whats the advantage of a stored procedure here? – Solid

@Solid - A stored procedure returns a result-set whereas a view represents a virtual table that allows you to use as a table in other queries. – Recorder

FYI some database systems allow selecting from a stored procedure as if it were a table or view, so you get the best of both worlds. – Nadean

I think this point about result set vs virtual table seems to be a key point that I didn't understand. – Solid

I believe in the past there were caching advantages also. I tend to use views for reusability. also its handy to tie lots of different db's together into a views layer for instnace (not that i entirely agree with this ) – Mortality

Answer 4

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.

U

can you elaborate on this? Your answer is being voted up quite a bit, but I'm not getting the value that everyone else seems to – Solid

Answer 5

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.

Whimsicality

Answer 6

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.

Promise

+1 for being one of the few answers to address the issue of stored procedures against SELECT statements. You're right to raise the issue of table functions. Basically, view are likely to perform better than functions because they share the same engine. There is an overhead (at least in Oracle) to be paid when switching from SQL to trabsactional SQL (i.e. PL/SQL). But all the other stuff - security, encapsulation, etc - applies equally to procedures or functions as to views. – Blackman

Depending on the structure of the view, some views can be indexed. That is a big improvement over table valued functions. – Whimsicality

Answer 7

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.

Pentangular

interesting. In your case, it is almost like an interface to the tables. – Solid

Answer 8

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.

Become

Answer 9

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

Dingbat

I must say I've learned a few things here. "To Combine Partitioned Data" is something I was unaware of even the performance is a good benefit. If you build those partitioned tables correctly it could be a perfect improvement. – Delineation

I also want to point out that this information is from an SQL2000 source that is no longer available in web page form, except in a legacy downloadable PDF from Microsoft with 20k pages (follow the link in the post above to download it). Page: 1139 "Scenarios for Using Views" – Delineation

Answer 10

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.

Decapitate

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.

Philoctetes

Answer 12

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.

Welloff

Answer 13

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

Rollick

Answer 14

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

Thayne

what do you mean by a 'snapshot of a table'? When or why would you want to do that? – Solid

There are many scenarios; say you want to run a complex query/store-precedure on a table without effecting and underlining table. You create a view (a read-only representation) – Thayne

so if you want to run a complex query store procedure, couldn't you access the view in a read only fashion? I really don't have the database experience to 'get' what you are talking about here. Could you elaborate or provide a detailed example? – Solid

Answer 15

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.

Platina

when you are running a query as opposed to? Why? This point doesn't quite make sense – Solid

when you use a view, you know you are only perform a DML operation, when you call an SP you don't what else maybe happening before you get your data. I.e. calling a cache function, may return the cached dataset, but it doesn't mean you should call the SP everything you want the data. It simplifies the API to the data IMO – Platina

Answer 16

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.

Previse

Answer 17

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.

Hickie

Answer 18

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.

Alopecia

can you give examples of small services – Solid

Answer 19

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.

Dowry

Answer 20

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.

Homologue

pardon me if this is outside the scope of the question, but several people have mentioned this-- don't you incur some kind of performance penalty for doing this? – Solid

Not at all. SQL Server optimizer show the exact same plan to select * from view as it does for the SQL joins equivalent to the view – Homologue

Answer 21

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.

Birdie

Answer 22

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.

Freemasonry

Answer 23

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.

Cressi

If you google it You would have got a very clear information for this question. – Farthingale

Answer 24

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.

Cinquain

This is only one reason why you should/could use a view. – Delisadelisle

Answer 25

Think of it as refactoring your database schema.

Gentianaceous