Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 11 down vote accepted

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:

enter image description here

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:

enter image description here

share|improve this answer
    
Solid answer, thanks! –  Abe Miessler Jan 9 '13 at 23:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.