Sql Server Pagening with large amount of records

Question

I have this SQL query for pagination:

SELECT * FROM 
  (
    SELECT T1.*,T2.*, ROW_NUMBER() over(ORDER BY ID DESC) row 
    FROM 
        table1 t1
         LEFT JOIN 
        table2 t2 on t1.id = t2.pid
  ) tbl
WHERE row>= @start and row<@end

Now the problem is that the select result can be thousands of records, that will be executed for each page of each users.

Any suggestion that I can part the select (select less records?)

the ROW_NUMBER could be over order by ID or DATE.

and by the way, selecting * is just for simplicity of the sample code.

Do any of these answers help? http://stackoverflow.com/questions/4358253/sql-server-2008-paging-methods — Kane, Aug 29 '14 at 12:54

score 1 · Accepted Answer · edited Aug 29 '14 at 13:27

1

If you have SQL Server 2012 or above you can use the Offset and Fetch keywords as stated here

edited Aug 29 '14 at 13:27

Moons

3,833
4
49
82

answered Aug 29 '14 at 12:54

miezke

51
2

as written in the comments he does not;) – DrCopyPaste Aug 29 '14 at 12:58
well I might be able to use sql server 2012, the performance is my first priority – Ashkan Mobayen Khiabani Aug 29 '14 at 13:02

Sql Server Pagening with large amount of records

1 Answers1