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]

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]

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