Views in SQl Server

VIEWS

A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table.

There are two types views available in SQL Server.

1.    Simple Views
2.    Complex Views

Note: 

 If we perform any modifications in base table, then those modifications automatically effected in view and vice-versa.

1.Simple Views: Creating View by taking only one single base table.

Syntax:

CREATE VIEW VIEWNAME
 [WITH ENCRYPTION]
[WITH SCHEMABINDING]
AS
SELECT * FROM TABLENAME [WHERE CONDITION]
   [WITH CHECK OPTION]

E.g.:
CREATE VIEW V1 AS SELECT * FROM EMP

INSERT INTO V1 VALUES (55,’RAVI’, 10000,10)

The above insert statement inserts the values into base table EMP as well as into view V1.

E.g.:

CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10

INSERT INTO V2 VALUES (66,’BABBU’, 25000,10)

The above insert statement inserts the values into base table EMP as well as into view V2.

INSERT INTO V2 VALUES (77,’AMAR’, 15000, 20)

The above insert statement inserts the values into only base table EMP but not into view V2 because according to the definition of V2 user supplied values are invalid values.

It means invalid values are inserting into base table EMP. To stop this kind of operations we have to create the view with ‘WITH CHECK OPTION’.

E.g.:
CREATE VIEW V3 AS SELECT * FROM EMP WHERE DEPTNO=10
        WITH CHECK OPTION

INSERT INTO V3 VALUES (88,’TEJA’, 25000,20)
The above insert statement cannot inserts the values into base table EMP as well as into view V3.

SP_HELPTEXT: This stored procedure is used to display the definition of a specific view.

Syntax:   SP_HELPTEXT    VIEWNAME
E.g.: SP_HELPTEXT      V1

Output: CREATE VIEW V1 AS SELECT * FROM EMP

WITH ENCRYPTION: Once we create any view with ‘WITH ENCRYPTION’ then we cannot find the definition of that particular view using SP_HELPTEXT stored procedure because this encryption option hides the definition.

E.g.:
CREATE VIEW V4
WITH ENCRYPTION
AS
SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

Output :The text for object v4 is encrypted

To decrypt the definition of view V4 we have to follow the below approach

1.    Replace CREATE with ALTER
2.    Remove WITH ENCRYPTION keyword
3.    Select the query and press F5.

E.g.:
ALTER VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

CREATE VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20

2.Complex Views:

Creating View by taking multiple base tables.

Ex:
CREATE VIEW EMP_DEPT_VIEW
AS
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

Syntax To Create view based on another views:
SQL SERVER enables users to create views based on another view.We can create view based on another view up to 32 levels.

Syntax:

CREATE VIEW VIEWNAME
[WITH ENCRYPTION]
[with scehmabinding]
AS
SELECT * FROM VIEWNAME [WHERE CONDITION]
   [WITH CHECK OPTION]

E.g.: CREATE VIEW V5 AS SELECT * FROM V1 WHERE DEPTNO=10

Syntax To Drop the Views:
DROP VIEW VIEWNAME […N]

E.g.: DROP VIEW V1, V2, V3, V4, V5

Schema Binding :

a. when view created by schema Binding then view becomes dependent object on base table.

b. The base table cannot be dropped without dropping view.

c. When schemabinding is used then tablename prefixed by username/schemabname.

ex:

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

ex: drop table emp

    Invalid , because view is dependent object so we first drop the view after that table.

Advantages of view:

1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.

2. To control access to rows and columns of data.

Thanks
GSV