sql-serverselectpaginationrowbetween

MSSQL 2008 R2 Selecting rows withing certain range - Paging - What is the best way

Currently this sql query is able to select between the rows i have determined. But are there any better approach for this ?

  select * from (select *, ROW_NUMBER() over (order by Id desc) as RowId 
                 from tblUsersMessages ) dt
  where RowId between 10 and 25
Solution

  • Depends on your indexes.

    Sometimes this can be better

    SELECT *
FROM   tblUsersMessages
WHERE  Id IN (SELECT Id
              FROM   (select Id,
                             ROW_NUMBER() over (order by Id desc) as RowId
                      from   tblUsersMessages) dt
              WHERE  RowId between 10 and 25)

    If a narrower index exists that can be used to quickly find the Id values within the range. See my answer here for an example that demonstrates the type of issue that can arise.

    You need to check the execution plans and output of SET STATISTICS IO ON for your specific case.