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?
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;