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

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

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 3

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.

Answer 4

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.