Introduction
Recently I've had to interview some candidates having 5+ years of experience in software development. They were not dedicated DB developers but full stack developers with good experience in SQL programming. One of my questions was regarding "What are Views & Why/When to use them". The responses were surprising for me. Therefore I am writing this post to share my personal experience with Views.
What are Database Views?
They are database objects like other objects; e.g. Table, Procedure, Function. A view basically encapsulates a SELECT statement. This statement can be simple or quite complex containing multiple joins or combination of multiple SELECT statements using UNION ALL etc. We use view in our queries like we use Table in our queries.
For example: Let say we want to create a view vwMyStudents which exposes ID & Name columns of Students table. Here is an example of how we can do this in SQL Server.
- Create View vwMyStudents
- AS
- Select ID, Name from dbo.Students
Now we can use vwMyStudents in our queries. For example
- Select * from vwMyStudents
The above query will actually execute (Select ID, Name from dbo.Students) to return data. Remember, view is just a wrapper around a query. It is possible that our actual table has many columns but we've exposed specific columns in view.
Note
View doesn't mean read-only. Take it from User interaction perspective. We may use Update/Insert/Delete with View depending on Permissions we have on target table/s. Here is an example of Delete operation on view.
- Delete from vwMyStudents Where ID IN (2,3)
Does View store a local copy of data?
No, view is just a wrapper on a query. It doesn't store a copy of data. When we query on a view, it actually executes the encapsulated query on actual tables to return data. Simple. But we've another type of Views which are called Materialized Views. A materialized view stores copy of data came by encapsulated query and returns data directly from that stored copy instead of going to actual table/s. There are some constraints we need to follow to create a materialized view. Please read more about it as it may be useful in some situations.
Does View Give any performance benefit?
If we don't go into more detail, we can say No. If we use materialized view, we may get performance benefit as it would be having local copy based on our encapsulated query which may have filters in it. Plus if we have some complex query, DBMS may generate an execution plan for view and may use that again & again when view is used. That may be a benefit from a performance perspective.
Let’s check some cases where we may use Views. (Please add in comments if you have used in some other case)
Case 1
We want to expose only Specific columns of a table so we can create a view on that table and may use that view instead of accessing actual table.
Case 2
We don't want to provide direct access of table to users or we don't want user to see data of all columns/rows of a table. We may create a view and expose only relevant column (in Select statement) or rows (by applying filter in where clause). Then we may provide access of view instead of table/s. For example, employee table contains salary information. We may create a view which only exposes basic information of people having specific designation.
Case 3
In situations when we have to join multiple tables and we have to use this same query with joins on multiple places in our SQL programming, we may create a view to encapsulate those joins/complex select logic. Then we may use that view directly in our queries instead of writtng joins logic again & again. In future if we've to add extra filter in that logic, we can directly change the view instead of updating on multiple places.
Case 4
We may have multiple views on same table/s but for different purposes. For example we've an Employee table and we may create two views. First is exposing all employees. Second is exposing only Active employees. So instead of applying Active check separately, relevant view can be used.
Case 5
Accessing objects from cross databases (or from Linked servers), we may use views instead. We should not expose name of cross databases or linked servers in our SQL scripts but we should create views to encapsulate actual database & object name and should use that view in our scripts. For example, we've another database (ems) which contains employee information so instead of using ems.dbo.employee in our scripts, we may create a view around it
- Create view dbo.vwEmsEmployee
- As
- Select * from ems.dbo.Employee
Now in our all scripts, we would be using vwEmsEmployee. This gives us many benefits. For example, if in our production environment, we've different name for ems database, we just need to update that in View, not anywhere else.
Case 6
We may also create views for our own help. To debug, we have our own queries which we just execute and get results. We may create views which give us to the point results (relevant columns) and with predefined filters applied.
Conclusion
Views allow us to create another layer to "view" things according to our understanding/ease. Think it from "user interaction" perspective just like we've a data entry form (UI) which might be adding data in multiple tables at the backend. Whatever table name is or whatever normalization is applied, we may create views to see relevant information directly instead of applying unnecessary joins/filters.