Introduction
In this article, I describe Views in SQL Server. This is a simple topic. I hope
this article will help you just like my Windows Store articles. Please give me
your valuable suggestions and feedback to improve my articles.
What is a View
Views are database objects which are like virtual tables that have no
physical storage and contains data from one table or multiple tables. A View
does not have any physical storage so they do not contain any data. When we
update, insert or apply any operation over the View then these operations are
applied to the table(s) on which the view was created.
Types Of View
- System View
- User Define View
User
Defined Views are important so I describe only User Defined Views. They are of
two types:
- Simple View
- Complex view
Simple View:
When a View is created on a single Table than it is called a Simple View. We
can apply all operations on a Simple View that we can apply on a table.
First of all we create a table on which we create a view.
create table emp(empId int,empName varchar(15),empAdd varchar(15))
Now insert data by the following code.
insert into emp
select 1,'deepak','UA'union all
select 2,'Middha','Punjab'union all
select 3,'d','Delhi'union all
select 4,'gourav','Noida'union all
select 5,'deepakia','Laksar'union all
select 6,'Deep','Haridwar'
Table:
Creation of a simple view:
create view v1
as
select * from emp
Operation on view:
See all the data of the view:
select * from v1
Output:
See the specific data of the view:
select * from v1 where empId=4
Output:
Insertion:
insert into v1 values(7,'raj','canada');
Output:
Updating:
update v1 set empAdd='usa'where empId=7
Output:
deletion:
delete from v1 where empId=7
Output:
Renaming:
exec sp_rename 'v1','v11'
Logic of the View:
exec sp_helptext v1
Output:
Dropping the View:
drop view v1
Encrypted View:
create view v1
with encryption
as
select * from emp
Complex view:
Views created on more
than one table are called Complex View. We cannot perform all operations of a
table on a Complex View.
First of all we create
a table and insert some data :
create table empStatus(empId int,empStatus varchar(10))
insert into empStatus
select 1,'active'union all
select 2,'inactive'union all
select 4,'active'union all
select 5,'inactive'union all
select 6,'active'
Table:
select * from empStatus
Output:
Creation of complex view:
create View VComplex
as
select e.empId,e.empName,e1.empStatus from emp e inner join empStatus e1 on e.empId=e1.empId
See all the records:
select * from VComplex
Output:
See specific record:
select * from VComplex where empId=4
Output:
If we try insert,
update or delete in a complex view then it shows an error as in the following:
insert into vcomplex values(11,'d','inactive')
Output:
Encryption of the Complex View:
create View VComplex
With encryption
as
select e.empId,e.empName,e1.empStatus from emp e inner join empStatus e1 on e.empId=e1.empId
Summary
In this app I described Views in SQL Server. I hope this article has helped you
to understand this topic. Please share if you know more about this. Your
feedback and constructive contributions are welcome.
