SQL Server Interview Questions on Views - Part 2
Can you create a view based on other views?
Yes, you can create a view based on other views. Usually we create views based on tables, but it also possible to create views based on views.
Can you update views?
Yes, views can be updated. However, updating a view that is based on multiple tables, may not update the underlying tables correctly. To correctly update a view that is based on multiple tables you can make use INSTEAD OF triggers in SQL Server. Click here for a real time example, that we have already discussed in SQL Server Interview Questions on triggers article.
What are indexed views?
Or
What are materialized views?
A view is a virtual table, it does not contain any physical data. A view is nothing more than compiled SQL query. Every time, we issue a select query against a view, we actually get the data from the underlying base tables and not from the view, as the view itself does not contain any data.
When you create an index on a view, the data gets physically stored in the view. So, when we issue a select query against an indexed view, the data is retrieved from the index without having to go to the underlying table, which will make the select statement to work slightly faster. However, the disadvantage is, INSERT, UPDATE and DELETE operations will become a little slow, because every time you insert or delete a row from the underlying table, the view index needs to be updated. Inshort, DML operations will have negative impact on performance.
Oracle refers to indexed views as materialized views.
Only the views created with schema binding, can have an Index. Simply adding WITH SCHEMABINDING to the end of the CREATE VIEW statement will accomplish this. However, the effect is that any changes to the underlying tables which will impact the view are not allowed. Since the indexed view is stored physically, any schema changes would impact the schema of the stored results set. Therefore, SQL Server requires that schema binding be used to prevent the view's schema (and therefore the underlying tables) from changing.
The first index for a view must be a UNIQUE CLUSTERED INDEX, after which, it's possible to create non-clustered indexes against the view.
Indexed Views are heavily used in data warehouses and reporting databases that are not highly transactional.
What are the limitations of a View?
1. You cannot pass parameters to a view.
2. Rules and Defaults cannot be associated with views.
3. The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.
4. Views cannot be based on temporary tables.
If you can think of any other interview questions on views please post them as comments, so they will be useful to other users like you.
Subscribe to:
Post Comments (Atom)
Great advantage of an Indexed View in my opinion. Please correct me if I am wrong.
ReplyDeleteSQL Server can receive a huge performance boost by aggregating data with a view and applying an index. The first index to apply to a view is a clustered index. A clustered index will materialize the view. In other words, the index will persist the data in a view to disk. An indexed view can dramatically decrease the amount of time needed to aggregate data as the index already has the sorted, grouped, and calculated results. The drawbacks to watch for include increased disk space, and more overhead on INSERT, UPDATE, and DELETE statements on the view’s base tables, because the database might need to update the index.
What does "Rules and Defaults cannot be associated with views" mean ?
ReplyDeleteWhile the base tables are undergoing huge updates or deletes or inserts, is it possible to disable re-building the index on the view temporarily?
ReplyDelete