Views do not occupy space in the memory. It creates a virtual table that represents the data in one or more tables. It is a window to data. It provides a indirect security mechanism, that restricts the user to a certain data in one or more base tables. We can create a view as follows:
CREATE VIEW AS SELECT_STATEMENT
CREATE VIEW emp_v AS SELECT eid , ename FROM EMP
Insertion, deletions and updations (DML commands) can be performed on a view. DML commands can be performed on a table that is in the view, if and only if there are no conditions on that field (example, if sal column of emp table have a check condn to be >1000 and if we are attempting to insert a value of sal <1000 through a view then it leads to the violation of constraint).
If a view is created with multiple tables, then we cannot insert the values in the view. A view can be altered as follows:
ALTER VIEW ViewName AS SELECT_STATEMENT
ALTER VIEW emp_v AS SELECT e.eid , e.ename , d.dno FROM emp e, dept d WHERE e.dno=d.dno
If we drop a column in the main table that is used in the view, then there will be no use of the view.
ALTER TABLE emp DROP COLUMN ename
Here, we attempt to drop a column in emp that is already used in view emp_v, hence when we drop this column, emp_v view cannot be used further.
View with Encryption:
CREATE VIEW ViewName [WITH ENCRYPTION] AS SELECT_STATEMENT
CREATE VIEW emp_v WITH ENCRYPTION AS SELECT eid FROM EMP
When a view is given WITH ENCRYPTION, it prevents the view from being published. It encrypts the text that is given in the view. We cannot decrypt the view if it is given WITH ENCRYPTION (need to use third party tools for decrypting the object).
Irrespective of normal views, indexed view occupies separate memory. It is possible to create a unique clustered index on a view, as well as non clustered indexes, to improve data access performance on the most complex queries. A view that has a unique clustered index is referred to as an indexed view.
Rules for creating an Indexed View:
- It should be bind with SCHEMABINDING.
- The columns used in the indexed views cannot be dropped as it occupies separate memory.
- Must create unique clustered index on view for creating index on view.
CREATE VIEW ViewName WITH SCHEMABINDING [WITH ENCRYPTION] AS SELECT_STATEMENT
CREATE VIEW emp_v WITH SCHEMABINDING AS SELECT e.eid , e.sal FROM emp e WHERE eid<=4 INSERT INTO emp_v values(10,1000)
Here, the above value is inserted into the emp table but is not inserted in the view(as view can take only the values that has eid<4)