What are the disadvantage of SQL views?

Question

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.

Answer 1

Hi

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

Answer 2

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

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)

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Chris Mullins defines three basic view implementation rules:

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