What Is Indexed View In SQL By Sagar Jaybhay

In this article we will understand What Is Indexed View In SQL Server By Sagar Jaybhay.

Indexed View

A normal view or non-indexed view is a saved SQL query or a virtual table. When you try to get data from the table the data is coming from an underlying table. So it means the view is a virtual table and doesn’t store any data.

But when you create an index on view it gets materialized which means you can store data in view so in the SQL server we called is as Indexed view.

There are some guidelines for creating an indexed view.

1. The view should be created with schemabinding option.
2. If in your select function contains aggregate functions like sum, avg
   like that then for this you need to replace the null value with other value
   means 0 or not null value.
3. If your select query contains group by clause then you need to use
   Count_Big(*) expression.
4. Whatever table you used in view you need to specify 2 part names of
   these tables the example of above shown below

create view IndexedView
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Indexed View In SQL By Sagar Jaybhay

Now by using the above query, we created a view but we are not able to create an index on that so that you can get below error

Msg 1939,
Level 16, State 1, Line 59

Cannot create an index on view 'IndexedView' because the view is not schema bound.

Indexed View In SQL Error Message By Sagar Jaybhay

For this, we need to create a view schemabinding option and now we can alter our view. For altering view our query looks like below

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Msg 4512,
Level 16, State 3, Procedure IndexedView, Line 4 [Batch Start Line 58]

Cannot schema bind view 'IndexedView' because the name 'Employee' is invalid for schema binding. Names must be in two-part format and an object cannot reference

Now when we are altering the view with schema binding option we get the above error. To fix this we need two-part names of the table.

Now our view gets created by using below query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

But when we are going to create the index we will get below error

Msg 10138,
Level 16, State 1, Line 68

Cannot create an index on view 'temp.dbo.IndexedView' because its select list does not include proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select a list.

Now we add count_big(*) in our query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(isnull(e.salary,0)) as totalsal,
COUNT_BIG(*) as totalemp
from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

and when we use below query

create unique clustered index view_deptindex
on IndexedView(dept);

to create an index it works perfectly fine.