Indexed Views in SQL Server – SQL Server Tutorial

Indexed Views in SQL Server:

A view whose query result is stored in database is called “indexed view”

In oracle is called “materialized view”.

Index views are created mainly for two reasons:

1. To improve performance of aggregate operation.
2. To create local copy for remote databases.

These two operations are most expensive.

Index views occupies memory as it containing data.

Difference between view and index view

View:

1. When View is created the query is stored in Database
2. A View doesn’t contains any data.

Index View:

1. When Index view is created the query result stored in Database
2.  Index views contains data. 

Follows 2 steps index viewed  :-

1. Create view with schema binding.
2. Create index on that view.

Create View with Schema Binding :

Create view indv1 with schemabinding As Select deptno,sum(isnull(sal,0))sumsal, Count_big(*) emps From dbo.emp Group by    deptno

Create Index on that View :

Create unique clustered index on group by column I:e   depno.

• Create unique clustered index  ill on indv1(depno)
• Select * from indv1
• Update emp set sal =sal+1000 where deptno =10.

After creating indexed view if we make any changes to base table the changes are reflected to index view automatically.