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.

select SQL server management studio

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.

database as name DVView

Now go to table and create two table:

  1. Employee
  2. Branch

created a database

Create table employee with 4 column as:

  1. create table Employee(eid int primary key,ename varchar(20),designation varchar(20),mgrid int)  
Create table employee

Now insert the values in table Employee:
  1. insert into Employee values(1,'Sandeep','DBA',2)  
  2. insert into Employee values(2,'Rakesh','.Net Developer',1)  
  3. insert into Employee values(3,'sohan','Java Developer',1)  
  4. insert into Employee values(4,'Suresh','Software Trainee',3)  
  5. insert into Employee values(5,'Mohan','Software Trainee',4)  
After inserting the data in table run the query:
  1. select * from Employee  
Now output will be as below figure.

data in table

Again create one more table as name Branch.

Create table Branch.

Create table Branch

Now insert the values in table branch.
  1. insert into branch values(1oo,'Hyd',1)  
  2. insert into branch values(1o1,'DEL',2)  
  3. insert into branch values(1o3,'Bag',3)  
  4. insert into branch values(1o4,'Chn',4)  
  5. insert into branch values(1o5,'Nod',5)  
Now run the query.
  1. 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:
  1. createview viewEmp  
  2. as  
  3. select e1.ename,e1.designation,e1.mgrid,b1.bid,b1.bname  
  4. from Employee e1,branch b1  
  5. where e1.eid=b1.eid  
  6. go  
While we will be execute the view:
  1. select*from viewEmp  
Output will be like

Output by using ANSI Query
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
  1. createview viewe  
  2. as  
  3. select e1.ename,e1.designation,e1.mgrid,b1.bid,b1.bname  
  4. from Employee e1  
  5. join branch b1  
  6. on e1.eid=b1.eid  
  7. go  
While we will be execute the view:
  1. select*from viewEmp  
Output will be like:

Output by using TSQL
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.