title: Most effective query plan using CTE and Offset/Fetch Next while still supplying a where clause
body: <p>How does one utilize fetch and offset in a cte to shorten the query plan?</p>
  <p>How can it be limited to a <code>where</code> clause, not just selecting all?</p>
  <hr />
  <p>Below is a full test that i believe isn't as efficient as it could be.  Can this be optimized?</p>
  <pre><code>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 &lt; 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
  </code></pre>