Sameer Gaikwad View in SQL Server mesameergaikwad

   Sameer Gaikwad CRUD Operation SQL stored procedure mesameergaikwad

This article was originally published on mesameergaikwad as Sameer Gaikwad View in SQL Server mesameergaikwad

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 stores and contain 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

1. System View
2. User Define View

User-Defined Views are important so I describe only User Defined Views. They are of two types:

1. Simple View
2. Complex view

Simple View

 

When a View is created on a single Table then 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.

1. create table emp(
2. empId int,
3. empName varchar(15),
4. empAdd varchar(15)
5. )  

Now insert data by the following code. 

1. insert into emp  
2. select 1,'deepak','UA'union all  
3. select 2,'Middha','Punjab'union all  
4. select 3,'d','Delhi'union all  
5. select 4,'gourav','Noida'union all  
6. select 5,'deepakia','Laksar'union all  
7. select 6,'Deep','Haridwar'  

Table

 

 

 

Creation of a simple view

1. create view v1  
2. as  
3. select * from emp  

Operation on view

 

See all the data of the view

1. select * from v1  

 

 

See the specific data of the view

1. select * from v1 where empId=4   

 

 

Insertion

1. insert into v1 values(7,'raj','canada');   

 

 

Updating 

1. update v1 set empAdd='usa'where empId=7   

 

 

Deletion

1. delete from v1 where empId=7  

 

 

Renaming 

1. exec sp_rename 'v1','v11'   

Logic of the View

1. exec sp_helptext v1  

 

 

Dropping the View

1. drop view v1   

Encrypted View

1. create view v1  
2. with encryption  
3. as  
4. 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 

1. create table empStatus(
2. empId int,
3. empStatus varchar(10)
4. )  

   

1. insert into empStatus  
2. select 1,'active'union all  
3. select 2,'inactive'union all  
4. select 4,'active'union all  
5. select 5,'inactive'union all  
6. select 6,'active'  

Table

1. select * from empStatus  

 

 

Creation of complex view 

1. create View VComplex  
2. as  
3. select e.empId,e.empName,e1.empStatus from emp e inner join empStatus e1 on e.empId=e1.empId    

See all the records

1. select * from VComplex  

 

 

See specific record 

1. select * from VComplex where empId=4  

If we try to insert, update or delete in a complex view then it shows an error as in the following

1. insert into vcomplex values(11,'d','inactive')  

 

 

Encryption of the Complex View

1. create View VComplex  
2. With encryption  
3. as  
4. select e.empId,e.empName,e1.empStatus from emp e inner join empStatus e1 on e.empId=e1.empId  

Summary

 

In this article, 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.