View in SQL Server - WebNetGuru.com

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 >
AS
(
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
AS
(
SELECT sm.StudentName, sm.Std, sa.StudentCity
FROM tblStudentMaster sm
JOIN tblStudentAddress sa
ON sm.StudentName = sa.StudentName
WHERE sm.Std = 10
)


Example

SELECT * FROM viewStudent

Output

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'

Output

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