Index on view with 3 joined table is not working

Question

I have Microsoft SQL Server 2019. I want to create a view that includes 3 tables, then create an index on it to use it in query.

When I do this on 2 tables, all works fine, but with 3 tables in the view, it does not work (all created correctly, but index isn't used in queries).

For example: I have these 3 tables: test, test_item, test_item_code

DROP VIEW IF EXISTS dbo.test_view1;
DROP VIEW IF EXISTS dbo.test_view2;

DROP TABLE IF EXISTS DB_TEMP.dbo.test_item_code;
DROP TABLE IF EXISTS DB_TEMP.dbo.test_item;
DROP TABLE IF EXISTS DB_TEMP.dbo.test;

CREATE TABLE DB_TEMP.dbo.test 
(
    t_id int IDENTITY(1,1) NOT NULL,
    t_time datetime NOT NULL,
    CONSTRAINT PK_test PRIMARY KEY (t_id)
);

CREATE TABLE DB_TEMP.dbo.test_item 
(
    ti_id int IDENTITY(1,1) NOT NULL,
    ti_t_id int NOT NULL,
    ti_name nvarchar(100) COLLATE Cyrillic_General_CI_AS NOT NULL
    CONSTRAINT PK_test_item PRIMARY KEY (ti_id)
);

ALTER TABLE DB_TEMP.dbo.test_item 
    ADD CONSTRAINT FK_test_item 
        FOREIGN KEY (ti_t_id) REFERENCES DB_TEMP.dbo.test(t_id);

CREATE TABLE DB_TEMP.dbo.test_item_code 
(
    tic_id int IDENTITY(1,1) NOT NULL,
    tic_ti_id int NOT NULL,
    tic_code varchar(10) COLLATE Cyrillic_General_CI_AS NOT NULL
);

ALTER TABLE DB_TEMP.dbo.test_item_code 
    ADD CONSTRAINT FK_test_item_code 
        FOREIGN KEY (tic_ti_id) REFERENCES DB_TEMP.dbo.test_item(ti_id);

When I create indexed view on two of them

CREATE VIEW test_view1 
WITH SCHEMABINDING 
AS
    SELECT ti_name, t_id, ti_id, t_time
    FROM dbo.test_item
    INNER JOIN dbo.test ON t_id = ti_t_id 
    WHERE ti_name > '';
GO 

CREATE UNIQUE CLUSTERED INDEX PK_test_view1 
ON test_view1 (ti_id);

CREATE INDEX IDX_test_view1 
ON test_view1 (ti_name, t_time) INCLUDE (t_id);

It worked fine. Plan for query

SELECT TOP 10 t_time  
FROM test_view1 
WHERE ti_name = 'name1' 
ORDER BY t_time DESC;

is using the index IDX_test_view1.

But for indexed view with 3 tables:

CREATE VIEW test_view2 
WITH SCHEMABINDING 
AS
    SELECT tic_code, tic_id, ti_id, t_id, t_time
    FROM dbo.test_item_code
    INNER JOIN dbo.test_item ON ti_id = tic_ti_id
    INNER JOIN dbo.test ON t_id = ti_t_id
    WHERE tic_code > '';
GO 

CREATE UNIQUE CLUSTERED INDEX PK_test_view2 
ON test_view2 (tic_id);

CREATE INDEX IDX_test_view2 
ON test_view2 (tic_code, t_time) INCLUDE (t_id);
GO

This does not work. Plan for query

SELECT TOP 10 t_time  
FROM test_view2 
WHERE tic_code = 'code1' 
ORDER BY t_time DESC

starts from 'Table scan test_item_code'... It doesn't use index IDX_test_view2. It used separate tables test_item_code, test_item, test and their indexes.

How to build a correctly indexed view for 3 tables?

Have you tried the `NOEXPAND` hint? `SELECT TOP 10 t_time FROM test_view2 WITH (NOEXPAND) WHERE tic_code = 'code1' ORDER BY t_time DESC` — Dan Guzman, Jul 02 '23 at 16:33
https://sqlperformance.com/2015/12/sql-performance/noexpand-hints — Charlieface, Jul 02 '23 at 20:04

score 2 · Accepted Answer · answered Jul 02 '23 at 17:12

The optimizer will consider view indexes as part of query optimization in Enterprise and Developer editions but index view matching does not always occur as expected even when the view is used directly in queries. It is sometimes necessary to specify the NOEXPAND table hint (which is required in lesser editions to use the view index).

SELECT TOP 10 t_time  
FROM test_view2 WITH (NOEXPAND)
WHERE tic_code = 'code1' 
ORDER BY t_time DESC;

Index on view with 3 joined table is not working

1 Answers1