snippetsqlModerate
How much of a view is persisted when you create an index?
Viewed 0 times
muchhowyoucreatepersistedviewwhenindex
Problem
Say I have a view defined by the following SQL:
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.
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.tIDFrom 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.
Solution
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
Turned a trace flag on so I can use
Results:
So I knew to check page 312:
And here are the three values from the join, even though they're not all part of the unique aspect of the clustered index:
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);
GOTurned 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:
Code Snippets
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);
GODBCC TRACEON(3604, -1);
DBCC IND('tempdb', 'dbo.v', 1);DBCC PAGE(2, 1, 312, 3);Context
StackExchange Database Administrators Q#31664, answer score: 15
Revisions (0)
No revisions yet.