SQL Paging SQL Server,oracle,db2,mysql

Scenario one (assuming the user only browses to the previous dozens of pages):

Idea: Take out the page size * Number of pages of data, and then get to Intstartindex and Intendindex direct data; Advantages: Simple writing, universal, suitable for users to only browse the first few pages of the situation disadvantage: if the amount of data is more than tens of millions, read the last few pages will be very slow.   SQL Server:  select top Page Size * pages * FROM table1; Get the data, and then take the corresponding data by calculation.   "Oracle":  SELECT * FROM (select tmp_tab.*, rownum as rn   from (SELECT * from table1) tmp_tab) WHERE rn<= Page Size * pages   "DB2":  SELECT * FROM table1 fetch first  Page size * page  rows only  "MySQL":  select * FROM table1 limit   Page size * pages Note: After limit if it is a parameter then starting from 0   scene two (user random access to specific pages): Thinking: Take out the corresponding start and end of the page bar number of advantages: can be directly taken, Cons: Writing is complex, and SQL Sqrver must specify a primary key or sort to complete the paging. "SQL Server 2000 Version": Requires primary key id select top page size from table1 where ID not in (select top  page size * pages  id from table1 Ord ER by-ID) Order BY id  "SQL Server 2005 or higher": Requires a sort field  select * FROM (select Row_number () over (order by ID) as rn,* From table1) where rn>= page starts and RN =< page size * pages) "DB2": Requires a sort field, less than sql2005 function with a lower bar number select * FROM (select RowNumber () over (or Der by id) as rn,* from table1) where rn>= page starts and RN =< pageSize * pages) "Oracle"  select * FROM (select tmp_tab.*, rownum as rn   from (SELECT * from table1) tmp_tab WHERE&NB Sp rn<= Page Size * pages) WHERE rn>= page start "MySQL":   select * from table1 limit page start, page size    

SQL Paging  sql server,oracle,db2,mysql