CODEWITHSUNDEEP
sqlsql-serverperformance
Vivek Mishra
Vivek Mishra

Reputation: 1804

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

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.

Upvotes: 0

Views: 1581

Answers (1)

Khurram Ali
Khurram Ali

Reputation: 1679

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

Upvotes: 1

Related Questions