Views

VIEWS :

A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database. Similar to a SQL table, the view name should be unique in a database.

create view viewname
as
---select statements

to execute view

select * from viewname

ex:

create view Vw_getemployeebydept
as
seletct ename,job,sal 
from emp e
inner join dept d on e.dept_no=d.deptno

How to alter a view

Alter view viewname
as
---select statements

SCHEMABINDING VIEW:

Schema bindining view is used for security purpose.

If create a view a view with schemabinding we can not drop the main tables.

Rules : 1.select Query should not contain * 2.Table name should be 2 part name (dbo.tablename) or 3 part name (databasename.dbo.tablename)

create view viewname
with schemabinding 
as
---select statements

ex:

create view Vw_GetempbyIT
with schemabinding 
as
select empno,ename,sal,dname from dbo.emp
inner join dept on dbo.emp.dept_no=dbo.dept.deptno
where dname='IT'

INDEXED VIEW:

If we create unique clustered index on a view then that view called as Indexed view .

If we want to create a indexed view then that view shoud be schemabinding.

Data retrieval will be fast.

create unique clustered index indexname
on viewname(columns)

ex:

create unique clustered Ix_vw_GetempbyIT
on Vw_GetempbyIT(ename,sal)