What is the disadvantage of SQL views?

Question

I recently came across an interview and I was asked the above question.

I was dumb when I thought about it.

The interviewer said:

Everyone says that opinions have many advantages, but I do not find flaws, why so?

EDIT

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

• When a table is dropped or changed, the view becomes inactive; it depends on the objects in the table.
• Not all of the time, we can execute DML instructions, since views are usually created for a complex query and depend on more than one table. Thus, there are many possibilities for breaking database restrictions when executing DML statements.
• Since views are typically used for complex static queries, not all the time we can have the same situation for using this static query. For example; If you request a view, then it looks like you will save time, but if you are looking for some information from the view, then you may encounter the problem of deformation of the transformation.

Answer 1

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

Representations are useful in many situations, but excessive use of them can be a mistake because they tie your hands in terms of query structure. Often, when your general query contains several views inside it (especially with multi-level views) or when the view has been adapted for a slightly different purpose to what was originally intended, you will find that there is a much better way to write the request if you simply extend the views and change the logic.

Answer 2

• when the table is missing, the view will not work.

• dml is not possible if it is more than one table.

• it is also a database object, so it will take up space.

• When you drop a table, the view becomes inactive .. it depends on the objects in the table.

• A query from a view takes longer than a query from a table itself

Answer 3

Like any tool, views can be misused, especially if you don’t know how to use them properly.

Chris Mullins defines three basic rules for implementing a view:

• View Use Rule
• Distribution Prevention Rule.
• View Sync Rule

If you do not get these rights, you will get code maintenance issues, performance issues, security issues, etc.

Answer 4

The only drawback I can think of is that you can force the user to join multiple views in order to get the data in a way that is useful to them, since now you have mostly static queries.

So, if a view was created once and it is expected to never change, you may encounter a predominance of views that create a maze for user navigation, so there should be some process of updating the views so that they are useful when changing needs.

Answer 5

The view allows the database administrator (DBA) to strictly control what happens and leaves the database.

In banking terms, the view is often used to constantly monitor all changes made to the table. A real table usually contains additional columns that are not visible by the "view", for example:

• last-modified (last modified)
• last-action (update / delete / add)
• last-actioner (the person who updated the line)

Thus, when displaying a table view, only the last update or addition of any row is displayed. However, the table contains all existing changes and deletion of rows.

The main drawback of the presentation is the table user (application programmer) who cannot directly modify the base table (for example, for performance reasons). In addition, it creates more work for the database administrator. You can also consider the additional processor load placed on the server, especially if it is used by many clients.

Answer 6

1) when deleting a table, the view will be affected. 2) If the column name is renamed, the exception "Invalid column name" will appear in the view. 3) When a view is created for a large table, it takes up some memory.

Answer 7

• If you are writing several complex views, then it will take longer to request simple data from the view.

• This affects performance. A query from a view takes longer than a direct query from a table.

• If a view joins more than one table, you cannot perform any DML operations.

• Dependency table - if you change the table, you need an updated view.