Indexed views are also called as materialized view that means result of data manipulated and stored in the view. We can create unique clustered index on view to improve performance of the queries based on it.
Indexed views can use to help those queries that generates large result set. It is not advisable to create index on such views that are updated very frequently.
We will make use of example to understand indexed view in better, we have created temp table with named indexedview_test having single column as serialno.
create table indexedview_test(serialno int)
Inserting data in the temp table for testing purpose using below query.
declare @count int
set @count =1
while @count < 280000
begin
insert into indexedview_test(serialno)values(@count)
set @count = @count + 1
end
Lets create a simple view based on indexedview_test table.
create view uspviews
as
select serialno,count(*) [total Count]
from indexedview_test
where serialno BETWEEN 1000 AND 2000
group by serialno
After creating view, lets create a unique clustered index on uspviews.
create unique clustered index uniclusterkey on uspviews(serialno)
Msg 4512, Level 16, State 3, Procedure uspviews, Line 3
Cannot schema bind view ‘uspviews’ because name ‘indexedview_test’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
As error suggest, we have to re-create the view with schema binding option.
alter view uspviews with schemabinding
as
select serialno,count(*) [total Count] from dbo.indexedview_test
where serialno BETWEEN 1000 AND 2000
group by serialno
Msg 10136, Level 16, State 1, Line 1
Cannot create index on view “testdb.dbo.uspviews” because it uses the aggregate COUNT. Use COUNT_BIG instead.
Only difference between Count and big_count is in term of return value. Count returns int value whereas big_count retruns bigint value.
alter view uspviews with schemabinding
as
select serialno,count_big(*) [total Count]
from dbo.indexedview_test
where serialno BETWEEN 1000 AND 2000
group by serialno
If your view definition having tsql statement “select * from indexedview_test” query then it will display below error.
Msg 1054, Level 15, State 6, Procedure uspviews, Line 4
Syntax ‘*’ is not allowed in schema-bound objects.
create unique clustered index uniclusterkey on uspviews(serialno)
select * from uspviews where serialno > 4000
You can see some difference in performance of the query.