How to get elements from table by row number in sql server

Question

I have a table in my database containing about 100000 records. I have to select 10 records from it by row number from n to n+10. I have used following way to get records-

ALTER PROCEDURE getGalleryImagesByPaging
@startIndex int,
@endIndex int
AS
  BEGIN
  WITH gallery as(
   Select ImageId,Caption, ROW_NUMBER() over (Order by id desc) as RN from   imagegalleries where ishome=1 and IsActive=1 
)
Select ImageId, Caption From gallery Where RN Between @startIndex AND @endIndex;
  END
GO

but I think it is very expensive query because it selects all the elements first then select records by row number. Please suggest me the better way to do it.

Post also the structure of `dbo.imagegalleries` and list of indexes. — Bogdan Sahlean, Mar 14 '15 at 07:56
dbo.imagegalleries contains columns named- Id, ImageId, Caption. Id is not auto incremented. My problem is not specific to this case only. This type of problem occurs in many tables in my scenario. — Vivek Mishra, Mar 14 '15 at 07:59
http://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch — Bogdan Sahlean, Mar 14 '15 at 08:01
http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way http://stackoverflow.com/questions/16968168/sql-server-using-union-all-for-multiple-tables-then-paging-implementation — Vladimir Baranov, Mar 15 '15 at 01:41

Khurram Ali · Accepted Answer · 2015-03-14T14:00:42.803

You can also use OFFSET FETCH to get your desire output in Sql server 2012 and +

ALTER PROCEDURE getGalleryImagesByPaging
@startIndex int,
@endIndex int
AS
BEGIN
Select ImageId,
       Caption 
       from   imagegalleries 
       where ishome=1 and IsActive=1 
       ORDER BY id desc OFFSET @startIndex ROWS FETCH NEXT @endIndex ROWS ONLY
END
GO

Limitations in Using OFFSET-FETCH

ORDER BY is mandatory to use OFFSET and FETCH clause.
OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
TOP cannot be combined with OFFSET and FETCH in the same query expression.
The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

Reference: MSDN => OFFSET and FETCH

Do mention that it will work with **Sql server 2012+** – Pரதீப் Mar 14 '15 at 09:41 — Pரதீப், Mar 14 '15 at 09:41

How to get elements from table by row number in sql server

1 Answers1