Most effective query plan using CTE and Offset/Fetch Next while still supplying a where clause

Question

How does one utilize fetch and offset in a cte to shorten the query plan?

How can it be limited to a where clause, not just selecting all?

---

Below is a full test that i believe isn't as efficient as it could be. Can this be optimized?

if exists(select 1 from sys.tables where name = 'TestTableSize')
begin
drop table TestTableSize;
end

CREATE TABLE dbo.TestTableSize
(
    MyKeyField VARCHAR(10) NOT NULL,
    MyDate1 DATETIME NOT NULL,
    MyDate2 DATETIME NOT NULL,
    MyDate3 DATETIME NOT NULL,
    MyDate4 DATETIME NOT NULL,
    MyDate5 DATETIME NOT NULL
);
go

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME

SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0

WHILE @RowCount < 100000
BEGIN
    SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SET @InsertDate = DATEADD(dd, @Random, GETDATE())
    
    INSERT INTO TestTableSize
        (MyKeyField
        ,MyDate1
        ,MyDate2
        ,MyDate3
        ,MyDate4
        ,MyDate5)
    VALUES
        (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
        , @InsertDate
        ,DATEADD(dd, 1, @InsertDate)
        ,DATEADD(dd, 2, @InsertDate)
        ,DATEADD(dd, 3, @InsertDate)
        ,DATEADD(dd, 4, @InsertDate))

    SET @RowCount = @RowCount + 1
END


DECLARE 
    @PageSize INT = 10, 
    @PageNum  INT = 1;

WITH cte AS(
   select * from TestTableSize
   where MyDate1 between '1/1/2015' and '2/1/2015'
), cteCount AS (
    SELECT COUNT(1) AS MaxRows FROM cte
)
SELECT *
FROM cte, cteCount
ORDER BY cte.MyKeyField
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

Answer 1

I assume you're not looking to improve the performance of your test table build, so the only thing I see for cleanup is that your 2nd cte isn't needed, you can just use COUNT(*) OVER() in the top cte:

DECLARE 
    @PageSize INT = 10, 
    @PageNum  INT = 1;

WITH cte AS(
   select *,COUNT(*) OVER() AS CT from TestTableSize
   where MyDate1 between '1/1/2015' and '2/1/2015'
)
SELECT *
FROM cte
ORDER BY cte.MyKeyField
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

Beyond that, some indexing will help, MyDate1 and MyKeyField should certainly be indexed.