Views -- Indexed views-- not suitable for OLTP --suitable for OLAP

 View is just a virtual table which consist of saved quarries. so normal view doesn't store any data by default.

But if we have to change the default nature we can use Index. In Oracle it is also called Materialized View.

SO WHY TO USE INDEXED VIEW WHEN WE HAVE NORMAL VIEW WHICH IS EASY.

so when we use normal view which is calculating may be total number of employee grouping by department, what happens is the view continuously goes back to the base table and calculates every time the select view statement is fired.. Sounds Time Consuming right ? this is where Indexing Comes to handy. Indexed views can store data breaking its default nature. Its not the view but it's clustered unique index stores the data. so AT THE END OF THE BLOG I HAVE STATED WHEN NOT TO USE INDEXED VIEWS, so don't judge the indexed view too quickly 

To make a indexed view one has to follow some rules or guideline such are discussed below:

1. WITH SCHEMABINDING should be used.

2. Replacement value For null should be pre-specified if the result may also NULL.

3. If Group By is used then COUNT_BIG(*) expression MUST be used. 

4. The Base Tables in the view must be referenced with 2 part name. eg. dbo.Employee

5.CLuseted Unique Index should be used.

CODE:

Create View vDepartement

With Schemabinding

as 

select Place As Plaace,Count_big(*) as TOTALNUMBER from dbo.Departement group by Place

create UNIQUE clustered Index vDepartementIndex on vDepartement(Plaace)

INDEXED VIEW WHERE NOT TO USE ?/WHY NOT TO USE INDEXED VIEW.

1. Indexed views are only suitable where the data are not changed heavily.

2. Ideal for OLAP systems(Online Analytics Processing), in a data warehousing system where data are not changed frequently.

In OLTP (online transaction processing)system this is not suitable, as the indexed are readjusted when the data are changed in base table. which will affect the performance greatly. 

   Note that cost of maintaining Indexed Views is much  more than maintaining Table views.