View in SQL Server -

View in SQL Server

View in SQL Server allows to create the logical table in SQL Server. View in SQL Server may contain data from one or multiple physical table . Let's check out how to create View in SQL Server and properties of View

When to use the View in SQL Server?

1) In database having table called tblUserDetails, tblUserDetails having more than 200 columns and for login purpose we need only two columns to verify the user that is UserName and Password. So, we can create view with 2 columns and access data from view.
So instead of searching record on such huge physical table, we can create one logical table of two, columns that are UserName and Password. Because searching record on 2 columns better than searching record in 200 columns.

2) SQL View is also use for security purpose, we can create view and can give required columns access to particular user instead of accessing whole physical table.

Below is the structure of the SQL View. But every time it is not necessary to create SQL View using more than one table. We can create the SQL View on one table as well.

create view in sql server

Example of View in SQL Server.

We have two tables that is tblStudentMaster and tblStudentAddress which contain the data regarding Student.
We store the student class, city, contact details in two tables. Let find out the 10th class student name, class, and city.
And student contact is secure data, so we don't want to allow developer to access that data.
So, we all achieve this by SQL View.

example of view in sql server

Syntax to create View in SQL Server

CREATE VIEW < View_Name >
SQL Server Select / Join Statement.

If we check below example we create the two view using two physical table that is tblStudentMaster and tblStudentAddress master.

Example of View in SQL Server

CREATE VIEW viewStudent
SELECT sm.StudentName, sm.Std, sa.StudentCity
FROM tblStudentMaster sm
JOIN tblStudentAddress sa
ON sm.StudentName = sa.StudentName
WHERE sm.Std = 10


SELECT * FROM viewStudent


create view in sql server

Can we perform DML operation on View in SQL Server ? And does it affected on physical table?

Answer is Yes. We can perform DML operation on SQL View because SQL View is logical table. Once we UPDATE / DELETE data then it gets affected on main / physical table.

Find below example.

Example Update View

UPDATE viewStudent SET StudentCity = 'Goa'
WHERE StudentName = 'Gopal'


update view in sql server

Drop View in SQL Server

Example Update View

DROP VIEW viewStudent

To read more about view in SQL Server refer Microsoft.
Move Top