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
– Bogdan Sahlean, Commented Mar 14, 2015 at 7: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
– Vivek Mishra, Commented Mar 14, 2015 at 7:59
use-the-index-luke.com/blog/2013-07/… stackoverflow.com/questions/16968168/… — Vladimir Baranov
– Vladimir Baranov, Commented Mar 15, 2015 at 1:41

Khurram Ali · Accepted Answer · 2015-03-14 14:00:42Z

1

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

edited Mar 14, 2015 at 14:00

answered Mar 14, 2015 at 8:31

Khurram Ali

1,6795 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pரதீப் Over a year ago

Do mention that it will work with Sql server 2012+

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related