Table vs View is a topic of debate among DBAs and beginners because the similarities they both share . We will discuss various aspects of both tables and views . Such as:
- Difference between Table vs View.
- Table vs View Performance.
- Advantages of Views in SQL
Difference between Table vs ViewA table is where you store your data. The table actually occupies space on disk and is created by defining columns , data types & Constraints . They are the entities and responsible to store data . From our sample university database . Below is the Course Table This table is created by a following DDL statement :
CREATE TABLE [dbo].[Course]( [CourseID] [int] NOT NULL, [Title] [nvarchar](100) NOT NULL, [Credits] [int] NOT NULL, [DepartmentID] [int] NOT NULL, )View is a created by DML statement (SELECT Statement) which doesn’t occupy space on disk . View is literally a query . Nothing more nothing less . What it does is it executes the query defined in it’s structure and display the result which looks a lot like table. Run this query in our Sample database
CREATE VIEW CourseView AS SELECT [CourseID] ,[Title] ,[Credits] ,[DepartmentID] FROM [School].[dbo].[Course]
SELECT * FROM [School].[dbo].[CourseView]This query will produce the same result Difference is in the behaviour these two behave and the purpose they serve. We create views to avoid writing same piece of code multiple times on multiple places . For instance : You want to see the departments name against the Department ID with courses from above table . The query will be as follows :
SELECT [CourseID] ,[Title] ,[Credits] ,D.[DepartmentID] , D.Name FROM [School].[dbo].[Course] C LEFT JOIN [School].[dbo].Department D ON C.DepartmentID=D.DepartmentID
When to use Views?
Now you will write this query once , execute it and then what will you do if you need this one more time? will you write this piece of code again? What if your information is scattered over 10 different tables & you need to execute this in 20 different places? This Scenario is common in Enterprise Applications. You may say , this is not a problem because we can save the files and call them every time. Problem is that when a query executes , it goes through various stages in Database engine .
Two main stages are
- Query Parsing
- Query Plan
If you run this query 20 times. Your database engine will parse the query & make the query plan 20 times ( I know smart engines may cache frequent queries -for the sake of argument , let’s think they won’t) . Therefore , your query will acquire & release locks approx. 20 times . Therefore ,This will create bottlenecks for other operations as well.
Instead of all this just create a VIEW . This way for the same query , database engine will create query plan and parse the query only once. Then whenever you call view it will simply execute the query to return results.
CREATE VIEW CourseView AS SELECT [CourseID] ,[Title] ,[Credits] ,D.[DepartmentID] , D.Name FROM [School].[dbo].[Course] C LEFT JOIN [School].[dbo].Department D ON C.DepartmentID=D.DepartmentIDNext time if we have to execute it , we will simply call CourseView to display result. Views can be updated , deleted & inserted though with a lot of restrictions. The base table for these operations should be same . More complex the view more difficult it is to do DDL operations.
CAN we have insert or update statement in view?No we cannot do this . For such scenarios , use Stored Procedures
CAN VIEWS HAVE INDEXES?Yes , index views are called materialised view. Very useful for reporting purposes & highly functional . We will soon write on materialised views as they are related to query optimisation section . The series is in the queue . Table vs View Performance This question is frequently asked in interviews & comes up frequently in our daily work. Answer to the question is as dynamic as it can be. This is one of those questions that doesn’t have one right answer because –It Depends– If the table and the view have the same structure and data in it, a view is slower than a normal query, because the view has to be generated from the table every time you query it. This is a step which is not necessary in a normal query. Therefore, view has normally only a piece of data from a table, which in turn makes view faster than a normal query. To further make it easy for you remember this
- It depends on the query- How well written it is.
- Do not use like operator in SQL Views because It will take millions of years if you’re data is big
- It depends on how many Joins are use to fetch data. Normally , if there are more tables . DBAs usually use multiple views to optimise speed. For example if the information is coming from 15 different tables. DBA may use 8 tables in one View lets call it View A and then later use the View A to form View B with remaining 7 tables.
- It depends on how well indexed your base table is. If the base table has poor index . View can’t do much.
- Advantages of Views in SQL
- Simplify the complex queries .For instance , reporting team wants to access the data. Usually , well managed databases can be large & complex . Sometimes , DBAs create views and make the data available hiding the complex underlying structure for other users. Though this practice is not recommended but still in the wake of meeting deadlines . Developers can be rough on naming columns and tables conventions.
- Computed Columns /Derived Columns can be created in Views. Great way to display reports. Write queries on multiple tables and create bulky reports with simple Views
- A database view provides extra security layer. Security is a vital part of any relational database management system. The database view offers additional protection for a database management system. The database view allows you to create the read-only view to expose read-only data to specific users. Users can only retrieve data in read-only view but cannot update it because he wont’t have access to data.
- Limits the access of data to certain users. For instance , your client may not want developers to access finance data or sensitive information.Because , this may create data breach. Hence , they need to have limited access. This can be easily manage with Views.