Table vs View – All You Need To Know

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:

  1. Difference between Table vs View.
  2. Table vs View Performance.
  3. Advantages of Views in SQL

In SQL Beginner’s Guide we have developed basic understanding about views. This article will discuss advance aspects of the topic. In case you need some background reading

Read : What is View in SQL ?

Difference between Table vs View

A 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

Table Vs Views
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

SQL Subquery
CourseView

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

  1. Query Parsing
  2. 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.DepartmentID

Next 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

  1. It depends on the query- How well written it is.
  2. Do not use like operator in SQL Views because It will take millions of years if you’re data is big
  3. 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.
  4. It depends on how well indexed your base table is. If the base table has poor index . View can’t do much.
  5. Advantages of Views in SQL
  6. 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.
  7. 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
  8. 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.
  9. 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.

Table vs View is one of the favourite topics among DBAs. Hopefully , you have had enough content here to understand the difference. In case , you want to add anything . Feel free to comment below!

Advertisements

How Grouping Works in SQL

One of our subscribers reached us and asked How Grouping works in SQL ?  So today , this is what we are going to do . We will discuss in detail about grouping in SQL and how it actually works behind the scenes. Also , this is our first post in Questions category and we will be doing more as there are bundle of requests in the queue. So if you want to contribute and like us to write something for you , please do not hesitate.

How Grouping works in SQL?

Grouping summarises the whole data set into smaller chunks of data where each row represents a group . Group is when we combine a group of things, elements under some rule. While working with SQL reports , a lot of times we will come across scenarios where we want to summarise the data. For this purpose we use MS SQL Group By Clause

Also see : MS SQL Group By Clause

because this clause summarise the data into groups . Time to dig in now.

Prepare the data

Examples and queries that we will see here will run on our University Management System database . If you want to use these SQL queries for practice , create the same database , you need to just copy paste the script from here .

Case Study : Grade Reports from University Management System

We are asked to get the top 5 students with top grades . One best query writing practice is to try and visualise the end result and then build the query around it. To do this , you need first to see the underlying data. If you run the below SQL query , you will have a good look at the data we have.

SELECT P.FirstName,C.Title,Grade
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID

How Grouping Works in SQL

 

Remember our objective. We need first five students with top marks out of these forty rows. Give a brief and a long look at the table. You are seeing three columns here. Student names , course names and respective grades. Objective is to find the top 5 students .

Let’s break up the table and visualise what is we want. We want two things

  1. Student Names
  2. Student Grades

Now it is clear that we want top achievers which are actually the students who have obtained highest marks. How can you find that? By adding marks in all courses of each student.

Aggregate functions in SQL will help us to add the grades . See the Grades columns , its a column of numbers.

How Grouping Works in SQL examples

Can you guess of which aggregate functions in SQL we will use? SQL Count () or SQL Sum() ?

See : Difference Between SUM() & Count ()

It has to be SQL Group By SUM  which will do the job for us. Now , we have already determined that when Grouping clause is in effect each row represents
a group . So to achieve the objective remove Course column from the query

Read : MS SQL Group By Clause 


SELECT P.FirstName,GradeFROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID

How Grouping Works in SQL example 2

See the table again. Let’s find total marks of each student. Run this query


SELECT P.FirstName,SUM(GRADE) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID


It will give us out this

How Grouping Works in SQL example 2

We have applied the aggregate function but haven’t told our SQL Server on what basis it should be grouped. We want to group the grades against each student that is why we are writing FirstName in query . To apply SQL aggregate function of SUM on grades column you need to put First Name in Group By Clause. When you do that it will create groups for each student like this


SELECT P.FirstName,SUM(GRADE) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID
GROUP BY P.FirstName

How Grouping Works in SQL example 4

All students are appearing only once because all same elements are grouped together . Each student was taking up more than one course so SQL Server first combines the elements into group. In our case , unique student names.  Now it will apply SQL Sum for each group. Combine both steps and SQL Server handles bot these steps like this

How Grouping Works in SQL example 5

This is the final output you will see

How Grouping Works in SQL example 6

See how SQL Server reduces the table into groups and then by the use of aggregate functions in SQL it gives us results for those groups. We will use TOP clause with Order By Clause to find the results

RESULT QUERY


SELECT TOP (5) P.FirstName,SUM(GRADE) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID
GROUP BY P.FirstName
ORDER BY SUM(GRADE) DESC

How Grouping Works in SQL example 7

If you use TOP clause always use Order By clause with it to get correct data. Because , results can be random , so if you apply Order By then the order will be consistent. Topic for another day!

ALL YOU NEED TO KNOW ABOUT GROUPING

  1. Grouping is applied on all columns as combination in the same order they appear in query.
  2. Each row represents a unique row.
  3. Filtering on grouping is done by using HAVING Clause
  4. Use ORDER BY clause to arrange the data set in a order.

if you can find more points about grouping , do comment and we will add this in our list.

I hope now you understand about How Grouping Works in SQL because this has been a brief articleNow if you like this article , do share with your friends and colleagues and recommend us . Oh! and if the sponsored content is bothering you , apologies for that too because it’s the only source of revenue for this blog.

See you with next post very soon!