How much of a view is persisted when you create an index?

Question

Say I have a view defined by the following SQL:

SELECT t1.id, t2.name, t3.address
FROM Table1 as t1
INNER JOIN Table2 t2
  ON t1.id = t2.tID
INNER JOIN Table3 t3
  ON t1.id = t3.tID

From what I understand, if I create an index on this view then the data will become persisted, but I'm unclear what exactly gets persisted. Say I create a unique clustered index on t1.id, will all three columns be saved to disk or will the values being pulled from table2 and table3 still be calculated at runtime?

Let me know if any of this doesn't make sense or if I have left anything important out.

Answer 1

All three columns are persisted to disk in the clustered index on the indexed view (no different, really, from a clustered index on a regular table). You can validate this using DBCC PAGE. I created the following structure in tempdb:

USE tempdb;
GO

CREATE TABLE dbo.a(a INT);
CREATE TABLE dbo.b(a INT, b DATETIME);
CREATE TABLE dbo.c(a INT, c CHAR(32));

INSERT dbo.a SELECT 1;
INSERT dbo.b SELECT 1, SYSDATETIME();
INSERT dbo.c SELECT 1, REPLICATE('c', 32);
GO

CREATE VIEW dbo.v
WITH SCHEMABINDING 
AS
  SELECT a.a, b.b, c.c
  FROM dbo.a
  INNER JOIN dbo.b
  ON a.a = b.a
  INNER JOIN dbo.c
  ON a.a = c.a;
GO

CREATE UNIQUE CLUSTERED INDEX x ON dbo.v(a);
GO

Turned a trace flag on so I can use IND/PAGE:

DBCC TRACEON(3604, -1);
DBCC IND('tempdb', 'dbo.v', 1);

Results:

So I knew to check page 312:

DBCC PAGE(2, 1, 312, 3);

And here are the three values from the join, even though they're not all part of the unique aspect of the clustered index: