EDIT

Rob Farley's answer (thanks!) is great and works perfectly for the question as I originally asked it, in which I implied I was joining a single table.

As it is, I have multiple such tables and I was unable to reconcile all using INNER JOINs in order to use that solution.

For the moment I have worked around this by adding a "NULL" row to the lookup tables so I can use an INNER JOIN without losing any rows on the left.

In my case I use uniqueidentifier identities, so I create an indexed view like this:

CREATE VIEW [dbo].[ParentView] WITH SCHEMABINDING AS SELECT P.Id, L.Name FROM [dbo].Parent P INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id 

I then add a row to the Lookup table with a value of 00000000-0000-0000-0000-000000000000 for Id so there is always a match on the right of the join.

I can then create indexes on that view as needed.

Also, as I'm not using Enterprise, I found I needed to use the NOEXPAND hint to ensure those indexes are used:

SELECT * FROM [ParentView] WITH (NOEXPAND) ORDER BY Name OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY 

I have a parent table that contains a foreign key to a lookup table (simplified example):

CREATE TABLE [dbo].[Parent] ( [Id] [uniqueidentifier] NOT NULL, [LookupId] [uniqueidentifier] NULL ) CREATE TABLE [dbo].[Lookup] ( [Id] [uniqueidentifier] NOT NULL, [Name] [nvarchar](64) NOT NULL ) 

In this case, the Parent table has over 10 million rows and the Lookup table has around 5,000.

Both have clustered indexes for the Id column, Parent has a non-clustered index for LookupId and Lookup has a non-clustered index for Name.

I'm running a paged query where I want to include the lookup value in the results:-

SELECT P.Id, L.Name FROM Parent P LEFT JOIN Lookup L ON P.LookupId = L.Id ORDER BY P.Id OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY 

This runs quickly, as does ordering by P.LookupId.

If, however, I try to order by Name (or even L.Id), the query runs considerably slower:

SELECT P.Id, L.Name FROM Parent P LEFT JOIN Lookup L ON P.LookupId = L.Id ORDER BY L.Name OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY 

The query plan for the second query is here: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD

Other seemingly related questions seem to involve ordering by columns in the first table which could be resolved using an appropriate index.

I tried creating an indexed view for this query, however, SQL Server won't allow me to index the view because it contains a LEFT JOIN which I require because LookupId may be NULL and if I use an INNER JOIN those records would be excluded.

Is there a way to optimise this situation?

I have a parent table that contains a foreign key to a lookup table (simplified example):

CREATE TABLE [dbo].[Parent] ( [Id] [uniqueidentifier] NOT NULL, [LookupId] [uniqueidentifier] NULL ) CREATE TABLE [dbo].[Lookup] ( [Id] [uniqueidentifier] NOT NULL, [Name] [nvarchar](64) NOT NULL ) 

In this case, the Parent table has over 10 million rows and the Lookup table has around 5,000. The real Parent implementation has several such foreign key references to other tables and each of those columns may contain NULLs.

Both example tables have unique clustered indexes for their Id columns, Parent has a non-clustered index for LookupId and Lookup has a non-clustered index for Name.

I'm running a paged query where I want to include the lookup value in the results:-

SELECT P.Id, L.Name FROM Parent P LEFT JOIN Lookup L ON P.LookupId = L.Id ORDER BY P.Id OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY 

This runs quickly, as does ordering by P.LookupId.

If, however, I try to order by Name (or even L.Id), the query runs considerably slower:

SELECT P.Id, L.Name FROM Parent P LEFT JOIN Lookup L ON P.LookupId = L.Id ORDER BY L.Name OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY 

The query plan for the second query is here: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD

Other seemingly related questions seem to involve ordering by columns in the first table which could be resolved using an appropriate index.

I tried creating an indexed view for this query, however, SQL Server won't allow me to index the view because it contains a LEFT JOIN which I require because LookupId may be NULL and if I use an INNER JOIN those records would be excluded.

Is there a way to optimise this situation?