Working with View in SQL Server

Introduction

View is a virtual table which use to display the data from one and more than one table .View use for security and restriction of acess the data. Suppose there is one software company X and the client of X company is Y. Y would like to know the all details of Employee of X. But Company X would not like to share salary details to Y. In this case company will be create one view excluding Salary column and they will show the details to their client Y.

Before discussing about view we will be discuss the task which has to perform in database.

Creating table in database
Creating View
Creating table in database

Step 1: Go to start button and select SQL server management studio as showing in below figure.

Now click on SQL server management studio and go to object explorer.

Step 2: Create a new database by using.

Create database database_Name

In this Discussion I have created a database as name DVView as showing in below figure.

Now go to table and create two table:

Employee
Branch

created a database

Create table employee with 4 column as:

create table Employee(eid int primary key,ename varchar(20),designation varchar(20),mgrid int)

Now insert the values in table Employee:

insert into Employee values(1,'Sandeep','DBA',2)
insert into Employee values(2,'Rakesh','.Net Developer',1)
insert into Employee values(3,'sohan','Java Developer',1)
insert into Employee values(4,'Suresh','Software Trainee',3)
insert into Employee values(5,'Mohan','Software Trainee',4)

After inserting the data in table run the query:

select * from Employee

Now output will be as below figure.

data in table

Again create one more table as name Branch.

Create table Branch.

Now insert the values in table branch.

insert into branch values(1oo,'Hyd',1)
insert into branch values(1o1,'DEL',2)
insert into branch values(1o3,'Bag',3)
insert into branch values(1o4,'Chn',4)
insert into branch values(1o5,'Nod',5)

Now run the query.

Select * from branch

Output will be display as below figure.

Output

Creating view

Syntax for creating View(ANSI QUERY):

Createview viewName
As
Selectcolumns
Fromtables Name
Where condition
Go

Example:

createview viewEmp
as
select e1.ename,e1.designation,e1.mgrid,b1.bid,b1.bname
from Employee e1,branch b1
where e1.eid=b1.eid
go

While we will be execute the view:

select*from viewEmp

Output will be like

Figure: Output by using ANSI Query

Syntax for creating view(TSQL Query)

createview viewName
as
selectcolumn name
from table1
jointable 2
on condition
go

Example

createview viewe
as
select e1.ename,e1.designation,e1.mgrid,b1.bid,b1.bname
from Employee e1
join branch b1
on e1.eid=b1.eid
go

While we will be execute the view:

select*from viewEmp

Output will be like:

Figure: Output by using TSQL

Recap

View is a virtual table which uses to display the data from one and more than one table. Also it use to restrict the access of data.