Not sure if I understand what an Indexed View does behind the scenes, in SqlServer

Question

Hi folks,

I'm using sql server 2008 and have started to find using Indexed Views are helping me speed up some of my queries ... as my schema isn't basic.

So, if i have a table that is the following...

**ParentTable
ParentId INT PK IDENTITY
Name VARCHAR(MAX)

**ChildTable
ChildId INT PK IDENTITY
ParentId INT (FK to the table above)
Name VARCHAR(MAX)
Boundary GEOGRAPHY
CentrePoint GEOGRAPHY
CentrePointFlattened GEOMETRY

So for each row in the parent table, it can have zero to many children.

Firstly, if I make an index'd view of the children, it won't work if the ParentId filed can be nullable. So i need to make it required.

Now the question.

I have an index view of the children table inner join'd to the parent table (note i'm only indexing some of the fields from either table)...

(pseduo sql code)
ParentId INT
Name VARCHAR(MAX) AS ParentName
ChildId INT
Name VARCHAR(MAX) as ChildName
Boundary GEOGRAPHY

Now, are the data for these 5 fields serialized/copied to ANOTHER location again ? or does the index view only create some index id's that is the data for the table?

Answer 1

Yes. Indexed view is basically another invisible table that gets updated automatically as underlying tables change.