开发者

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

Currently this sql query is able to select betw开发者_开发问答een 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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