Sql-server – SQL Server – materialised (indexed) view not working as expected

materialized-viewsql server

I've created a materialised view in SQL Server, but when selecting from it I get the same performances and the same execution plan than when I select from the same query not "materialised".

The view is simple:

CREATE VIEW TestView WITH SCHEMABINDING
AS
SELECT TAB_A.C1, TAB_A.C2, TAB_A.C3
FROM TAB1
INNER JOIN TAB2 ON TAB1.C2 = TAB2.C2
INNER JOIN TAB3 ON TAB2.C1 = TAB3.C1;


CREATE UNIQUE CLUSTERED INDEX IX_TestView
    ON TestView(C1, C2, C3);

Now, when I do something like

SELECT C1 FROM TestView

I'd expect to see in the plan only an access to the view and nothing else, instead it accesses the underlying tables like the view does not even exists.
I get exactly the same plan and the same execution time, too.

What am I doing wrong? If SQL Server cannot "materialise" the view, I'd expect to at least get an error when creating it.

Best Answer

Like @sp_BlitzErik noted in the comments, you need to use the WITH (NOEXPAND) hint, like so:

SELECT something, something
FROM the.indexed_view WITH (NOEXPAND);

When SQL Server reads your query and replaces the name of the view with the definition of the view, that's what's known as "expanding" the view, so with NOEXPAND, you specifically tell SQL Server to not expand the view, but use the index that's there.

On the flip side, if there's no index (like if someone does ALTER VIEW!) the query will fail if you use WITH NOEXPAND.

Related Question