为什么要在数据库中创建视图?

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?

何时以及为何有人决定需要在数据库中创建视图?为什么不运行一个正常的存储过程或者选择呢?

24 个解决方案

#1

409

A view provides several benefits.

视图提供了几个好处。

1. Views can hide complexity

1。视图可以隐藏复杂性

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

2。视图可以用作安全机制

A view can select certain columns and/or rows from a table, 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

3所示。视图可以简化对遗留代码的支持

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.

这些只是视图如何有用的众多示例中的一部分。

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

此外，它还可以用于安全性。如果您有一个“customer”表，那么您可能希望让所有销售人员访问名称、地址、zipcode等字段，而不是credit_card_number。您可以创建一个只包含需要访问的列的视图，然后授予它们对视图的访问权。

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

视图是查询的封装。将查询转换为视图往往比较复杂，因此将它们保存为视图以便重用是有利的。

#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:

细化,如果我有一个数据库的一些实体的人,公司,角色,所有者类型,订单,订单细节,地址和电话,那里的人表存储员工和联系人地址和电话表存储电话号码为个人和公司,和开发团队负责生成报告(或非开发人员可以访问报告数据)等销售员工,由客户或销售,或者销售区域,按月销售，按状态客户，等等，我将创建一组视图，这些视图使数据库实体之间的关系非规范化，从而可以获得真实世界实体的更集成的视图(没有双关语)。其中一些好处可以包括:

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)
为复杂的计算和连接提供评估和最大化性能的机会(例如，在MSSQL中对schem视图进行索引)
Making data more accessible and intuitive to team members and non-developers.
使数据对团队成员和非开发人员更易于访问和直观。

#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

Crystal报表似乎更喜欢使用视图来存储procs，所以写大量报表的人往往使用很多视图

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.

视图在重构数据库时也非常有用。您通常可以隐藏更改，以便旧代码通过创建视图不会看到它。阅读重构数据库，看看它是如何工作的，因为它是一种非常强大的重构方法。

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

与存储过程相比，视图的一个主要优点是可以像使用表一样使用视图。也就是说，可以直接在查询的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.

在几乎所有其他方面，存储过程都更强大。您可以传入参数，包括允许您有效地同时返回多个值的out参数，您可以执行选择、插入、更新和删除操作等操作。

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.

如果您希望视图能够从from子句中查询，但也希望能够传递参数，那么也有一种方法可以做到这一点。它被称为表值函数。

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.

顺便说一下，这就提出了一个问题，视图比表值函数有什么优势?对此我没有一个很好的答案，但我要指出的是，创建视图的T-SQL语法比表值函数更简单，而且数据库的用户可能更熟悉视图。

#7

It can function as a good "middle man" between your ORM and your tables.

它可以在你的ORM和你的表格之间充当一个好的“中间人”。

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.

我们有一个Person表，我们需要改变它的结构，因此SomeColumn列将被移到另一个表中并与之有一个到多个关系。

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.

然而，就人而言，大多数系统仍然把一些分子作为单一的东西，而不是很多。我们使用视图将所有的somecolumn组合在一起，并将其放在视图中，效果很好。

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.

这是因为数据层发生了变化，但是业务需求没有从根本上改变，因此业务对象不需要更改。如果业务对象必须更改，我不认为这是一个可行的解决方案，但视图肯定是一个很好的中间点。

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

你可以用它来方便。将视图中一直使用的一些表连接在一起。这可以使查询一致和更容易。

#9

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

表1:科勒姆- USER_ID;用户名,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

管理用户可以在实际的表上有privs，但是不希望访问SSN的用户可以创建一个视图as

CREATE VIEW USERNAMES AS SELECT user_id, username FROM Table1;

Then give them privs to access the view and not the table.

然后让他们访问视图而不是表。

#10

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.

