VIEWS IN SQL SERVER

Dept Table

Views:

A view is like a window through which user can access a table.

A view has the following three main purposes

1 Restrict the user from performing insert, update and delete only on specific rows of the table

2 Restrict the user from accessing only specific rows of the table.

3 Simplify the complex queries like set operators joins and sub queries when we have to repeatedly execute that statement.

Creating a View:

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

Create view <view name> ((columnist)) [with encryption][,][schema binding]] as <select statement>

[with check option]

Create a view to restrict the user to access only the details of employee working in deptno=20

create view emp20 as select * from emp where deptno=20

 Important:

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

But a view does not contain any data physically and whatever you select any data from a view, it will execute the select statement specify while creating a view gets data from the table and displays it to you.

When you perform insert, update, delete on a view than it will perform that insert, update & delete on the table on which the view was created. Hence the view is called as a logical object and not the physical object.

Views will not store data and will not use memory. Views contains only the structure or columns by using those views will display values from the tables. View will not contain data of its own the data which it displays will be from the table.

 The following examples retrieves data from the view emp20

select * from emp20

Example:

The following example inserts a row into the view emp20

insert emp20 values(1001,'A','clerk',7902,'10/21/1980',2000,NULL,30)

Check option: The above statement will successfully insert a row even if it is not satisfying the condition specified in select statement of the view.

Emp20 that is dept no 20 because the condition is specified in select statement of the view will be checked only during the select and not during insert, update, delete.

To check the condition specify in the select statement of the view during insert, update and delete also you must specify the “check option” while creating the view.

The following example alter the view emp20 to include check option

alter view emp20 as

select * from emp where DEPTNO=20

with check option

Note:

Using grant and revoke we can restrict user operations.

But by using view we can restrict data (particular columns)

Now we will execute the same insert query again and check what happens

insert emp20 values(1005,'A','clerk',7902,'10/21/1980',2000,NULL,30)

Error: The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

Types of Views:

Simple view

Complex view

Simple view:

-        These Views as based upon a single table, which access the data from the single table.

-        They contain a Sub Query which retrieves the data from one base table.

Syntax

Create view <viewname>

(<with option>)

As

Select statement

[where condition]

Example:

createview v1 as

selectempno,ename, sal from emp

When the above view is created select statement is stored in dB, that why view is also called as stored query.

CREATE VIEW SIMPLE_VIEW

AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP

Once the view is created we can access the data from it as if it was a table as following:

    SELECT * FROM SIMPLE_VIEW

SELECT EMPNO, ENAME, SAL, SAL*12 AS [ANNUAL SAL], DEPTNO FROM SIMPLE_VIEW

-We can also perform DML operations on the Simple Views which will effect on the base table.

INSERTINTO SIMPLE_VIEWVALUES(1234, 'SANTOSH', 4300, 20)

DELETEFROM SIMPLE_VIEWWHERE DEPTNO=20

UPDATEEMP SET SAL=5600 WHERE EMPNO=1001

All the columns that are referenced in the view can be modified through the view.

We cannot perform insert operations on the view if he view does not contain all the not null columns of the base table.

Example

createview v4

as

selectempno, sal,deptno from emp

wheredeptno=10

withcheck option

insertinto v4 values(100,900,10)

insert into v4 values(100,900,30)

 

Invalid because the view is created with check option

Complex Views:

-        If the View is based on multiple tables it is a complex view

-        If it is based on a single table with any of the following:

o  Group By Clause

o  Having Clause

o  Group Functions

o  Distinct Function

o  Function Calls

CREATEVIEW EMP_GRADE

    AS

    SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL

    FROM EMPE INNER JOIN SALGRADE S

    ON E.SAL BETWEEN S.LOSAL AND S.HISAL

CREATEVIEW EMP_DEPT

AS

SELECTE.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC

FROMEMP E INNER JOIN DEPT D

ONE.DEPTNO=D.DEPTNO

       CREATE VIEW EMP_GRADE1

    AS

    SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL

    FROM EMPE INNER JOIN SALGRADE S

    ON E.SAL BETWEEN S.LOSAL AND S.HISAL

CREATEVIEW EMP_DESIGNATIONS

AS

SELECTJOB FROM EMP WHERE DEPTNO=10

UNION

SELECTJOB FROM EMP WHERE DEPTNO=20

UNION

SELECTJOB FROM EMP WHERE DEPTNO=30

CREATEVIEW EMP_MAX_SAL

AS

SELECTDEPTNO, MAX(SAL) AS [HIGH SAL] FROM EMP GROUP BY DEPTNO

Create a view that provides access to details of employees along with manager Name of the employee, grade of the employee based on salary ,and deptname and location in which employee is working.

createview empdetails

as

selecte.EMPNO,e.ENAME,e.JOB,e.MGR,m.ENAME as manager,e.SAL ,losal,hisal,grade,e.DEPTNO,d.DNAME,d.LOC

 from emp e inner join emp m on e.MGR=m.EMPNO inner join salgrade on e.SAL between losal and hisal inner join dept d on e.DEPTNO =d.DEPTNO

Note:

When a view is complex view then insert, update, delete are not allowed on that view but when the view is created on multiple tables, than if you write insert, update statement that effect only one table then they are not allowed.

Query fired on view takes more time than query fired on base table. Views degrades performance.

We can also classify views as Updateable Views and Non Updateable Views:

-        A View, which allows manipulations on it, is known as Updateable View.

-        A View, which will not allow manipulations on it, is known as Non Updateable View.

If we want to perform manipulations on the Complex Views we have the following restrictions:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

With Check Option:

-        Forces all data modification statements executed against the view to follow the criteria set within select statement.

-        When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

-         

-        CREATE VIEW SALES_EMP

-        AS

-        SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

-         

-        INSERT INTO SALES_EMP VALUES(1050, ‘RAJU’, 3500, 30)

-         

-        -The above insert statement executes even if it does not satisfy the condition in the View, if this has to be restricted the view has to be created by using With Check Option clause.

ALTER VIEW SALES_EMP

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

WITH CHECK OPTION

View Attributes:

Encryption:

If the view is created with encryption, then view definition will be hidden in information schema views table.

Schemabinding:

-        When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

-        We need to specify the column names individual in the select statement, cannot use “*” in the select statement.

-        All referenced objects must be in the same database.

-        Tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.

-        CREATE VIEW EMP_BIND

-        WITH SCHEMABINDING

-        AS

-        SELECT EMNO, ENAME, JOB, MGR FROM DBO.EMP

-         

After the view is created EMP table cannot be dropped.

When a view is created with schema binding option than it is not possible to alter the columns that are selected into the view within the table until the views exists.

While creating a view with schema binding option, within the select statement of the view you must specify the table name in the format “schemaname.tablename” where schema name is same as username.

The following examples creates a view that provides access to empno, ename, job, Sal and comm by specifying schema binding.

Create view empview

With schema binding as

Select empno, ename, job, Sal, comm from dbo.emp

Now, we will try to alter.

Alter table EMP alter column sal small money

Error

Getting List of Views:

To get list of view available in the dB, you can use any of the following two options

It gets the definition of the view, table name

Select* from INFORMATION_SCHEMA.VIEWS

Select* from sys.views

(View date, created data, modified data)

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

Getting definition of a view:

To get the definition of a view, use the stored procedure sp_helptext that has the following syntax:

sp_helptext ‘view name’

Example

sp_helptext'v4'

Note:

When you don’t want to display the definition of your view to anyone then specify encryption option while creating the view.

To delete the view

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

Drop view <view name>

The following examples deletes the view v4

Dropview v4