View in SQL By Sagar Jaybhay 2020

In this article Sagar Jaybhay explain what is View. What is the use of View. what is the advantages of views in SQL server.

View

The view is saved SQL query or we can call it a virtual table.

View In SQL 1
View In SQL 1

We have these 2 tables and by joining these 2 tables we want to output.

View In SQL 2
View In SQL 2

By joining these 2 tables we get the above result. Now we want to create a view, like other create statement like create a table, create procedure we have to create view statement

Below is a query for creating a view

create view empwithdepartment
as
select e.EmpID,e.full_name,e.Salary,e.Gender,d.Department_Name from Employee as e
join Department as d on d.DepartmentID=e.DepartmentID;

View In SQL 3
View In SQL 3

To find a view in the database refer below image

How to find view
How to find view

If you check the above query it just selects query and if you want to get data from the view you will able to treat it as a table and simple select * from view_name; by using this you can get data.

View doesn’t store any data and it is just saved select query.

Advantages of views

  1. It is used to reduce the complexity of database schema
  2. It provides a mechanism to implement column level and row-level security means if you want to give access to certain users with a limited number of rows and columns then put your query in view and given that view name to end-user so that he performs the operation on that considering this is a table. By doing this end-user doesn’t know the underlying base table.
  3. It Is used to present aggregated data or detailed data.

To alter view you can use alter view syntax

Alter view view_name
As
-	your query syntax here

To drop the view you can use

Drop view view_name;


GitHub : https://github.com/Sagar-Jaybhay


Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts