*.sqlplans attached. (SQL below used to create the sample data - know this isn't optimal but didn't want to spend too much brain power on it, got bored waiting at 90,000 rows).

Statistics have been updated with full scan.

create database TestOffset;

go

use TestOffset;

go

create table TblOffset(

ID uniqueidentifier primary key nonclustered,

Title varchar(150),

DateCreated datetime

)

go

create index DCIndex on TblOffset(DateCreated)

go

go

set showplan_xml on;

go

set statistics xml on;

go

declare @rows int=1, @maxrows int=100000

while @rows <= @maxrows

begin

insert into TblOffset

select newid(), 'AAAA', dateadd(dd,@rows % 730,'2010-01-01')

set @rows = @rows + 1

end

select * from TblOffset

go

update statistics TblOffset with fullscan

go

-- table scan, sort, top = 7.35012

select *

from TblOffset

order by DateCreated

offset 50000 rows fetch next 20000 rows only;

go

-- table scan, sort, top = 7.35012

select *

from TblOffset with (index (DCIndex))

order by DateCreated

offset 50000 rows fetch next 20000 rows only;

go

-- index scan/RIDlookup, nested loop, top = 0.305035

declare @offset bigint = 50000

declare @nextrows bigint = 20000

select *

from TblOffset

order by DateCreated

offset @offset rows fetch next @nextrows rows only;

go

Fitz