How do SQL Server View Indexes work under the hood?

Question

How do SQL Server View Indexes work under the hood? I've read this TechNet article but it doesn't explain how a view index actually works. Can anyone explain this to me?

Note: I'm not sure if this should have gone on SF. If it should, just move it over there.

Answer 1

I would have to refer to some course notes to get a proper answer, I remember that the indexed view contains a count of the rows, and updates the pre-aggregated data based on changes in the underlying table. (Just like a normal index.)

It specifically contains the sum and the count so that it can handle insertions / updates and deletions. (Adjust the sum and the count then represent the result). This way a view can output an average (sum / count) but does not store the average, since it would have no way of being able to adjust it from new data coming it.

As far as I remember, the indexed view is stored just the same as the normal index, using a B-Tree, data pages and has it's own IAM.

Thats all I remember off top of my head.

Answer 2

During query compilation the SQL compiler will see the indexed view definition in the metadata and will generate execution plans that maintains the indexed view data along with the table. Eg:

create table foo (a int not null,
   constraint pkfoo primary key (a))
GO

create view vFoo 
with schemabinding
as
select a from dbo.foo
GO

create unique clustered index cdxvFoo on vFoo(a)
GO

insert into foo (a) values (1);
GO

If you look at the execution plan of the INSERT statement you'll see that it contains two clustered index inserts, one into the foo.pkFoo and one into vFoo.cdxvFoo. Similar with any delete or update.