Indexed View Quiz: The Table and Clustered Index Confusion

An indexed view can provide another stored access path, while its base table still has only one clustered index. The view’s unique clustered index belongs to a separate object. The example below makes the two owners visible.

Separate the table from the stored view

A table can have one clustered index. An indexed view also has one unique clustered index, which materializes its result. These indexes belong to different object IDs. An alternative representation does not give one table two clustered indexes.

A small indexed view example with two object owners

Run this example in a demo database. It creates permanent demo objects and uses one connection throughout. Do not reuse existing objects with these names. The explicit session options and schema binding support the indexed view definition.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;

CREATE TABLE dbo.QuizTable
(
    Id int NOT NULL PRIMARY KEY CLUSTERED,
    Code char(1) NOT NULL
);
INSERT dbo.QuizTable VALUES (1, 'A'), (2, 'B');
GO
CREATE VIEW dbo.QuizView WITH SCHEMABINDING
AS
SELECT Id, Code FROM dbo.QuizTable;
GO
CREATE UNIQUE CLUSTERED INDEX CIX_QuizView
ON dbo.QuizView(Id);
GO

SELECT OBJECT_NAME(object_id) AS ObjectName,
       COUNT(*) AS ClusteredIndexes
FROM sys.indexes
WHERE object_id IN (OBJECT_ID(N'dbo.QuizTable'),
                    OBJECT_ID(N'dbo.QuizView'))
  AND type = 1
GROUP BY object_id
ORDER BY ObjectName;

The expected catalog result has two rows: QuizTable with one clustered index and QuizView with one clustered index. Count by object ID to keep the ownership clear. The view’s index is not added to the table’s own index count.

Observe maintenance without keeping the test change

SELECT Id, Code FROM dbo.QuizView WITH (NOEXPAND) ORDER BY Id;
BEGIN TRANSACTION;
UPDATE dbo.QuizTable SET Code = 'Z' WHERE Id = 1;
SELECT Id, Code FROM dbo.QuizView WITH (NOEXPAND) ORDER BY Id;
ROLLBACK TRANSACTION;
SELECT Id, Code FROM dbo.QuizView WITH (NOEXPAND) ORDER BY Id;

The first result contains A and B. The result inside the transaction contains Z and B. After rollback, the final result returns to A and B. This tests maintenance and rollback on the same small example, rather than measuring a performance gain.

Indexed views require specific session options during creation, base-table writes and relevant query use. The optimizer’s automatic view substitution depends on the SQL Server edition. The example uses NOEXPAND to access the stored view explicitly. View maintenance adds write work, so evaluate the actual workload before adopting the design.

Clean Up the Demonstration

DROP VIEW dbo.QuizView;
DROP TABLE dbo.QuizTable;

Coverage describes a query’s available columns. It does not make a nonclustered index the table’s clustered index. Count indexes by their owning object to keep that distinction clear.

If you want the longer story, my older note on the limitations of the views is still a good read.

A view index is not a second table index, it is one stored result with its own owner.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.