Ok, here's some code to demonstrate what I meant in my previous reply:

-- Key Seek index

CREATE INDEX [IX dbo.Post thread_id, create_dt]

ON dbo.Post (thread_id, create_dt);

DECLARE @PageNumber BIGINT, -- Page number to fetch

@PageSize BIGINT; -- Rows per page

SET @PageSize = 50;

SET @PageNumber = 10;

-- The thread_id to filter on

DECLARE @ThreadID INTEGER;

SET @ThreadID = 6;

-- Key-Seek algorithm

WITH Keys

AS (

-- Step 1 : Number the rows from the non-clustered index

-- Maximum number of rows = @PageNumber * @PageSize

SELECT TOP (@PageNumber * @PageSize)

rn = ROW_NUMBER() OVER (ORDER BY P1.create_dt ASC),

P1.post_id,

P1.create_dt

FROM dbo.Post P1

WHERE P1.thread_id = @ThreadID

ORDER BY

P1.create_dt ASC

),

SelectedKeys

AS (

-- Step 2 : Get the primary keys for the rows on the page we want

-- Maximum number of rows from this stage = @PageSize

SELECT TOP (@PageSize)

SK.rn,

SK.post_id,

SK.create_dt

FROM Keys SK

WHERE SK.rn > ((@PageNumber - 1) * @PageSize)

ORDER BY

SK.create_dt ASC

)

SELECT -- Step 3 : Retrieve the off-index data

-- We will only have @PageSize rows by this stage

SK.rn,

P2.post_id,

P2.thread_id,

P2.member_id,

P2.create_dt,

P2.title,

P2.body

FROM SelectedKeys SK

JOIN dbo.Post P2

ON P2.post_id = SK.post_id

ORDER BY

SK.create_dt ASC

OPTION (RECOMPILE);

Actual execution plan: