Views in SQL Server

                                            VIEWS

                                                                                                    

* A view is a logical or virtual or mirror table that consists of columns from one or more tables.

* In general views are used to display information from the table.

* View will be created by using select statement and table used for the creation of the view is called base table.

* View will not store records and will not use memory space . View will shows the records from the table with help of column names existing in it .

* Views are classified into 2 types based on its functionality.

Simple View:

A simple view is a view which is created from only one base table.

Syntax:

CREATE VIEW <VIEW-NAME>
AS
SELECT *
FROM < T.N >

Ex:

CREATE VIEW EMP_VIEW
AS
SELECT *
FROM EMP

* Whenever we are performing DML operations both underline table are effected.

* We can perform DML operations through view to base table in a simple view based on the following conditions.

* We must include base table NOT NULL column into the view then only we can perform insertion operation on view.

* We cannot perform DML operation on view, If view contains group functions, group by clause ,distinct ,set operators, joins then on those type of views

Complex Views:

A view which is created from more than one base table

Syntax :

CREATE VIEW <VIEW-NAME>
AS
SELECT
WITH
INNER JOIN
 OR
SET OPERATORS STATEMENT

EX:

CREATE VIEW EMP_COMPLEX_VIEW
AS
SELECT EMPNO
 ,ENAME
 ,SAL
 ,EMP.DEPTNO
 ,DNAME
 ,LOC
FROM EMP ,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO

CREATE VIEW EMP_COMPLEX_JOIN
AS
SELECT *
FROM EMP

UNION

SELECT *
FROM EMP

With Check Option:

* If you want to create constraints or conditions on view it is possible with help of with check option.

* Whenever we are using with check option clause always server checks the where condition and satisfied values only inserted into base table.

Ex:

CREATE VIEW TEST_WITHCHEK
AS
SELECT *
FROM EMP
WHERE DEPTNO = 10
WITH CHECK
OPTION

Now you cannot insert values into table other than 10 th department.

View Restrictions

                A view can be created only if there is select permission  on its base table.

                View cannot be sorted with order by unless the TOP command is used.

With Schema Binding:

   With Schema Binding is used to restrict the user to drop or rename base table or columns present in the view. 

Ex:

CREATE VIEW V_EMP
 WITH SECHEMABINDING
AS
SELECT EMPNO
 ,ENAME
FROM DBO.EMP

Note:

* You cannot able to use * with select statement whenever you are using with schema binding.

* You should mention schema name along with table name when ever if you are suing with schema binding.

Supplying column names in the header of the view:

CREATE VIEW TEST_VIEW (
 ID
 ,NAME
 )
AS
SELECT EMPNO ,ENAME
FROM EMP

Syntax to Drop a View:

DROP VIEW <VIEW-NAME>