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.
- Simple Views
- 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
- Replace CREATE with ALTER
- Remove WITH ENCRYPTION keyword
- 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
No comments:
Post a Comment