How do SQL Server View Indexes work under the hood?
How do SQL Server View Indexes work under the hood? I read this TechNet article , but it doesn't explain how the view index actually works. Can someone explain this to me?
Note. I'm not sure if this should have happened on SF. If so, just move it there.
source to share
During SQL compilation, the SQL compiler will see the definition of the indexed view in the metadata and will generate execution plans that support the indexed lookup data along with the table. For example:
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 can see that it contains two clustered index inserts, one in foo.pkFoo and one in vFoo.cdxvFoo. Like any uninstall or update.
source to share
I would need to refer to some of the course notes to get the correct answer, I remember that the indexed view contains the number of rows and updates the pre-aggregated data based on changes in the underlying table. (Like a normal index.)
It specifically contains a sum and a counter so that it can handle inserts / updates and deletions. (Adjust the amount and then the counter presents the result). So the view can output the average (sum / count), but does not store the average, as it will not be able to adjust it from new data coming into it.
As far as I remember, the indexed view is stored the same as a normal index, using B-Tree data pages and has its own IAM.
This is all that I remember from my head.
source to share