paging function runs very long time if omits the 'fetch next' statement and the index is nonclustered index

Question

create database db1
go
use db1
go
CREATE SEQUENCE [dbo].[SEQUENCE_test]
 AS [int]
 START WITH 1
 INCREMENT BY 1
go
create table table1(c1 int,c2 int,c3 int,c4 char(10))
go
---populate 266256 entires into table1
insert table1
select  NEXT VALUE FOR SEQUENCE_test, NEXT VALUE FOR SEQUENCE_test, NEXT VALUE FOR SEQUENCE_test,'a'
from sys.columns c1 cross join sys.columns c2
go
create index index1 on table1(c1)
go

--get the last 20 rows of the table1 

select c1,c2 from table1 order by c1 offset 266236 rows fetch next 20 rows only
go
select c1,c2 from table1 order by c1 offset 266236 

This two statement return the same results(20 rows) and have the same execution plan, but the second one runs more time than the frist one.
I use 'set showplan_all on' , find the the second one use 9223372036854775807(the maxvalue of bigint, sql server use this value to guarantee that all the rest entires can be returned?) as top expression.

select c1,c2 from table1 order by c1 offset 266236 rows fetch next 20 rows only
  |--Top(OFFSET EXPRESSION:((266236)),TOP EXPRESSION:((20)))
       |--Parallelism(Gather Streams, ORDER BY:([db1].[dbo].[table1].[c1] ASC))
            |--Sort(TOP 266256, ORDER BY:([db1].[dbo].[table1].[c1] ASC))
                 |--Table Scan(OBJECT:([db1].[dbo].[table1]))

select c1,c2 from table1 order by c1 offset 266236 rows
  |--Top(OFFSET EXPRESSION:((266236)),TOP EXPRESSION:((9223372036854775807)))
       |--Parallelism(Gather Streams, ORDER BY:([db1].[dbo].[table1].[c1] ASC))
            |--Sort(TOP 266235, ORDER BY:([db1].[dbo].[table1].[c1] ASC))
                 |--Table Scan(OBJECT:([db1].[dbo].[table1]))

select c1,c2 from table1 order by c1 offset 266236 rows fetch next 9223372036854775807 rows only
--This statement only returns 20 entires, but runs very long time.

There is no such problem if index1 is a clustered index
Not sure this is by desgin or a bug

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 1

Out of curiousity (and sorry, I'm not running Denali), how does it compare to the execution plan of the following statement?

 

WITH table1_indexed AS (
  SELECT c1, c2, ROW_NUMBER() OVER (ORDER BY c1) AS rownum
  FROM table1
)
SELECT c1, c2
FROM table1_indexed
WHERE rownum > 266236;

I would expect that without an index, clustered or otherwise, that in either case the server would need to sort the complete results in the query before being able to return any results.  While you are populating the table incrementally there is no way that the server can guarantee that will always be the case.  With an index on c1 it could at least perform a range scan against that index and if that index were clustered it would also save bookmark look ups back to the row data.  Without the index the server has to do the same work as if it was selecting the entire table ordered in some arbitrary fashion, except that it will only start to return the results after having read through 26636 records first.  The initial amount of time is the time spent discarding those first 26636 records as otherwise the server does not know where to begin.

---

Halo_Four