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.

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]

Example: 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.

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.

Example:

create view v4 with encryption as select * from emp where deptno=20

Sp_Helptext v4

Output: Object Comments have been 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

Example:

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 only one multiple base tables.

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

Create view viewname [with encryption] as select * from viewname [where condition][with check option]

Example: create view v5 as select * from v1 where deptno=10

Syntax: to drop the views:

Drop view viewname […N]

Example: drop view v1,v2,v3,v4,v5

VIEWS