Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34180

Sql Server Pagening with large amount of records

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.

Upvotes: 1

Views: 160

Answers (1)

miezke
miezke

Reputation: 51

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

Upvotes: 1

Related Questions