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:
- To improve performance of aggregate operation.
- 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
- When View is created the query is stored in Database
- A View doesn’t contains any data.
- When Index view is created the query result stored in Database
- Index views contains data.
Follows 2 steps index viewed :-
- Create view with schema binding.
- 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.