Indexed view in sql server

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.

Leave a Reply

  

  

  


+ 9 = 13