在报告遗留数据库时，视图是天赐之物。特别是，您可以使用有意义的表名，而不是晦涩的5个字母名称(其中两个是常见的前缀!

#11

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

#12

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.

您还可以创建内联用户定义函数，这些函数在逻辑上作为参数化视图操作，或者在where子句搜索条件中具有参数的视图。有关更多信息，请参见内联用户定义函数。

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.

要定制数据视图，允许不同的用户以不同的方式查看数据，即使他们同时使用相同的数据。当具有许多不同兴趣和技能级别的用户共享相同的数据库时，这尤其有利。例如，可以创建一个视图，该视图只检索帐户管理器处理的客户的数据。视图可以根据使用视图的帐户管理器的登录ID确定要检索的数据。

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.

导出和导入数据视图可用于将数据导出到其他应用程序。例如,您可能希望使用商店和酒吧销售表数据库使用Microsoft®Excel分析销售数据。为此，您可以基于商店和销售表创建视图。然后可以使用bcp实用程序导出视图定义的数据。还可以使用bcp实用程序或批量插入语句将数据导入到数据文件的某些视图中，提供可以使用INSERT语句将行插入视图中。有关将数据复制到视图的限制的更多信息，请参见INSERT。有关使用bcp实用程序和批量插入语句将数据复制到或从视图复制的更多信息，请参阅复制到视图或从视图复制。

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.

要合并分区数据，可以在视图中使用Transact-SQL联合集运算符，将两个或多个查询的结果从不同的表合并到一个结果集中。例如，如果一个表包含华盛顿的销售数据，而另一个表包含加利福尼亚的销售数据，那么可以从这些表的联合创建一个视图。视图表示两个区域的销售数据。要使用分区视图，需要创建几个相同的表，并指定一个约束来确定可以添加到每个表的数据范围。然后使用这些基表创建视图。当查询视图时，SQL Server自动确定哪些表受到查询的影响，并且只引用这些表。例如，如果查询指定只需要华盛顿州的销售数据，SQL Server只读取包含华盛顿销售数据的表;不访问其他表。

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

https://technet.microsoft.com/en-us/library/aa214282(v = sql.80). aspx # sql:加入

#13

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

当我想要查看表和/或视图的快照时(以只读方式)

#14

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.

我使用了物化视图来运行长期查询，这些查询不需要实时保持准确。

#15

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.

视图还将非常复杂的配置和表分解为可管理的块，这些块很容易查询。在我们的数据库中，我们的整个表管理系统被分解成一个大表的视图。

#16

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.

这并不能完全回答你的问题，但我认为值得一提的是物化的观点。我的经验主要是在Oracle上，但是据说SQL-Server是相当相似的。

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.

我们在架构中使用了类似的东西来解决XML性能问题。我们的系统被设计为以XML形式存储在一行中的大量数据，应用程序可能需要查询其中的特定值。处理大量的XMLTypes并跨大量行运行xpath对性能有很大的影响，因此我们使用物化视图的形式，在基表更改时将所需的XML节点提取到关系表中。这有效地提供了查询在某个时间点的物理快照，而不是按需运行查询的标准视图。

#17

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.

我把存储过程更多地看作是我可以对我的数据调用的方法，而对我来说，视图提供了一种机制来创建基数据的合成版本，可以根据它创建查询或存储过程。当简化或聚合有意义时，我将创建一个视图。当我想提供一个非常特定的服务时，我将编写一个存储过程。

#18

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.

视图的一个奇怪之处是，它们被Microsoft Access视为表:当您使用ODBC将Microsoft Access前端附加到SQL数据库时，您将看到可用表列表中的表和视图。因此，如果您准备在MS Access中编写复杂的报告，您可以让SQL server进行连接和查询，并大大简化您的生活。在MS Excel中准备查询也一样。

#19

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.

通常，我使用视图来简化操作，从存储在多个表上的某个实体获取扩展的细节(消除代码中的大量连接以增强可读性)，有时还在多个数据库上共享数据，甚至使插入更容易阅读。

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

我的产品数据库中只有大约10个视图。我一直在用一些列。我使用的一个集合来自7个表，有些表具有外部连接，而不是不断重写，我只需要在select中调用该视图，并进行一个或两个连接。对我来说，这只是节省时间。

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

我创建了一个xxx，它映射了主表(如产品表)和引用表(如ProductType或ProductDescriptionByLanguage)之间的所有关系。这将创建一个视图，该视图将允许我检索一个产品，以及从其外键转换到其描述的所有细节。然后我可以使用ORM创建对象来轻松地构建网格、组合框等。

#22

Think of it as refactoring your database schema.

可以将其视为重构数据库模式。

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

我认为首先要隐藏查询的复杂性。它非常适合视图。当我们对数据库表进行规范化时，它是如何增加的。当表的数量增加时，获取数据是非常困难的。所以最好的处理方法是遵循视图。如果我错了，纠正我。

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

我们创建视图来限制或限制访问表中的所有行/列。如果所有者希望只共享特定或有限的行/列，那么他将创建一个包含这些列的视图。

#1

409