Views in SQL Server

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:

Syntax:

CREATE VIEW 
AS 
SELECT_STATEMENT

Example:

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:

Syntax:

ALTER VIEW ViewName
AS 
SELECT_STATEMENT

Example:

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.

Example:

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:

Syntax:

CREATE VIEW ViewName [WITH ENCRYPTION] 
AS 
SELECT_STATEMENT

Example:

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).

Indexed Views:

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:

  1. It should be bind with SCHEMABINDING.
  2. The columns used in the indexed views cannot be dropped as it occupies separate memory.
  3.  Must create unique clustered index on view for creating index on view.

 Syntax:

CREATE VIEW ViewName WITH SCHEMABINDING [WITH ENCRYPTION] 
AS 
SELECT_STATEMENT

Example:

CREATE VIEW emp_v 
WITH SCHEMABINDING 
AS 
SELECT 
    e.eid
  , e.sal 
FROM emp e 
WHERE eid&lt;=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)

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: