A Brief Note on VIEWS

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.

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

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 (45,’ARJUN’, 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 (55,’NAG’, 10000,10)

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

INSERT INTO V2 VALUES (65,’NAGARJUN’, 20000,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 ( DEPTNO=20 is not valid as view definition is with condition where clause). It means invalid values are inserting into the base table EMP.

To avoid such operations we need to create the view with – ‘WITH CHECK OPTION’
E.g: CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10
                                                                                    WITH CHECK OPTION                                                           

INSERT INTO V3 VALUES (75,’ARUN’,25000,20)

The above insert statement cannot insert values into base table EMP and into View V3.

SP_HELPTEXT: This Stored Procedure is used to display the definition of a specific view.

Syntax: SP_HELPTEXT VIEW NAME
E.g.: SP_HELPTEXT V1
Output:  CREATE V1 AS SELECT * FROM EMP

If we want to hide the definition of view use ‘WITH ENCRYTION’.

WITH ENCRYTION: Once we create a view with ‘WITH ENCRYTION’ then we cannot find the definition of that view using SP_HELPTEXT.

E.g.:

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

Output: The text for V4 object is encrypted.

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

       1.       Replace the CREATE with ALTER

2.       Remove WITH ENCRYPTION key word

3.       Select the query and press F5

E.g.:

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

Creating 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