View in SQL Server

View

A view is a windows through that user can access the data from the table .a view has the following 3 main purpose.

1. Restrict the user from accessing only specific rows of the table.
2. Restrict the user from performing insert ,update and delete only on specific rows of the table.
3. Simplify the complex queries like "set operators",joins and sub query.

Creating View

To create the view use the create view command that has the following syntax:

create view <view Name> [{<Columns List>}]
[with [encryption],[[schemebinding]]
as <select statement>
[with check option]

For example, create a view on emp that provides access to only the employee working in department 10.

Create the view empview as:

select * from emp where deptno =10

Once the view is created it can be used like a table and the same as for a table, you can perform select, insert, update and delete on the view.

But the view doesn't contain any data physically whenever you execute a select statement on the view. It will internally execute a select statement of the view that gets the data from the table and displays it to you. In the same way when you do an insert, update or delete on the view then the rows will be inserted, updated or deleted (respectively) from the table on which the view is created.

For example, the following example returns data from the view empview:

select * from empview

For example, the following example inserts rows into the view empview:

insert empview values(1001,'abhijit','Sales')

The Check condition specified in the select statement of the view during insert, update and delete also when creating the view you must specify the check option.

For example, the following example of alter of the view empview includes the check option.

The alter view empview is as:

select * from emp where deptno=10 with check option

Next the following describes Simple and Complex views.

1. Simple view

A view is called a simple view when it doesn't satisfy any of the following 3 conditions.

2. A view is called a complex view when it does satisfy any one of the following 3 conditions.

1. The view is created from multiple tables.
2. A select statement of the view contains an aggregate function or group by.
3. A select statement in the view contains a keyword.

For example, create a view that displays the details of the employee along with department name and location in which the employee is working, the mananger name of the employee and the grade of the employee based on salary.

The create view empdetails is as:

select e.empid,e.empname,e.mgr,m.ename as mananger,e.sal ,losal,hisal,grade ,e.deptno,dname,loc from emp e inner join emp M on e.mgr=m.empno
innerjoin salgrade on e.sal betwen losal and hisal innerjoin dept d on E.deptnO=d.deptno

select * from empdetails

Getting list of view

To get the list of views available in the DB use any of the following two select statements.

select * from sys.views
select * from INFORMATION_SCHEMA.VIEWS

To get the list of tables on which a view is created use the following select statement.

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where VIEW_NAME='empview'

Getting view defination

To get the source code of a view use a Stored Procedure with the following syntax.

sp_helptext <viewname>

For example, the following example gets the definition of the view with the name empdetails.

sp_helptext 'empdetails'

Encryption

When you don't want to display the source code of your view to anyone then when creating the view specify the encryption option.

For example, the following example alters the empview to include the encryption option.

The alter view empview with encryption is as:

select * from emp where deptno=10 with check option

Scheme Binding

When creating a view if you don't want to allow the user to alter the structure of the columns that are selected in the view then use the schema binding option when the creating view.

When a view is created using the schema binding option then within the select statement of the view you must specify the tablename in the form of schemaname .tablename.

For example, the following example creates a view that provides access to only employee no, employee name, job and salary of all employees using the schema binding option.

create view empview with schemabinding as select empno,ename,job,sal from dbo.emp

Delete View

To delete a view use the drop view command that has the following syntax:

drop view <view name>

For example, the following command deletes the view empview.

drop view empview