Views

                                            Views

                       View is a virtual table. Which allows to view and modify related data. view is DB Object,which stores only select Query in compiled format. Hence it is also called as "Stored Query".

Types of views:

1. Simple View
2. Check option view
3. Complex view
4. Indexed view

Syntax:

            create view view_name

                        [with Encryption]/[with Schema binding]

              As

              Select Query [with check option]

1.Simple view:

                        A view which is created on a single table and does not contain group by clause, group by function , Distinct operator and Joins is called simple view. To perform insert operation through simple view all mandatory columns of base objects has to be enclosed.
Ex:
create view v1
As
select id,name,salary from employee
Select * from v1

2.Check option view:
                                  A view which is created on conditional basis. if data beyond the condition then it will reject the data. 
Ex:
create view v2
As 
Select id, name, salary from employee where salary>10000 with check option
Select * from v2

3.Complex view:
                            A view which is created on multiple tables and contain group by clause, group by function , Distinct operator and Joins is called Complex view. By default it is not update able view.
Ex:
create view v3
As
select dept,sum(salary) from employee 
group by dept
select * from v3

4.Indexed view:
                         This view is different from other views. here we create index for the view which will enhance the performance of a Query.
To create indexed view we have the following restrictions:

1. The view must be a schema binding view
2. The view should contains count_big aggregate function on it
3. If the aggregate functions are associated with any column and if that column allows null values in ti then we should associated it with "isnull()" function.

Ex:

create view v4 with schema binding

As

select dept,count_big(*) as cnt ,sum(isnull(comm,0)) as commission from DBO.employee

group by dept

                        The above view is logical to make it physical we need to create index for that view.

Create unique clustered index dept_index on v4(dept)

Note:

if you want to use order by clause in your view then it is must that select Query should be associated with TOP keyword.

Q.what is Encrypted view?

A.Which is used to hide the query stored in a view. These views are associated by an option called " with Encryption"

Ex:

create view v5 with encryption

As

select id, name, salary from employee

check this: sp_helptext v5

Q.what is Schema binding ?

A.This view is not allowed to drop  base object. These views will be associated by an option "with schema binding". These views when stored a query table should be preceded by schema name and these views will not allow to story select operator ("*").

Note:

if we required we can use both encryption and schema binding in single query

Ex:

create view v6 with encryption , Schema binding

As

select query