什么时候以及为什么有些人决定他们需要在他们的数据库中创建一个视图?为什么不运行一个普通的存储过程或选择?


当前回答

几个原因: 如果您有复杂的连接,有时最好有一个视图,这样任何访问都会有正确的连接,开发人员不必记住他们可能需要的所有表。通常情况下,这可能用于财务应用程序,其中所有财务报告都基于同一组数据,这是非常重要的。

如果你有用户,你想限制他们可以看到的记录,你可以使用一个视图,只给他们访问视图,而不是底层表,然后查询视图

Crystal报表似乎更喜欢使用视图来存储过程,因此编写大量报表的人倾向于使用大量视图

视图在重构数据库时也非常有用。您通常可以通过创建一个视图来隐藏更改,以便旧代码看不到它。阅读重构数据库的相关内容,了解它是如何工作的,因为这是一种非常强大的重构方式。

其他回答

以下是两个常见的原因:

你可以用它来保证安全。在主表上不授予权限,创建限制列或行访问的视图,并向用户授予查看视图的权限。

你可以为了方便使用它。将视图中经常使用的一些表连接在一起。这可以使查询保持一致并更容易。

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

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

关注特定的数据 视图允许用户专注于他们感兴趣的特定数据和他们负责的特定任务。不必要的数据可以从视图中删除。这还提高了数据的安全性,因为用户只能看到视图中定义的数据,而不能看到底层表中的数据。有关将视图用于安全目的的更多信息,请参见将视图用作安全机制。

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.

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

自定义数据 视图允许不同的用户以不同的方式查看数据,即使他们同时使用相同的数据。当具有许多不同兴趣和技能水平的用户共享同一个数据库时,这是特别有利的。例如,可以创建一个视图,该视图仅检索与客户经理打交道的客户的数据。该视图可以根据使用该视图的帐户经理的登录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.

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.

如果满足以下条件之一,分区视图是可更新的: 在视图上定义了一个INSTEAD OF触发器,其逻辑支持INSERT、UPDATE和DELETE语句。

视图和INSERT、UPDATE和DELETE语句都遵循为可更新分区视图定义的规则。有关更多信息,请参见创建分区视图。

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

我认为第一个。隐藏查询的复杂性。它非常适合于视图。当我们规范化数据库表时如何增加。现在,当表数量增加时,获取数据是非常困难的。所以最好的处理方法是遵循视图。如果我错了,请纠正我。