Indexed View Restrictions Quiz: Which Rule Blocks the Index?

This Indexed View Restrictions Quiz asks which one of four familiar things stops you from indexing a view. Three of them are required, and one is forbidden. Read the setup, pick your answer, and then run the script to check yourself.

The Quiz

A shop has a product table and a sales table. A view totals the sales for each product, and reports read it all day. Jordan wants to speed up those reports by creating a unique clustered index on the view.

Which one of these stops you from creating that index?

A. COUNT_BIG(*) in the select list
B. WITH SCHEMABINDING on the view
C. A LEFT OUTER JOIN in the view
D. Two-part table names, such as dbo.QuizSale

Take a moment and pick one before you read on.

The Answer

The answer is C. An indexed view can’t contain an outer join.

The other three aren’t obstacles. They’re requirements. SQL Server wants the view bound to its tables and written with two-part names. It also wants a row count when the view groups data. An outer join breaks the rules that let SQL Server keep the stored results up to date.

Prove It

The first script creates a small database called SqlQuizIndexedViewRestrictions. It is used only for this example, so run it on a test server. The script builds both tables and puts in a few rows. One setting near the top matters later, and I’ll explain it in a moment.

IF DB_ID(N'SqlQuizIndexedViewRestrictions') IS NULL CREATE DATABASE SqlQuizIndexedViewRestrictions;
GO
USE SqlQuizIndexedViewRestrictions;
SET QUOTED_IDENTIFIER ON;
GO
DROP VIEW IF EXISTS dbo.QuizSalesByProductLeft;
DROP VIEW IF EXISTS dbo.QuizSalesByProduct;
DROP VIEW IF EXISTS dbo.QuizSalesNoBind;
DROP VIEW IF EXISTS dbo.QuizSalesNoCount;
DROP VIEW IF EXISTS dbo.QuizSalesOnePart;
DROP VIEW IF EXISTS dbo.QuizSalesMax;
DROP VIEW IF EXISTS dbo.QuizSalesStar;
DROP VIEW IF EXISTS dbo.QuizSalesSetOff;
DROP TABLE IF EXISTS dbo.QuizSale;
DROP TABLE IF EXISTS dbo.QuizProduct;
CREATE TABLE dbo.QuizProduct
(
    ProductID int NOT NULL PRIMARY KEY,
    ProductName nvarchar(40) NOT NULL
);
CREATE TABLE dbo.QuizSale
(
    SaleID int IDENTITY(1,1) PRIMARY KEY,
    ProductID int NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT INTO dbo.QuizProduct (ProductID, ProductName)
VALUES (1, N'Notebook'), (2, N'Desk lamp'), (3, N'Backpack');
INSERT INTO dbo.QuizSale (ProductID, Amount)
VALUES (1, 4.50), (1, 9.00), (2, 32.00), (3, 58.00), (3, 58.00);

Now create the view with a LEFT OUTER JOIN, and try to index it. The view itself is accepted. Only the index fails.

CREATE VIEW dbo.QuizSalesByProductLeft
WITH SCHEMABINDING
AS
SELECT p.ProductID, p.ProductName, SUM(s.Amount) AS TotalAmount, COUNT_BIG(*) AS SaleCount
FROM dbo.QuizProduct AS p
LEFT OUTER JOIN dbo.QuizSale AS s ON s.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName;
GO
CREATE UNIQUE CLUSTERED INDEX IX_QuizSalesByProductLeft ON dbo.QuizSalesByProductLeft (ProductID);

This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 10113, Level 16, State 1, Line 1
Cannot create index on view "SqlQuizIndexedViewRestrictions.dbo.QuizSalesByProductLeft" because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

The view in the next script is the same, with one change: INNER JOIN replaces LEFT OUTER JOIN. The index is created, and a query on the view reads the stored totals.

CREATE VIEW dbo.QuizSalesByProduct
WITH SCHEMABINDING
AS
SELECT p.ProductID, p.ProductName, SUM(s.Amount) AS TotalAmount, COUNT_BIG(*) AS SaleCount
FROM dbo.QuizProduct AS p
INNER JOIN dbo.QuizSale AS s ON s.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName;
GO
CREATE UNIQUE CLUSTERED INDEX IX_QuizSalesByProduct ON dbo.QuizSalesByProduct (ProductID);
GO
SELECT ProductID, ProductName, TotalAmount, SaleCount
FROM dbo.QuizSalesByProduct WITH (NOEXPAND)
ORDER BY ProductID;
GO
SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.QuizSalesByProduct'), 'IsIndexed') AS IsIndexed;

The first query returned these three rows. The second query returned 1, which means the view now has an index.

The NOEXPAND hint tells SQL Server to read the view’s own index. In Enterprise and Developer editions, the optimizer can pick the index by itself. Other editions need the hint.

Why the Other Answers Are Wrong

A is a requirement, not a block. When a view groups rows, SQL Server needs COUNT_BIG(*) in the select list. The count tells it when a group has no rows left. Leave it out, and the index fails.

B is a requirement too. SCHEMABINDING stops anyone from changing the tables in a way that would break the view. Without it, the index can’t be created.

D is a requirement as well. A schema-bound view must name its tables with two-part names. A bare table name is refused when you create the view.

This script proves all three. It creates one view without each required rule. The first two views are created, and their indexes fail. The third view fails earlier, at CREATE VIEW.

CREATE VIEW dbo.QuizSalesNoBind
AS
SELECT s.ProductID, SUM(s.Amount) AS TotalAmount, COUNT_BIG(*) AS SaleCount
FROM dbo.QuizSale AS s
GROUP BY s.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_QuizSalesNoBind ON dbo.QuizSalesNoBind (ProductID);
GO
CREATE VIEW dbo.QuizSalesNoCount
WITH SCHEMABINDING
AS
SELECT s.ProductID, SUM(s.Amount) AS TotalAmount
FROM dbo.QuizSale AS s
GROUP BY s.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_QuizSalesNoCount ON dbo.QuizSalesNoCount (ProductID);
GO
CREATE VIEW dbo.QuizSalesOnePart
WITH SCHEMABINDING
AS
SELECT s.ProductID, SUM(s.Amount) AS TotalAmount, COUNT_BIG(*) AS SaleCount
FROM QuizSale AS s
GROUP BY s.ProductID;

This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'QuizSalesNoBind' because the view is not schema bound.

Msg 10138, Level 16, State 1, Line 1
Cannot create index on view 'SqlQuizIndexedViewRestrictions.dbo.QuizSalesNoCount' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

Msg 4512, Level 16, State 3, Procedure QuizSalesOnePart, Line 4
Cannot schema bind view 'dbo.QuizSalesOnePart' because name 'QuizSale' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

More Rules That Block the Index

The outer join isn’t the only forbidden item. An indexed view also can’t use MAX or MIN. It can’t use SELECT * either. The next script tries both.

CREATE VIEW dbo.QuizSalesMax
WITH SCHEMABINDING
AS
SELECT s.ProductID, MAX(s.Amount) AS BiggestSale, COUNT_BIG(*) AS SaleCount
FROM dbo.QuizSale AS s
GROUP BY s.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_QuizSalesMax ON dbo.QuizSalesMax (ProductID);
GO
CREATE VIEW dbo.QuizSalesStar
WITH SCHEMABINDING
AS
SELECT * FROM dbo.QuizSale;

This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 10125, Level 16, State 1, Line 1
Cannot create index on view "SqlQuizIndexedViewRestrictions.dbo.QuizSalesMax" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

Msg 1054, Level 15, State 6, Procedure QuizSalesStar, Line 4
Syntax '*' is not allowed in schema-bound objects.

One more trap hides in a session setting. The view must be created with QUOTED_IDENTIFIER on. SSMS has it on already, but tools such as sqlcmd turn it off. That’s why the first script sets it. If the view is created with it off, the index fails later even after you turn the setting back on.

SET QUOTED_IDENTIFIER OFF;
GO
CREATE VIEW dbo.QuizSalesSetOff
WITH SCHEMABINDING
AS
SELECT s.ProductID, SUM(s.Amount) AS TotalAmount, COUNT_BIG(*) AS SaleCount
FROM dbo.QuizSale AS s
GROUP BY s.ProductID;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE UNIQUE CLUSTERED INDEX IX_QuizSalesSetOff ON dbo.QuizSalesSetOff (ProductID);

This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 1935, Level 16, State 1, Line 1
Cannot create index. Object 'QuizSalesSetOff' was created with the following SET options off: 'QUOTED_IDENTIFIER'.

Find the Indexed Views in a Database

A name alone doesn’t tell you which views have an index. The catalog does. This query lists every view in the database that has an index on it.

SELECT v.name AS ViewName, i.name AS IndexName, i.type_desc AS IndexType
FROM sys.views AS v
JOIN sys.indexes AS i ON i.object_id = v.object_id
WHERE i.index_id > 0
ORDER BY v.name;

The query returned one row. Six views exist at this point, and only QuizSalesByProduct has an index. The other five failed at CREATE INDEX. QuizSalesOnePart and QuizSalesStar failed at CREATE VIEW, so they were never created.

What the Index Costs

The stored totals stay current because SQL Server updates them with every change to the sales table. That’s the price of faster reads. This script adds one sale for the desk lamp and reads its total again.

INSERT INTO dbo.QuizSale (ProductID, Amount) VALUES (2, 10.00);
SELECT ProductID, TotalAmount, SaleCount
FROM dbo.QuizSalesByProduct WITH (NOEXPAND)
WHERE ProductID = 2;

The total changed from 32.00 to 42.00, and the count from 1 to 2. Nobody ran a refresh. Every insert, update and delete on QuizSale now carries that extra work. Reads get cheaper because the totals are already stored. Writes get dearer for the same reason.

What to Remember

An indexed view needs SCHEMABINDING, two-part names, COUNT_BIG(*) for grouped data and inner joins only. It also needs QUOTED_IDENTIFIER on when you create it. Leave out an outer join, MIN, MAX and SELECT *.

When I’m asked whether to index a view, I check how much the base tables change. A table that gets many writes pays for the index on every one. For the trade-off in more detail, read Indexed Views: When the Faster Reads Are Worth It. For a related puzzle, try Indexed View Quiz: The Table and Clustered Index Confusion.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlQuizIndexedViewRestrictions SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlQuizIndexedViewRestrictions;

An indexed view is not a view with an index added, it is a stored table kept in step.

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