A view is a subset of a table and is very much equal to a table and does not contain any memory, that's why it is called "
Virtual Table".
Views and virtual tables represent one or more table data at one time. We must use views when specific security permissions are needed, complex join t-sql queries, or query encryption etc.
Key points about views:
- Multiple views can be created based on one table.
- Views can be defined as read-only or updatable.
- Indexing can be done on view for better performance.
- Insert, update, delete operations can be done on view.
- It restrict the user's from accessing only specific data of the table.
Example showing why do we need view:
Let us suppose that in any organization, when the client is asking for an employee's details on a certain basis, but in this case the organization is not willing to display the personal data of employees like salary and address. So the developer of the organization will create a view from the existing data with salary and address column are not included for the clients. That is how a view can fulfill the requirement.
Example of existing Views in SQL Server - Create View view_name As SELECT column_name(s) FROM table_name WHERE condition
There are mainly 2 types of view - Simple view - It is created by using simple select statements.
- Complex View - It is created by using select statement containing order by, group by and joins.
Example of Creating a Simple view: - Create view Simple_view as Select e.Emp_Id, e.EmployeeName, e.EmpSalary, e.StateId, e.CityId from Employee e where e.EmployeeName LIKE '[C-K]%'
- Select * from Simple_view // to display view data.
Example of Creating a Complex View:
- Create view Complexview as select e.EmpId,e.Name,e.ContactNo, l.Location from Persons e inner join Orders l on e.EmpId=l.EmpId
-
- Select * from Complexview // to display view data.
Inserting values in an existing view:
- Insert into View_Name values(48,'Ajay','India');
Updating values of an existing view:
- update View_Name set Name='Pankaj' where empId=10
Deleting a View:
To delete a view use the drop view command.