SQL 视图的缺点是什么？

What are the disadvantage of SQL views?

提问by Vikas

Recently I faced an interview and I was asked the above question.

最近我遇到了一个面试，我被问到了上面的问题。

I was dumb when I think about it.

一想到这里，我就傻了。

Interviewer said:

面试官说：

All people are saying views have lots of advantages but I find no disadvantages, why so?

所有人都说视图有很多优点，但我发现没有缺点，为什么会这样？

EDIT

编辑

Based on the answers of all experts, I think I can summarize it:

根据各位专家的回答，我想我可以总结一下：

1. When table is dropped or modified, view becomes inactive, it depends on the table objects.
2. Not all the time we can perform DML statements, as normally views are made for complex query and depends on more than one table. So there is more possibilities of violating your database constrains while performing DML statements.
3. As views are normally used for a complex static query, not all the times we can have same situation to use that static query. For example; If you are querying over view, then it looks like you save time , but if you are looking for few information from view, then you may face preformace degradation problem.

1. 当表被删除或修改时，视图变为非活动状态，这取决于表对象。
2. 并非所有时间我们都可以执行 DML 语句，因为通常视图是为复杂查询创建的，并且依赖于多个表。因此，在执行 DML 语句时，违反数据库约束的可能性更大。
3. 由于视图通常用于复杂的静态查询，并非所有时候我们都可以在相同的情况下使用该静态查询。例如; 如果您在视图上查询，那么看起来您可以节省时间，但是如果您从视图中查找的信息很少，那么您可能会面临预制件退化问题。

回答by Geeth

1. when table is not there view will not work.

2. dml is not possible if that is more than one table.

3. it is also database object so it will occupy the space.

4. When table is dropped view becomes inactive.. it depends on the table objects.

5. Querying from view takes more time than directly querying from the table

1. 当表不在那里时，视图将不起作用。

2. 如果多于一张表，dml 是不可能的。

3. 它也是数据库对象，因此会占用空间。

4. 当表被删除时，视图变为非活动状态。它取决于表对象。

5. 从视图查询比直接从表查询需要更多时间

回答by MLT

Most of the things I would say have already been covered, I would add this though.

我想说的大部分内容都已经涵盖了，不过我还是要补充一下。

Views are useful in many situations but making too much use of them can be a mistake because they tie your hands in terms of query structure. Often when your overall query contains several views within it (especially when views are layered), or when a view has been adapted for a slightly different purpose to what was originally intended, you find that there is a far better way of writing the query if you just expand the views and change the logic.

视图在许多情况下都很有用，但过多地使用它们可能是一个错误，因为它们在查询结构方面束缚了您的手。通常，当您的整体查询中包含多个视图时（尤其是当视图分层时），或者当视图的用途与最初的意图略有不同时，您会发现有更好的编写查询的方法，如果您只需扩展视图并更改逻辑即可。

回答by onedaywhen

Like any tool, views can be misused particularly when you're not sure how they should be used properly.

与任何工具一样，视图可能会被滥用，尤其是当您不确定如何正确使用它们时。

Chris Mullins definesthree basic view implementation rules:

Chris Mullins 定义了三个基本的视图实现规则：

• The View Usage Rule
• The Proliferation Avoidance Rule
• The View Synchronization Rule

• 视图使用规则
• 避免扩散规则
• 视图同步规则

If you don't get these things right you get code maintenance problems, performance problems, security problems, etc.

如果你没有把这些事情做好，你就会遇到代码维护问题、性能问题、安全问题等。

回答by sanjeev

1) when a table is dropped ,view will be affected. 2) If column name is renamed then view will show exception "Invalid column name" . 3)When view is created for large table ,it occupies some memory .

1）当一个表被删除时，视图会受到影响。2) 如果列名被重命名，则视图将显示异常 "Invalid column name" 。3）当为大表创建视图时，它会占用一些内存。

回答by malepati varaprasad

1. If you write some complex views, while querying simple data from view it will take more time.

2. It affects performance. Querying from view takes more time than directly querying from the table.

3. If view would join more than one table, you may not perform any DML operations.

4. Table dependence- if you change table, you need to updated view also.

1. 如果你写一些复杂的视图，从视图中查询简单的数据会花费更多的时间。

2. 它会影响性能。从视图查询比直接从表查询花费更多的时间。

3. 如果视图将加入多个表，则您不能执行任何 DML 操作。

4. 表依赖——如果你改变表，你也需要更新视图。

回答by James Black

The only disadvantage I can think of is that you may force the user to join several views to get the data in a way that is useful to them, as you now have largely static queries.

我能想到的唯一缺点是，您可能会强制用户加入多个视图，以便以对他们有用的方式获取数据，因为您现在主要是静态查询。

So, if the view was created one time and it is expected to never change, you may end up with a preponderance of views that creates a maze for the user to navigate through, so there should be some process to update views, to keep them useful as needs change.

因此，如果视图是一次性创建的并且预计永远不会改变，那么您最终可能会得到大量视图，这些视图会为用户导航创建一个迷宫，因此应该有一些过程来更新视图，以保留它们随着需求的变化很有用。

回答by PP.

A view permits the DBA (database administrator) to tightly control what goes in and comes out of a database.

视图允许 DBA（数据库管理员）严格控制进出数据库的内容。

In banking a view is often used to permanently keep track of every change made to the table. The real table typically contains additional columns that are not seen by "the view" such as:

在银行业务中，视图通常用于永久跟踪对表所做的每个更改。真实表通常包含“视图”看不到的附加列，例如：

• last-modified (when the last change was made)
• last-action (update/delete/add)
• last-actioner (person who updated the row)

• last-modified（最后一次修改的时间）
• 最后一个动作（更新/删除/添加）
• last-actioner（更新行的人）

So when displaying the view of the table only the latest update or add of any row is displayed. However the table still contains every existing change and row deletion.

因此，当显示表的视图时，只显示任何行的最新更新或添加。然而，该表仍然包含每个现有的更改和行删除。

The major downside to a view is to the user of the table (the application programmer) who cannot directly change the underlying table (for performance reasons, for example). Additionally it does create more work for the database administrator. You might also consider the extra CPU burden placed upon the server - particularly if it is utilised by many clients.

视图的主要缺点是表的用户（应用程序程序员）无法直接更改基础表（例如，出于性能原因）。此外，它确实为数据库管理员创造了更多的工作。您可能还会考虑服务器上的额外 CPU 负担 - 特别是当它被许多客户端使用时。