MSSQL 2008 R2 Selecting Rows with a Specific Range - Paging - What is the Best Way

Question

Currently, this SQL query can choose between the rows that I defined. But is there a 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 

Answer 1

Depends on your indices.

Sometimes it 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 there is a narrower index that can be used to quickly find Id values ​​within a range. See my answer here for an example demonstrating the type of problem that may occur.

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