How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use it, it seems to ignore any index I put on it:

-- +++ THE QUERY THAT I WANT TO IMPROVE PERFORMANCE-WISE +++

    SELECT TOP 1 *
    FROM    dbo.TB_test1 t1
            INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
    ORDER BY t1.somethingelse1
           ,t2.somethingelse2;

    GO

The table setup is as follows:

• two tables

• they are joined by an inner join by the query above

• and ordered by a column from the first and then a column from the second table by the query above; only TOP 1 is selected

-
(in the script below there are also some lines to generate test data, just in case it helps reproducing the problem)

```
-- +++ TABLE SETUP +++

CREATE TABLE [dbo].[TB_test1]
(
[PK_ID1] [INT] IDENTITY(1, 1) NOT NULL
,[something1] VARCHAR(40) NOT NULL
,[somethingelse1] BIGINT NOT NULL
CONSTRAINT [PK_TB_test1] PRIMARY KEY CLUSTERED ( [PK_ID1] ASC )
);

GO

create TABLE [dbo].[TB_test2]
(
[PK_ID2] [INT] IDENTITY(1, 1) NOT NULL
,[FK_ID1] [INT] NOT NULL
,[something2] VARCHAR(40) NOT NULL
,[somethingelse2] BIGINT NOT NULL
CONSTRAINT [PK_TB_test2] PRIMARY KEY CLUSTERED ( [PK_ID2] ASC )
);

GO

ALTER TABLE [dbo].[TB_test2] WITH CHECK ADD CONSTRAINT [FK_TB_Test1] FOREIGN KEY([FK_ID1])
REFERENCES [dbo].[TB_test1] ([PK_ID1])
GO

ALTER TABLE [dbo].[TB_test2] CHECK CONSTRAINT [FK_TB_Test1]

GO

-- +++ TABLE DATA GENERATION +++

-- this might not be the quickest way, but it's only to set up test data

INSERT INTO dbo.TB_test1
( something1, somethingelse1 )
VALUES ( CONVERT(VARCHAR(40), NEWID()) -- something1 - varchar(40)
,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1) -- somethingelse1 - bigint
)

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test1', 0, 1

It seems to ignore any index I put on it

Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint.

Without the hint, the query optimizer (in Enterprise Edition) may make a cost-based choice between using the materialized view or accessing the base tables. Where the view is as large as the base tables, this calculation may favour the base tables.

Another point of interest is that without a NOEXPAND hint, view references are always expanded to the base query before optimization begins. As optimization progresses, the optimizer may or may not be able to match the expanded definition back to the materialized view, depending on previous optimization activity. This is almost certainly not the case with your simple query, but I mention it for completeness.

So, using the NOEXPAND table hint is your main option, but you might also think about just materializing the base table keys and the columns needed for ordering in the view. Create a unique clustered index on the combined key columns, then a separate nonclustered index on the ordering columns.

This will reduce the size of the materialized view, and limit the number of automatic updates that must be made to keep the view synchronized with the base tables. Your query can then be written to fetch the top 1 keys in the required order from the view (ideally with NOEXPAND), then join back to the base tables to fetch any remaining columns using the keys from the view.

Another variation is to cluster the view on the ordering columns and table keys, then write the query to manually fetch the non-view columns from the base table using the keys. The best option for you depends on the broader context. A good way to decide is to test it with the real data and workload.
Basic solution

CREATE VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t1.something1,
        t1.somethingelse1,
        t2.PK_ID2,
        t2.FK_ID1,
        t2.something2,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2 
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Brute force unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);
GO
SELECT TOP (1) * 
FROM dbo.VI_test WITH (NOEXPAND)
ORDER BY somethingelse1,somethingelse2;

Execution plan:

Using a nonclustered index

-- Minimal unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
    (PK_ID1, PK_ID2)
WITH (DROP_EXISTING = ON);
GO
-- Nonclustered index for ordering
CREATE NONCLUSTERED INDEX ix 
ON dbo.VI_test (somethingelse1, somethingelse2);

Execution plan:

There is a lookup in this plan, but it is only used to fetch a single row.
Minimal Indexed View

ALTER VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t2.PK_ID2,
        t1.somethingelse1,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2 
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);

Query:

SELECT TOP (1)
    V.PK_ID1,
    TT1.something1,
    V.somethingelse1,
    V.PK_ID2,
    TT2.FK_ID1,
    TT2.something2,
    V.somethingelse2
FROM dbo.VI_test AS V WITH (NOEXPAND)
JOIN dbo.TB_test1 AS TT1 ON TT1.PK_ID1 = V.PK_ID1
JOIN dbo.TB_test2 AS TT2 ON TT2.PK_ID2 = V.PK_ID2
ORDER BY somethingelse1,somethingelse2;

Execution plan:

This shows the table keys being retrieved (a single row fetch from the view clustered index in order) followed by two single-row lookups on the base tables to fetch the remaining columns.

StackExchange Database Administrators Q#112721, answer score: 13