What is Indexed View in SQL Server

Indexed Views:

A view whose query result is stored in database is called as indexed View.

·       Indexed views dramatically improve the performance of some types of queries

Indexed views are created mainly for two reasons

1 To improve performance of aggregate operations

2 To create local copy for remote db.

If we want to create an Indexed View we need to do the following:

·       Create a View by using the with Schema Binding Option.(create view with schema binding)

·       Create a Unique Clustered Index on the View (create index on that view).

Step1:

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

Step2: create index on that view.

Now, I am creating unique clustered index on group by column i.e. deptno

Create unique clustered index i11 on index (deptno)

Select * from indx1

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.

NOTE:

Once the index is created on the view it will internally the store the information of the View physicially in a location, any manupulation performed on the base table reflects to the the View also.

Difference between View and Indexed View

      

VIEW	INDEX VIEW
When view is created the query is stored in database.	When index view is created the query result stored in db
View does not contain any data.	Index view contains data.